Query goal: | Do not duplicate code. In this case a CHECK constraint duplicates the restriction that is already enforced with the help of the declaration of the maximum field size (for instance, VARCHAR(100)). |
Notes about the query: | The query considers both CHECK constraints that are associated directly with a table as well as CHECK constraints that are associated with a table through a domain. |
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: | Drop the CHECK constraints. Before doing it make sure that you do not drop any unrelated constraint. This could happen if a CHECK constraint enforces multiple rules. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH text_domains_with_checks AS ( SELECT cdu.table_schema, cdu.table_name, cdu.column_name, d.data_type, d.character_maximum_length, cc.check_clause 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_catalog, constraint_schema, constraint_name) INNER JOIN INFORMATION_SCHEMA.domains AS d USING (domain_schema, domain_name) WHERE t.table_type IN ('BASE TABLE', 'FOREIGN') AND d.data_type LIKE '%char%' 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)), text_columns_with_checks AS (SELECT (SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema, c.relname as table_name, a.attname AS column_name, t.typname AS data_type, a.atttypmod-4 AS character_maximum_length, substring(pg_get_constraintdef(o.oid),7) as consrc FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE INNER JOIN pg_type t ON a.atttypid=t.oid WHERE cardinality(o.conkey)=1 AND o.contype = 'c' AND t.typname in ('bpchar','char','varchar') UNION SELECT table_schema, table_name, column_name, data_type, character_maximum_length, check_clause FROM text_domains_with_checks) SELECT table_schema, table_name, column_name, data_type, character_maximum_length, consrc FROM text_columns_with_checks WHERE consrc LIKE '% <= ' || character_maximum_length || ')%' OR consrc LIKE '%(' || character_maximum_length || ' >= %' OR consrc LIKE '%~%,' || character_maximum_length || '}%' OR consrc LIKE '%,' || character_maximum_length || '}%~%' ORDER BY table_schema, table_name ; |
SQL query | Description |
---|---|
WITH table_simple_chk AS (SELECT (SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema, c.relname as table_name, a.attname AS column_name, t.typname AS data_type, a.atttypmod-4 AS character_maximum_length, o.conname, substring(pg_get_constraintdef(o.oid),7) AS consrc FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE INNER JOIN pg_type t ON a.atttypid=t.oid WHERE cardinality(o.conkey)=1 AND o.contype = 'c' AND o.conrelid in (SELECT oid FROM pg_class c WHERE c.relkind = 'r') AND t.typname in ('bpchar','char','varchar')) SELECT format ('ALTER TABLE %1$I.%2$I DROP CONSTRAINT %3$I;', table_schema, table_name, conname) AS statements FROM table_simple_chk WHERE consrc LIKE '% <= ' || character_maximum_length || ')%' OR consrc LIKE '%(' || character_maximum_length || ' >= %' OR consrc LIKE '%~%,' || character_maximum_length || '}%' OR consrc LIKE '%,' || character_maximum_length || '}%~%' ORDER BY table_schema, table_name ; | Drop the check constraint that is associated directly with a table. |
WITH domains AS ( SELECT (SELECT data_type FROM INFORMATION_SCHEMA.domains AS d WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS data_type, (SELECT character_maximum_length FROM INFORMATION_SCHEMA.domains AS d WHERE d.domain_schema=dc.domain_schema AND d.domain_name=dc.domain_name) AS character_maximum_length, cc.check_clause, cdu.domain_schema, cdu.domain_name, dc.constraint_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_catalog, constraint_schema, constraint_name) WHERE t.table_type='BASE TABLE' 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 catalog_name IS NOT NULL AND schema_name IS NOT NULL)), text_domains AS ( SELECT domain_schema, domain_name, constraint_name, check_clause, character_maximum_length FROM domains WHERE data_type LIKE '%char%') SELECT format('ALTER DOMAIN %1$I.%2$I DROP CONSTRAINT %3$I;', domain_schema, domain_name, constraint_name) AS statements FROM text_domains WHERE check_clause LIKE '% <= ' || character_maximum_length || ')%' OR check_clause LIKE '%(' || character_maximum_length || ' >= %' OR check_clause LIKE '%~%,' || character_maximum_length || '}%' OR check_clause LIKE '%,' || character_maximum_length || '}%~%' ORDER BY domain_schema, domain_name; | Drop the check constraint that is associated with a domain. Be careful because the domain could be used in case of multiple columns and such modification affects all these columns. |
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 . |
Category name | Category description |
---|---|
CHECK constraints | Queries of this category provide information about CHECK constraints. |
Duplication of implementation elements | Queries of this catergory provide information about the duplication of the database objects. |
Field size | Queries of this category provide information about the maximum size of values that can be recorded in column fields |
Regular expressions | Queries of this catergory provide information about the use of regular expressions. |
Reference |
---|
https://refactoring.guru/smells/alternative-classes-with-different-interfaces |
https://refactoring.guru/smells/duplicate-code |
The corresponding code smell in case of cleaning code is "G5: Duplication". (Robert C. Martin, Clean Code) |