Perhaps too many subconditions in a CHECK constraint

Query goal:

Find check constraints of base table and foreign table columns that are either associated with more than one column and have at least one AND operation or are associated with exactly one column and have two or more AND operations.

Notes about the query:

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 may give false positive results if the constraint uses BETWEEN … AND … predicate. The query excludes constraints where subconditions are connected by the OR operator because in this case a rule could be that if a condition P holds a condition Q must also hold. For instance, if in the column X is value 'A' and in the column Y is value 'B', then in the column Z there must be value 'C'. The query excludes constraints with the Boolean expression NOT (P AND Q) where P and Q are subconditions on different columns.

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

Medium (Medium number of false-positive results)

Query license:

MIT License

Fixing suggestion:

Increase separation of concerns in case of CHECK constraints by creating multiple smaller check constraints instead of one big constraint. Instead of one CHECK constraint with multiple sub-conditions create multiple CHECK constraints.

Data source:

INFORMATION_SCHEMA only

SQL query:

Click on query to copy it

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)),
checks_columns AS (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)
SELECT table_schema, table_name, table_type, columns, check_clause, check_clause, constraint_name, check_type, domain_schema, domain_name
FROM checks_columns
WHERE check_clause!~*'[[:space:]]OR[[:space:]]'
AND (
(check_type='TABLE CHECK' AND cardinality(columns)>1 
AND check_clause~*'((.*[[:space:]]AND[[:space:]].*){2,}|(?<!NOT.*)[[:space:]]AND[[:space:]])')
OR ((cardinality(columns)=1 OR check_type='DOMAIN CHECK') 
AND check_clause~*'(.*[[:space:]]AND[[:space:]].*){2,}')
)
ORDER BY table_schema, table_name;

Collections where the query belongs to

Collection name	Collection description
Find problems automatically	Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Collection name

Collection description

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories where the query belongs to

Category name	Category description
CHECK constraints	Queries of this category provide information about CHECK constraints.
Comfortability of database evolution	Queries of this category provide information about the means that influence database evolution.

Category name

Category description

CHECK constraints

Queries of this category provide information about CHECK constraints.

Comfortability of database evolution

Queries of this category provide information about the means that influence database evolution.

Reference materials for further reading

Reference
The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code)

Reference

The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code)