This page in other versions: 9.1 / 9.2 / 9.3 / current (9.4)  |  Development versions: devel / 9.5  |  Unsupported versions: 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0

45.12. pg_constraint

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 45-12. pg_constraint Columns

Name Type References Description
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?
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
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, u = simple (unspecified)
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.
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 text   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.)

Note: 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.

Note: pg_class.relchecks needs to agree with the number of check-constraint entries found in this table for each relation. Also, pg_class.relhasexclusion must be true if there are any exclusion-constraint entries for the relation.

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group