SQL : 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.).

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table