Query goal: | A surrogate key is a primary key that consist of one column. The values of this column do not have any meaning for the user and the system usually generates the values (integers) automatically. In case of defining a surrogate key in a table it is a common mistake to forget declaring existing natural keys in the table. If a key covers an optional column then it does not prevent duplicate rows where some values are missing and other values are equal. Because NULL is not a value and is not duplicate of another NULL the, follwing is possible: CREATE TABLE Uniq(a INTEGER NOT NULL, b INTEGER, CONSTRAINT ak_uniq UNIQUE (a, b)); INSERT INTO Uniq(a, b) VALUES (1, NULL); INSERT INTO Uniq(a, b) VALUES (1, NULL); |
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 |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH unique_columns AS (SELECT n.nspname AS table_schema, c2.relname AS table_name, a.attname AS column_name, c.relname AS index_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.indisprimary=FALSE AND a.attnum>=1 AND a.attisdropped = FALSE AND (n.nspname = 'public' OR u.rolname <> 'postgres')), all_unique_constraints_have_optional_columns AS (SELECT A.table_schema, A.table_name FROM unique_columns AS U INNER JOIN information_schema.columns A USING (table_schema, table_name, column_name) INNER JOIN information_schema.tables T USING (table_schema, table_name) INNER JOIN information_schema.schemata S ON A.table_schema=S.schema_name WHERE T.table_type='BASE TABLE' AND (A.table_schema = 'public' OR S.schema_owner<>'postgres') AND A.is_nullable='YES' GROUP BY A.table_schema, A.table_name HAVING Count(DISTINCT index_name)=(SELECT Count(DISTINCT index_name) AS cnt FROM unique_columns AS u WHERE u.table_schema=A.table_schema AND u.table_name=A.table_name)), tables_with_surrogate AS (SELECT c.table_schema, c.table_name , c.column_name FROM information_schema.columns c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name) INNER JOIN information_schema.schemata s ON c.table_schema=s.schema_name WHERE (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%' OR c.is_identity='YES') AND c.is_nullable='NO' AND 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 FROM tables_with_surrogate AS ts WHERE EXISTS (SELECT * FROM all_unique_constraints_have_optional_columns AS au WHERE ts.table_schema=au.table_schema AND ts.table_name=au.table_name) ORDER BY table_schema, table_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 |
---|---|
Default value | Queries of this catergory provide information about the use of default values. |
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. |
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). |