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)