51.13. pg_constraint

51.13. `pg_constraint`
Prev	Up	Chapter 51. System Catalogs	Home	Next

The 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 51.13. pg_constraint Columns

Name	Type	References	Description
`oid`	`oid`		Row identifier
`conname`	`name`		Constraint name (not necessarily unique!)
`connamespace`	`oid`	`pg_namespace .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`	`pg_class .oid`	The table this constraint is on; 0 if not a table constraint
`contypid`	`oid`	`pg_type .oid`	The domain this constraint is on; 0 if not a domain constraint
`conindid`	`oid`	`pg_class .oid`	The index supporting this constraint, if it's a unique, primary key, foreign key, or exclusion constraint; else 0
`conparentid`	`oid`	`pg_constraint .oid`	The corresponding constraint in the parent partitioned table, if this is a constraint in a partition; else 0
`confrelid`	`oid`	`pg_class .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[]`	`pg_attribute .attnum`	If a table constraint (including foreign keys, but not constraint triggers), list of the constrained columns
`confkey`	`int2[]`	`pg_attribute .attnum`	If a foreign key, list of the referenced columns
`conpfeqop`	`oid[]`	`pg_operator .oid`	If a foreign key, list of the equality operators for PK = FK comparisons
`conppeqop`	`oid[]`	`pg_operator .oid`	If a foreign key, list of the equality operators for PK = PK comparisons
`conffeqop`	`oid[]`	`pg_operator .oid`	If a foreign key, list of the equality operators for FK = FK comparisons
`conexclop`	`oid[]`	`pg_operator .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. (It's recommended to use `pg_get_constraintdef()` to extract the definition of a check constraint.)

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.)

Note

pg_class.relchecks needs to agree with the number of check-constraint entries found in this table for each relation.

Prev	Up	Next
51.12. `pg_collation`	Home	51.14. `pg_conversion`