Base tables that have a surrogate key and all its unique constraints have an optional column

Back to Catalog
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);
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Data Source INFORMATION_SCHEMA+system catalog
SQL Query 
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 ;
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, 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 .
Categories

This query is classified under the following categories:

NameDescription
Default valueQueries of this catergory provide information about the use of default values.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.
UniquenessQueries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
Validity and completenessQueries 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).