How to get all the constraints for a table with this command?
Getting the constraint for a table with a single command in your Postgres database.
A nifty little trick if you want to quickly know all the constraints in a table in your postgres database:
This is esp. useful when you have dozens of tables and you don’t want to go through each one of them to find out the constraints.
For example, before deleting an unused table, I want to check if there’s any foreign key constraint on it. What type of constraint is it? Is it on delete cascade? Or is it on delete restrict? Or is it on delete set null?
Here’s the command
select *
from information_schema.table_constraints
where table_name = 'table_name';
This will give something like this:
| constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred |
| — — — — — — — — — — | — — — — — — — — — -| — — — — — — — — -| — — — — — — — -| — — — — — — — | — — — — — — | — — — — — — — — -| — — — — — — — -| — — — — — — — — — — |
| postgres | public | table_name_pkey | postgres | public | table_name | PRIMARY KEY | NO | NO |