Find names of check constraints (either associated with a base table or a domain) that names contain words that are not needed in the name. For instance, constraints cannot ensure the correctness of data and thus the word correct should not be used in the names. Words like "valid" or phrases like "follows_rules" are just noise because all the constraint ensure that the registered data values are valid and follow certain rules.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Rename the constraints and remove the unnecessary words from the name.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
WITH checks AS(SELECT
n.nspname || '.' || c.relname AS container_name,
CASE WHEN c.relkind='r' THEN 'BASE TABLE'
WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
WHEN c.relkind='f' THEN 'FOREIGN TABLE'
WHEN c.relkind='p' THEN 'PARTITIONED TABLE'
ELSE 'TABLE' END AS container_type,
conname AS constraint_name,
replace(pg_get_constraintdef(o.oid),'CHECK','') AS constraint_src
FROM pg_constraint o INNER JOIN pg_class c ON o.conrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND o.contype='c'
UNION SELECT dc.domain_schema || '.' || dc.domain_name AS container_name,
'DOMAIN' AS container_type,
cc.constraint_name,
cc.check_clause
FROM INFORMATION_SCHEMA.domain_constraints AS dc
INNER JOIN INFORMATION_SCHEMA.domains AS d 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 container_name, container_type, constraint_name, constraint_src,
CASE WHEN constraint_name~*'(oige|right|correct)' THEN 'Constraints cannot ensure correctness of data'
ELSE 'Noise words like valid or follows rules are not needed in the name' END AS comment_about_the_name
FROM checks
WHERE constraint_name~*'(oige|korrektne|valiidne|reegliparane|vastab([ ]|_)*reeglitele|right|correct|valid|(corresponds|follows)([ ]|_)*rules)'
AND constraint_src!~*'(oige|korrektne|valiidne|reegliparane|vastab([ ]|_)*reeglitele|right|correct|valid|(corresponds|follows)([ ]|_)*rules)'
ORDER BY container_type, container_name;
Collections
This query belongs to the following collections:
Name
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
This query is classified under the following categories:
Name
Description
CHECK constraints
Queries of this category provide information about CHECK constraints.
Domains
Queries of this category provide information about reusable specifications of column properties.
Naming
Queries of this category provide information about the style of naming.