Multiple foreign keys with the same name and information_schema
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
Jonathan Tapicer <tapicer@gmail.com> writes:
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?
Actually, the information_schema supposes that constraint names are
unique within a *schema*, not within a *catalog* (a/k/a database).
Don't know if that distinction can help you or not. You are correct
that Postgres is less rigid. We do not consider that to be a deficiency
on the Postgres side ;-)
If you want to use the information_schema to deal with this stuff, the
answer is to make sure that your application follows the SQL-standard
rule of not duplicating constraint names within a schema.
regards, tom lane
Actually, the information_schema supposes that constraint names are
unique within a *schema*, not within a *catalog* (a/k/a database).
Don't know if that distinction can help you or not. You are correct
that Postgres is less rigid. We do not consider that to be a deficiency
on the Postgres side ;-)
Yes, my bad. Anyway, it doesn't help, I have both tables on the same schema.
If you want to use the information_schema to deal with this stuff, the
answer is to make sure that your application follows the SQL-standard
rule of not duplicating constraint names within a schema.regards, tom lane
Yes, I know that following the SQL standards is the way to go, but
sometimes this has to be done in databases I don't design, so I have
to be prepared for every case. I think I'll use the pg_catalog for
this case.
Thank you for you answer,
Jonathan
check out this link. I it will be what you are looking for
http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Multiple-foreign-keys-with-the-same-name-and-information-schema-tp1921901p4303625.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.