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
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 |