Find cases where a unique constraint (that is not case-insensitive or partial, i.e., applies only to certain rows) has been enforced by using a CREATE UNIQUE INDEX statement instead declaring a PRIMARY KEY, UNIQUE, or EXCLUDE constraint. You should try to work on as high level of abstraction as possible. According to the ANSI-SPARC Architecture indexes are a part of internal database schema whereas constraints are a part of conceptual schema, i.e., at the higher level of abstraction.
Notes
The query uses a system view and returns CREATE INDEX statement.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Drop the index and declare PRIMARY KEY or UNIQUE constraint instead.
Data Source
INFORMATION_SCHEMA+system catalog
SQL Query
SELECT schemaname, tablename, indexname, indexdef
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND
indexdef NOT LIKE '% WHERE %' AND
indexdef NOT LIKE '%(lower(%' AND
indexdef NOT LIKE '%(upper(%' AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(schemaname, tablename, indexname) NOT IN
(select
(select nspname from pg_namespace where oid=c.relnamespace) as table_schema,
c.relname as table_name,
o.conname as constraint_name
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
where o.contype in ('p','u','x')
and c.relkind = 'r')
ORDER BY schemaname, tablename, indexname;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
SELECT format('DROP INDEX %1$I.%2$I;', schemaname, indexname) AS statements
FROM pg_catalog.pg_indexes
WHERE indexdef LIKE 'CREATE UNIQUE INDEX%' AND
indexdef NOT LIKE '% WHERE %' AND
indexdef NOT LIKE '%(lower(%' AND
indexdef NOT LIKE '%(upper(%' AND
schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL) AND
(schemaname, tablename, indexname) NOT IN
(SELECT
table_constraints.table_schema,
table_constraints.table_name,
table_constraints.constraint_name
FROM
information_schema.table_constraints
WHERE
table_constraints.constraint_type IN ('PRIMARY KEY', 'UNIQUE'))
ORDER BY schemaname, tablename, indexname;
Drop the index.
Collections
This query belongs to the following collections:
Name
Description
Find problems about integrity constraints
A selection of queries that return information about the state of integrity constraints in the datadabase. 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 .
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Performance
Queries of this category provide information about indexes in a database.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.