Re: constraints in table

Dominique Bessette - Halsema asked:
>
> Hello,
>
> How do i find the constraints on a table in SQL? my database is linux
> based, and I cant seem to find the command. Thanks
>

From the psql prompt, \d works for me:

billing=# \d work.clients
Table "work.clients"
Column | Type | Modifiers
--------------------------+------------------------+-----------
client_id | character varying(10) | not null
client_name | character varying(60) | not null
<...>
source_id | integer |
Indexes:
"clients_pkey" PRIMARY KEY, btree (client_id)
Check constraints:
"clients_client_host_fee_type" CHECK (client_host_fee_type = 'P'::bpchar OR client_host_fee_type = 'M'::bpchar OR client_host_fee_type = ''::bpchar)
Foreign-key constraints:
"$1" FOREIGN KEY (client_status) REFERENCES client_status(client_status)
"$2" FOREIGN KEY (client_brand) REFERENCES brandinginfo(branding_id)
Triggers:
aud_client AFTER INSERT OR DELETE OR UPDATE ON "work".clients FOR EACH ROW EXECUTE PROCEDURE "work".aud_client()
rt_client BEFORE INSERT OR DELETE OR UPDATE ON "work".clients FOR EACH ROW EXECUTE PROCEDURE work_rt.rt_client()

If you want to see the SQL that gets these results, invoke psql with -E:

bildb-01:~/wf_progs> !! -E
psql -d billing -E
Welcome to psql 8.1.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

billing=# \d work.clients
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^clients$'
AND n.nspname ~ '^work$'
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '21191'
**************************

********* QUERY **********
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '21191' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = '21191' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
**************************

********* QUERY **********
SELECT r.conname, pg_catalog.pg_get_constraintdef(r.oid, true)
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '21191' AND r.contype = 'c' ORDER BY 1
**************************

********* QUERY **********
SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid)
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = '21191' AND (not tgisconstraint OR NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) WHERE d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = 'i' AND c.contype = 'f')) ORDER BY 1
**************************

********* QUERY **********
SELECT conname,
pg_catalog.pg_get_constraintdef(oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '21191' AND r.contype = 'f' ORDER BY 1
**************************

********* QUERY **********
SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '21191' ORDER BY inhseqno ASC
**************************

This SQL may differ on different versions; this is from 8.1.

HTH,

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)