Query goal: | Find table constraints (constraints that are associated directly with the table) that have too generic names like "key" or the name contain too generic words like "data" (all constraints restrict data in the table), or the name is an abbreviation of a constraint type name. |
Notes about the query: | The query considers both names in English and in Estonian. |
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: | Rename the constraints. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
SELECT n.nspname AS table_schema, c.relname AS table_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 table_type, CASE WHEN o.contype='p' THEN 'PRIMARY KEY' WHEN o.contype='u' THEN 'UNIQUE' WHEN o.contype='f' THEN 'FOREIGN KEY' WHEN o.contype='c' THEN 'TABLE CHECK' WHEN o.contype='x' THEN 'EXCLUDE' WHEN o.contype='t' THEN 'CONSTRAINT TRIGGER' END AS suspected_constraint_type, o.conname AS suspected_name 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.conname~*'(andme(?!baas)|(?<!eel)data(?!base)|info|veerg|column|kitsendus|constraint|restriction)' OR o.conname~*'^[^[:alpha:]]*(pk|uk|uq|ak|ck|chk|fk|excl|key|relationship|id|voti|seos|suhe)[^[:alpha:]]*$') ORDER BY suspected_constraint_type, table_schema, table_name, suspected_name; |
Collection name | Collection description |
---|---|
Find problems about names | A selection of queries that return information about the names of database objects. Contains all the types of queries - problem detection, software measure, and general overview. |
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. |
Naming | Queries of this category provide information about the style of naming. |
Relationships between tables | Queries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |