Query goal: | Find CHECK constraints on base table or foreign table columns that contain data about personal names. Make sure that the constraints do not restrict registration of legal names. |
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 considers both column names in English and Estonian. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Fixing suggestion: | Drop the too restrictive 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 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 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, string_agg(column_name, ',<br>' ORDER BY column_name) AS columns, check_clause, constraint_name, check_type FROM checks WHERE column_name~*'(first[_]*name|last[_]*name|given[_]*name|surname|person[_]*name|eesnimi|perenimi|perekonnanimi|isik.*nimi)' GROUP BY table_schema, table_name, table_type, check_clause, constraint_name, check_type ORDER BY table_schema, table_name; |
Collection name | Collection 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 . |
Category name | Category description |
---|---|
CHECK constraints | Queries of this category provide information about CHECK constraints. |
Result quality depends on names | Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results. |
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). |
Reference |
---|
https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ |
https://shinesolutions.com/2018/01/08/falsehoods-programmers-believe-about-names-with-examples/ |