CODEWITHSUNDEEP
postgresql
xonya
xonya

Reputation: 2484

PostgreSQL: constraint name is not necessarily unique

I'm trying to list all the foreign keys in a schema, and, for each key, listing the columns involved. So I'm querying the pg_catalog.pg_constraint and information_schema.columns tables.

I need a way to distinguish the keys, to do the second query and retrieve the key columns list.

I thought to use the constraint name (the conname column, in pg_catalog.pg_constraint table), but the PostgreSQL documentation about pg_constraint says that the constraint name is not necessarily unique! Why? I can't find additional information about this fact in the documentation.

Is the couple connamespace + conname unique?

This are my queries:

Retrieve the lists of foreign keys from and to the given schema:

SELECT
conname AS constraint_name,
conrelid::regclass AS referring_table, 
confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint
WHERE contype = 'f' AND ((conrelid::regclass || '' LIKE '<my_schema_name>.%') OR (confrelid::regclass || '' LIKE '<my_schema_name>.%'))

Retrieve the list of columns of a given key:

SELECT
c.column_name AS key_column
FROM information_schema.columns c
JOIN pg_catalog.pg_constraint r ON (c.table_schema || '.' || c.table_name) = (r.conrelid::regclass || '')
WHERE r.conname = '<my_constraint_name>'

Thanks for your help.

Upvotes: 10

Views: 5216

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246798

Constraint names are unique only within the object on which they are defined.

Two different tables (or domains) can have constraints with the same name.

The exception to that rule are constraints that are implemented by an index (primary key, unique and exclusion constraints). The index that implements the constraint has to have the same name as the constraint, and indexes share a namespace with tables, views, sequences and composite types, which means that no two such objects in the same schema can have the same name.

Upvotes: 26

Related Questions