Обсуждение: Problem with non-unique constraint names

The following query against my PostgreSQL 8.0.x 'public' schema:

SELECT
    fkc.table_name as fk_table_name,
    fkc.constraint_name AS fk_constraint_name
FROM information_schema.table_constraints fkc
WHERE fkc.constraint_schema = 'public'
    AND fkc.constraint_type = 'FOREIGN KEY'
    AND (
        SELECT count(*) FROM information_schema.table_constraints dup
        WHERE dup.constraint_schema = 'public'
            AND dup.constraint_name = fkc.constraint_name
        ) > 1

Returns this:

fk_table_name                   fk_constraint_name
-----------------------------   ------------------------------
site_role___site_permission     fk_site_rol_site_role_site_rol
site_role_criterion             fk_site_rol_site_role_site_rol
teaching                        fk_teaching_teaching__teaching
teaching_package_distribution   fk_teaching_teaching__teaching

Since constraint name uniqueness is a SQL standard, I was surprised that
PostgreSQL doesn't enforce it.  I found one thread (from 2002) in the
archive that discusses this, but the thread ended inconclusively.  And I
just discovered a warning at
http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html that
these names aren't necessarily unique.

This is more than just a quibble about standards.  When there are duplicate
constraint names, I'm having trouble writing queries against the
information_schema that accurately return the FK columns of all the FKs that
reference a given table.

For example, this query gives some details of the two constraints named
'fk_teaching_teaching__teaching':

SELECT
    fkc.table_name as fk_table_name,
    fkc.constraint_name AS fk_constraint_name
FROM information_schema.table_constraints fkc
WHERE fkc.table_schema = 'public'
    AND fkc.constraint_name = 'fk_teaching_teaching__teaching'

It returns:

fk_table_name            fk_constraint_name
-----------------------------    ------------------------------
teaching                fk_teaching_teaching__teaching
teaching_package_distribution    fk_teaching_teaching__teaching

So far so good.  But what table is referenced by the FK in the 'teaching'
table?  One way to bridge across is via the info schema view
'referential_constraints':

SELECT
    fkc.table_name as fk_table_name,
    fkc.constraint_name AS fk_constraint_name,
    pkc.constraint_name AS pk_constraint_name,
    pkc.table_name as pk_table_name
FROM information_schema.table_constraints fkc,
    information_schema.referential_constraints r,
    information_schema.table_constraints pkc
WHERE fkc.table_schema = 'public'
    AND fkc.constraint_name = 'fk_teaching_teaching__teaching'
    AND fkc.table_name = 'teaching'
    AND r.constraint_schema = fkc.constraint_schema
    AND r.constraint_name = fkc.constraint_name
    AND pkc.constraint_schema = r.unique_constraint_schema
    AND pkc.constraint_name = r.unique_constraint_name

This returns:

fk_table_name fk_constraint_name   pk_constraint_name   pk_table_name
------------- -------------------- -------------------- -----------------
teaching      fk_teaching_teaching pk_teaching_type_lov teaching_type_lov
            __teaching
teaching      fk_teaching_teaching pk_teaching_package  teaching_package
            __teaching

The second row returned is bogus: there is no FK from 'teaching' to
'teaching package'.  The problem is that
information_schema.referential_constraints does not contain a table_name
column identifying the table that contains the FK, so there is no way to
specify which of the redundantly named constraints you want.

The other way to find the referenced table of a FK constraint is via
information_schema.constraint_column_usage, but it has the same problem: the
FK constraint you want to follow can be identified in
constraint_column_usage only by its schema and its name, which ain't enough
when constraints are redundantly named.

I shudder at the horrors that might ensue if PK constraints also had
duplicate names!

I can do what I need with queries on pk_catalog, but I would rather do it
through information_schema because it's based on a broader standard.  Is
there some way that I have missed to do it that way?

~ TIA
~ Ken

I think your two alternatives are to either make sure no constraint
names are duplicated, or add a unique key for conname to
pg_constraint.  I didn;t know constraint name was a SQL requirement.
I can see how the information_schema would be designed to assume they
were unique if it was.

"Ken Winter" <ken@sunward.org> writes:
> Since constraint name uniqueness is a SQL standard, I was surprised that
> PostgreSQL doesn't enforce it.  I found one thread (from 2002) in the
> archive that discusses this, but the thread ended inconclusively.

I think our position is pretty clear: we aren't going to enforce it.

The PG code does avoid choosing default constraint names that
duplicate an existing constraint.  So if you have this situation it's
presumably because you explicitly named two constraints the same.
Solution, of course, is don't do that if you need them to not be the
same.

            regards, tom lane