Multiple foreign keys with the same name and information_schema

Поиск
Список
Период
Сортировка
От Jonathan Tapicer
Тема Multiple foreign keys with the same name and information_schema
Дата
Msg-id b12229ca-d1d9-4562-ba09-a408d9c82046@g23g2000vbr.googlegroups.com
обсуждение исходный текст
Ответы Re: Multiple foreign keys with the same name and information_schema
Re: Multiple foreign keys with the same name and information_schema
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Bill Moran
Дата:
Сообщение: Re: Postgres memory question
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Multiple foreign keys with the same name and information_schema