Multiple foreign keys with the same name and information_schema

From: Jonathan Tapicer <tapicer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Multiple foreign keys with the same name and information_schema
Date: 2009-08-10 13:55:42
Message-ID: b12229ca-d1d9-4562-ba09-a408d9c82046@g23g2000vbr.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello everyone, I have a question regarding foreign keys and
information_schema. Given the following valid schema:

CREATE TABLE "Cat"
(
"IdCat" serial NOT NULL,
CONSTRAINT "PK_Cat" PRIMARY KEY ("IdCat")
);

CREATE TABLE "Art"
(
"IdArt" serial NOT NULL,
"IdCat" integer NOT NULL,
CONSTRAINT "PK_Art" PRIMARY KEY ("IdArt"),
CONSTRAINT "FK_Art_Cat" FOREIGN KEY ("IdCat")
REFERENCES "Cat" ("IdCat") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE "Cat2"
(
"IdCat2" serial NOT NULL,
CONSTRAINT "PK_Cat2" PRIMARY KEY ("IdCat2")
);

CREATE TABLE "Art2"
(
"IdArt2" serial NOT NULL,
"IdCat2" integer NOT NULL,
CONSTRAINT "PK_Art2" PRIMARY KEY ("IdArt2"),
CONSTRAINT "FK_Art_Cat" FOREIGN KEY ("IdCat2")
REFERENCES "Cat2" ("IdCat2") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
);

PostgreSQL, unlike other DBMSs, allows foreign keys on different
tables to have the same name (note FK_Art_Cat on "Art" and "Art2"). I
need to make a query to the information_schema catalog to get the
table referenced by a given field in a given table (eg: Art, IdCat
references Cat; Art2, IdCat2 references Cat2).

I was a able to do it using the pg_catalog tables, but I haven't found
a way to do it using information_schema since it relies on foreign
keys names being unique in the same catalog. Is this a known
limitation? Is there any way to do what I need with the
information_schema catalog? I want to make generic queries to use them
across different DBMSs that support the ANSI information_schema.

A possible solution would be adding the foreign key table_name to all
the tables on the information_schema that rely on foreign keys names
being unique, for the case I am talking about it would be enough to
have it the table referential_contraints.

Thanks,

Jonathan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-08-10 15:07:54 Re: Multiple foreign keys with the same name and information_schema
Previous Message Bill Moran 2009-08-10 13:46:21 Re: Postgres memory question