Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications).
Notes
The query finds CHECK constraints that are associated with a base table directly as well as CHECK constraints that are associated with domains that are used to define at least one column. The query does not find CHECK constraints of domains that are not associated with any table. The query does not find constraints that do not cover any column (for instance: CHECK (false)).
Type
General (Overview of some aspect of the database.)
WITH checks AS (SELECT ccu.table_schema, ccu.table_name, t.table_type, ccu.column_name, cc.check_clause, cc.constraint_name, 'TABLE CHECK' AS check_type, NULL AS domain_schema, NULL AS domain_name
FROM INFORMATION_SCHEMA.constraint_column_usage AS ccu INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
WHERE cc.check_clause NOT LIKE '%IS NOT NULL' AND
ccu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT cdu.table_schema, cdu.table_name, t.table_type, cdu.column_name, cc.check_clause, cc.constraint_name, 'DOMAIN CHECK' AS check_type, cdu.domain_schema, cdu.domain_name
FROM INFORMATION_SCHEMA.column_domain_usage AS cdu INNER JOIN INFORMATION_SCHEMA.tables AS t USING (table_schema, table_name)
INNER JOIN INFORMATION_SCHEMA.domain_constraints AS dc USING (domain_schema, domain_name)
INNER JOIN INFORMATION_SCHEMA.check_constraints AS cc USING (constraint_schema, constraint_name)
WHERE t.table_type IN ('BASE TABLE','FOREIGN') AND cc.check_clause NOT LIKE '%IS NOT NULL' AND
cdu.table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL))
SELECT table_schema, table_name, table_type, array_agg(column_name) AS columns, check_clause, constraint_name, check_type, domain_schema, domain_name
FROM checks
GROUP BY table_schema, table_name, table_type, check_clause, constraint_name, check_type, domain_schema, domain_name
ORDER BY table_schema, table_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems about integrity constraints
A selection of queries that return information about the state of integrity constraints in the datadabase. Contains all the types of queries - problem detection, software measure, and general overview
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
Description
CHECK constraints
Queries of this category provide information about CHECK constraints.
Validity and completeness
Queries of this category provide information about whether database design represents the world (domain) correctly (validity) and whether database design captures all the information about the world (domain) that is correct and relevant (completeness).