Query goal: | Find base base tables have a simple unique index (not associated with a constraint) that contains the column with the (case insensitive) name id and an integer type. In addition, the key values are generated automatically by the system by using a sequence generator. |
Notes about the query: | The query does not take into account as to whether the table has any other declared key or unique index. The query considers a possibility that the key column may be declared based on 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: | Instead of unique index declare the primary key. Declare alternate keys in the table. Rename the key column - for instance according to the pattern "table_name"_id. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
with simple_keys as (SELECT n.nspname AS table_schema, c2.relname AS table_name, a.attname AS column_name FROM pg_catalog.pg_class AS c INNER JOIN pg_catalog.pg_index AS i ON i.indexrelid = c.oid INNER JOIN pg_catalog.pg_class AS c2 ON i.indrelid = c2.oid INNER JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid INNER JOIN pg_catalog.pg_authid AS u ON n.nspowner = u.oid INNER JOIN pg_catalog.pg_attribute AS a ON a.attrelid = c.oid WHERE c.relkind = 'i' AND i.indisunique=TRUE AND i.indnkeyatts=1 AND a.attnum>=1 AND a.attisdropped = FALSE AND (n.nspname = 'public' OR u.rolname <> 'postgres') AND NOT EXISTS (SELECT * FROM pg_catalog.pg_constraint AS o WHERE o.conindid=i.indexrelid AND o.contype IN ('u','p'))) SELECT sk.table_schema, sk.table_name FROM simple_keys AS sk INNER JOIN information_schema.columns c USING (table_schema, table_name, column_name) LEFT JOIN information_schema.domains d USING (domain_schema, domain_name) INNER JOIN information_schema.schemata s ON sk.table_schema=s.schema_name WHERE (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%' OR C.is_identity='YES') AND (sk.table_schema = 'public' OR S.schema_owner<>'postgres') AND Upper(sk.column_name)='ID' AND c.data_type IN ('smallint','integer', 'bigint') ORDER BY sk.table_schema, sk.table_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 |
---|---|
Database design antipatterns | Queries of this category provide information about possible occurrences of SQL database design antipatterns. |
Naming | Queries of this category provide information about the style of naming. |
Sequence generators | Queries of this category provide information about sequence generators and their usage. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
Reference |
---|
This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 4: ID Required. |
Dintyala, P., Narechania, A., Arulraj, J.: SQLCheck: automated detection and diagnosis of SQL anti-patterns. In: 2020 ACM SIGMOD International Conference on Management of Data, pp. 2331–2345. (2020). https://doi.org/10.1145/3318464.3389754 (Generic primary key (ID, code)) |
Balogh, G., Gergely, T., Beszédes, Á., Szarka, A., Fábián, Z.: Capturing expert knowledge to guide data flow and structure analysis of large corporate databases. Acta Polytechnica Hungarica 16(4), 7–26 (2019). (Using monolithic primary keys) |