Thread: 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.