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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
