Problem with non-unique constraint names

Поиск
Список
Период
Сортировка
От Ken Winter
Тема Problem with non-unique constraint names
Дата
Msg-id 1FE89E589F974E3AB3AAD9F0FCE5C7B6@KenIBM
обсуждение исходный текст
Ответы Re: Problem with non-unique constraint names  (Scott Marlowe <scott.marlowe@gmail.com>)
Re: Problem with non-unique constraint names  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Raymond O'Donnell
Дата:
Сообщение: Re: Track a function
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Problem with non-unique constraint names