Recipe5.4.Listing Constraints on a Table

Recipe 5.4. Listing Constraints on a Table

Problem

You want to list the constraints defined for a table in some schema and the columns they are defined on. For example, you want to find the constraints and the columns they are on for table EMP.

Solution

DB2

Query SYSCAT.TABCONST and SYSCAT.COLUMNS:

 1  select a.tabname, a.constname, b.colname, a.type 2    from syscat.tabconst a, 3         syscat.columns b 4  where a.tabname   = 'EMP' 5    and a.tabschema = 'SMEAGOL' 6    and a.tabname   = b.tabname 7    and a.tabschema = b.tabschema 

Oracle

Query SYS.ALL_CONSTRAINTS and SYS.ALL_CONS_COLUMNS:

  1  select a.table_name,  2         a.constraint_name,  3         b.column_name,  4         a.constraint_type  5    from all_constraints a,  6         all_cons_columns b  7  where a.table_name      = 'EMP'  8    and a.owner           = 'SMEAGOL'  9    and a.table_name      = b.table_name 10    and a.owner           = b.owner 11    and a.constraint_name = b.constraint_name 

PostgreSQL, MySQL, and SQL Server

Query INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_ SCHEMA.KEY_COLUMN_USAGE:

  1  select a.table_name,  2         a.constraint_name,  3         b.column_name,  4         a.constraint_type  5    from information_schema.table_constraints a,  6         information_schema.key_column_usage b  7  where a.table_name      = 'EMP'  8    and a.table_schema    = 'SMEAGOL'  9    and a.table_name      = b.table_name 10    and a.table_schema    = b.table_schema 11    and a.constraint_name = b.constraint_name 

Discussion

Constraints are such a critical part of relational databases that it should go without saying why you need to know what constraints are on your tables. Listing the constraints on tables is useful for a variety of reasons: you may want to find tables missing a primary key, you may want to find which columns should be foreign keys but are not (i.e., child tables have data different from the parent tables and you want to know how that happened), or you may want to know about check constraints (Are columns nullable? Do they have to satisfy a specific condition? etc.).