pg_constraintThe catalog pg_constraint stores check, primary key, unique, foreign key, and exclusion constraints on tables. (Column constraints are not treated specially. Every column constraint is equivalent to some table constraint.) Not-null constraints are represented in the pg_attribute catalog, not here.
User-defined constraint triggers (created with CREATE CONSTRAINT TRIGGER) also give rise to an entry in this table.
Check constraints on domains are stored here, too.
Table 52.13. pg_constraint Columns
| Name | Type | References | Description |
|---|---|---|---|
oid |
oid |
Row identifier (hidden attribute; must be explicitly selected) | |
conname |
name |
Constraint name (not necessarily unique!) | |
connamespace |
oid |
|
The OID of the namespace that contains this constraint |
contype |
char |
c = check constraint, f = foreign key constraint, p = primary key constraint, u = unique constraint, t = constraint trigger, x = exclusion constraint |
|
condeferrable |
bool |
Is the constraint deferrable? | |
condeferred |
bool |
Is the constraint deferred by default? | |
convalidated |
bool |
Has the constraint been validated? Currently, can only be false for foreign keys and CHECK constraints | |
conrelid |
oid |
|
The table this constraint is on; 0 if not a table constraint |
contypid |
oid |
|
The domain this constraint is on; 0 if not a domain constraint |
conindid |
oid |
|
The index supporting this constraint, if it's a unique, primary key, foreign key, or exclusion constraint; else 0 |
conparentid |
oid |
|
The corresponding constraint in the parent partitioned table, if this is a constraint in a partition; else 0 |
confrelid |
oid |
|
If a foreign key, the referenced table; else 0 |
confupdtype |
char |
Foreign key update action code: a = no action, r = restrict, c = cascade, n = set null, d = set default |
|
confdeltype |
char |
Foreign key deletion action code: a = no action, r = restrict, c = cascade, n = set null, d = set default |
|
confmatchtype |
char |
Foreign key match type: f = full, p = partial, s = simple |
|
conislocal |
bool |
This constraint is defined locally for the relation. Note that a constraint can be locally defined and inherited simultaneously. | |
coninhcount |
int4 |
The number of direct inheritance ancestors this constraint has. A constraint with a nonzero number of ancestors cannot be dropped nor renamed. | |
connoinherit |
bool |
This constraint is defined locally for the relation. It is a non-inheritable constraint. | |
conkey |
int2[] |
|
If a table constraint (including foreign keys, but not constraint triggers), list of the constrained columns |
confkey |
int2[] |
|
If a foreign key, list of the referenced columns |
conpfeqop |
oid[] |
|
If a foreign key, list of the equality operators for PK = FK comparisons |
conppeqop |
oid[] |
|
If a foreign key, list of the equality operators for PK = PK comparisons |
conffeqop |
oid[] |
|
If a foreign key, list of the equality operators for FK = FK comparisons |
conexclop |
oid[] |
|
If an exclusion constraint, list of the per-column exclusion operators |
conbin |
pg_node_tree |
If a check constraint, an internal representation of the expression | |
consrc |
text |
If a check constraint, a human-readable representation of the expression |
In the case of an exclusion constraint, conkey is only useful for constraint elements that are simple column references. For other cases, a zero appears in conkey and the associated index must be consulted to discover the expression that is constrained. (conkey thus has the same contents as pg_index.indkey for the index.)
consrc is not updated when referenced objects change; for example, it won't track renaming of columns. Rather than relying on this field, it's best to use pg_get_constraintdef() to extract the definition of a check constraint.
pg_class.relchecks needs to agree with the number of check-constraint entries found in this table for each relation.