Checking Whether Constraints Are Enabled

Use the SELECT statement to list constraints and confirm whether they are enabled or disabled.

This example shows a query that lists all tables along with their associated primary key, unique, and check constraint types. The query also indicates whether the constraints are enabled or disabled.

select table_name, constraint_name, constraint_type, is_enabled from v_catalog.constraint_columns where constraint_type in ('p', 'u', 'c') order by table_name;

The following output shows the results of this query. The constraint_type column indicates whether the constraint is a primary key, unique key, or check constraint (p, u, or c, respectively). The is_enabled column indicates whether the constraint is enabled or disabled (t or f respectively).

table_name  | constraint_name | constraint_type | is_enabled 
------------+-----------------+-----------------+-----------
table01     | pksample        | p               | t
table02     | uniquesample    | u               | f	
table03     | checksample     | c               | t
				
(3 rows)
		

The following example is similar but shows how you can create a query that lists associated columns instead of tables. You could add both tables and columns to the same query, if you want.

select column_name, constraint_name, constraint_type, is_enabled from v_catalog.constraint_columns where constraint_type in ('p', 'u', 'c') order by column_name;

Sample output follows.

column_name | constraint_name | constraint_type | is_enabled 
------------+-----------------+-----------------+-----------
col1_key    | pksample        | p               | t
vendor_key  | uniquesample    | u               | f	
zip_key     | checksample     | c               | t
					
(2 rows)	

The following example statement shows how to create a sample table with a multi-column constraint.

CREATE TABLE table09 (column1 int, column2 int, CONSTRAINT multicsample PRIMARY KEY (column1, column2) ENABLED);

Here's the output listing associated columns.

column_name | constraint_name | constraint_type | is_enabled 
------------+-----------------+-----------------+-----------
column1     | multicsample    | p               | t
column2     | multicsample    | p               | t	
					
(2 rows)