Query goal: | Find base table and foreign table constraint names that are used in a database more than once (possibly in different schemas or in case of different types of constraints). Different things should have different names. But here different constraints have the same name. Also make sure that this is not a sign of duplication. |
Notes about the query: | In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser. The query considers only constraints that are connected directly with a base table or a foreign table, i.e., it does not consider domain CHECK constraints. The query does not consider constraint triggers becasuse these could be used to implement foreign key constraints and the same name could be used in case of different triggers that implement the same constraint. |
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 so that the name is unique within a schema. Follow a naming convention. |
Data source: | system catalog only |
SQL query: | Click on query to copy it
WITH constraints AS (SELECT conname, nspname AS table_schema, c.relname as table_name, CASE WHEN contype='c' THEN 'CHECK' WHEN contype='f' THEN 'FOREIGN KEY' WHEN contype='p' THEN 'PRIMARY KEY' WHEN contype='u' THEN 'UNIQUE' WHEN contype='x' THEN 'EXCLUDE' END AS contype, CASE WHEN c.relkind='r' then 'BASE TABLE' WHEN c.relkind='f' then 'FOREIGN TABLE' END AS table_type FROM pg_constraint o INNER JOIN pg_namespace n ON o.connamespace=n.oid INNER JOIN pg_class c on c.oid = o.conrelid WHERE (substring(pg_get_constraintdef(o.oid),7) NOT LIKE '%NOT NULL' OR substring(pg_get_constraintdef(o.oid),7) IS NULL) AND c.relkind IN ('r', 'f') AND o.contype<>'t') SELECT conname, Count(*) AS number_of_appearances, string_agg(table_type || '.' || table_schema || '.' || table_name || ' (' || contype || ')',';<br>' ORDER BY table_schema, table_name, contype) AS tables FROM constraints GROUP BY conname HAVING Count(*)>1 ORDER BY Count(*) DESC; |
Category name | Category description |
---|---|
Comfortability of database evolution | Queries of this category provide information about the means that influence database evolution. |
Duplication of implementation elements | Queries of this catergory provide information about the duplication of the database objects. |
Naming | Queries of this category provide information about the style of naming. |
Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |
Reference |
---|
https://dba.stackexchange.com/questions/152510/enforce-constraint-name-uniqueness |
https://www.postgresql.org/message-id/3724.1094436837%40sss.pgh.pa.us |
https://www.sqlstyle.guide/ |