Verifying constraint enforcement
SHOW CURRENT can return the settings of constraint enforcement parameters:.
SHOW CURRENT can return the settings of constraint enforcement parameters:
=> SHOW CURRENT EnableNewCheckConstraintsByDefault, EnableNewUniqueKeysByDefault, EnableNewPrimaryKeysByDefault;
level | name | setting
----------+------------------------------------+---------
DEFAULT | EnableNewCheckConstraintsByDefault | 1
DEFAULT | EnableNewUniqueKeysByDefault | 0
DATABASE | EnableNewPrimaryKeysByDefault | 1
(3 rows)
You can also query the following system tables to check table enforcement settings:
For example, the following statement queries TABLE_CONSTRAINTS and returns all constraints in database tables. Column is_enabled is set to true or false for all constraints that can be enabled or disabled—PRIMARY KEY, UNIQUE, and CHECK:
=> SELECT constraint_name, table_name, constraint_type, is_enabled FROM table_constraints ORDER BY is_enabled, table_name;
constraint_name | table_name | constraint_type | is_enabled
---------------------------+-----------------------+-----------------+------------
C_PRIMARY | call_center_dimension | p | f
C_PRIMARY | date_dimension | p | f
C_PRIMARY | employee_dimension | p | f
C_PRIMARY | online_page_dimension | p | f
C_PRIMARY | product_dimension | p | f
C_PRIMARY | promotion_dimension | p | f
C_PRIMARY | shipping_dimension | p | f
C_PRIMARY | store_dimension | p | f
C_UNIQUE_1 | tabletemp | u | f
C_PRIMARY | vendor_dimension | p | f
C_PRIMARY | warehouse_dimension | p | f
C_PRIMARY | customer_dimension | p | t
C_PRIMARY | new_sales | p | t
C_CHECK | new_sales | c | t
fk_inventory_date | inventory_fact | f |
fk_inventory_product | inventory_fact | f |
fk_inventory_warehouse | inventory_fact | f |
...
The following query returns all tables that have primary key, unique, and check constraints, and shows whether the constraints are enabled:
=> SELECT table_name, constraint_name, constraint_type, is_enabled FROM constraint_columns
WHERE constraint_type in ('p', 'u', 'c')
ORDER BY table_name, constraint_type;
=> SELECT table_name, constraint_name, constraint_type, is_enabled FROM constraint_columns WHERE constraint_type in ('p', 'u', 'c') ORDER BY table_name, constraint_type;
table_name | constraint_name | constraint_type | is_enabled
-----------------------+-----------------+-----------------+------------
call_center_dimension | C_PRIMARY | p | f
customer_dimension | C_PRIMARY | p | t
customer_dimension2 | C_PRIMARY | p | t
customer_dimension2 | C_PRIMARY | p | t
date_dimension | C_PRIMARY | p | f
employee_dimension | C_PRIMARY | p | f
new_sales | C_CHECK | c | t
new_sales | C_PRIMARY | p | t
...