| PostgreSQL 8.2.23 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 43. System Catalogs | Fast Forward | Next |
The catalog pg_constraint stores check, primary key, unique, and foreign key 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.
Check constraints on domains are stored here, too.
Table 43-13. 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 | |
| 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 |
| confrelid | oid | pg_class.oid | If a foreign key, the referenced table; else 0 |
| confupdtype | char | Foreign key update action code | |
| confdeltype | char | Foreign key deletion action code | |
| confmatchtype | char | Foreign key match type | |
| conkey | int2[] | pg_attribute.attnum | If a table constraint, list of columns which the constraint constrains |
| confkey | int2[] | pg_attribute.attnum | If a foreign key, list of the referenced columns |
| 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 |
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 the given relation.
This query will get you the namespace, table, column of each foreign key that points to the plugged in namespace, table, column
SELECT n.nspname AS namespace, p1.relname AS table,
a1.attname AS column
FROM pg_constraint c, pg_namespace n,
pg_class p1, pg_class p2,
pg_attribute a1, pg_attribute a2
WHERE c.contype = 'f'
AND c.confrelid > 0
AND c.connamespace = n.oid
AND c.conrelid = p1.oid
AND c.confrelid = p2.oid
AND c.conrelid = a1.attrelid
AND a1.attnum = ANY (c.conkey)
AND c.confrelid = a2.attrelid
AND a2.attnum = ANY (c.confkey)
AND n.nspname = 'public'
AND p2.relname = 'users'
AND a2.attname = 'user_id'
This query is similar to last one I posted, but will give you schema.table.column for each foreign key pointing to the given one for the whole db and not just current schema
SELECT n.nspname AS namespace, p1.relname AS table,
a1.attname AS column
FROM pg_constraint c, pg_namespace n,
pg_class p1, pg_class p2,
pg_attribute a1, pg_attribute a2,
pg_namespace n2
WHERE c.contype = 'f'
AND c.confrelid > 0
AND c.connamespace = n.oid
AND c.conrelid = p1.oid
AND c.confrelid = p2.oid
AND c.conrelid = a1.attrelid
AND a1.attnum = ANY (c.conkey)
AND c.confrelid = a2.attrelid
AND a2.attnum = ANY (c.confkey)
AND p2.relnamespace = n2.oid
AND n2.nspname = 'public'
AND p2.relname = 'users'
AND a2.attname = 'user_id'