Goal Find all CHECK constraints (except NOT NULL) of domains that are not associated with any column.
Type General (Overview of some aspect of the database.)
License MIT License
Data Source INFORMATION_SCHEMA only
SQL Query
WITH checks AS (SELECT dc.domain_schema, dc.domain_name, cc.check_clause, cc.constraint_name, d.data_type
FROM INFORMATION_SCHEMA.domains AS d 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 cc.check_clause NOT LIKE '%IS NOT NULL'  AND 
dc.domain_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 domain_schema, domain_name, check_clause, constraint_name, data_type
FROM checks
WHERE NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.tables AS t INNER JOIN INFORMATION_SCHEMA.columns AS c USING (table_schema, table_name)
WHERE 
c.domain_schema=checks.domain_schema 
AND c.domain_name=checks.domain_name
AND 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))
ORDER BY domain_schema, domain_name, constraint_name;

Collections

This query belongs to the following collections:

NameDescription
Find problems by overviewQueries 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:

NameDescription
CHECK constraintsQueries of this category provide information about CHECK constraints.
DomainsQueries of this category provide information about reusable specifications of column properties.
Validity and completenessQueries 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).