| Query goal: | Find domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1"). |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | High (Few or no false-positive results) |
| Query license: | MIT License |
| Fixing suggestion: | Rename the constraints. |
| Data source: | INFORMATION_SCHEMA only |
| SQL query: | Click on query to copy it
SELECT domain_schema, domain_name, constraint_name AS suspected_constraint_name FROM INFORMATION_SCHEMA.domain_constraints WHERE 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) AND ((constraint_name~*'(andmed|_data|info|veerg|column|kitsendus|piirang|constraint|restriction)' AND constraint_name!~*'database') OR constraint_name~*'^(_){0,1}(ck|chk)(_|[[:digit:]])*$') ORDER BY domain_schema, domain_name, constraint_name; |
| 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. |
| 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. |