Foreign Key with Constant

Поиск
Список
Период
Сортировка
От Gregory Wood
Тема Foreign Key with Constant
Дата
Msg-id 068b01c2d9c7$c7df3a90$4f89ffcc@eng3
обсуждение исходный текст
Ответы Re: Foreign Key with Constant
Re: Foreign Key with Constant
Список pgsql-general
Is it possible to create a foreign key that uses a constant for one of the
fields. In other words, I want to foreign key one value into another table,
but only if a constant on the first table matches another column in the
second table.

For example, I want to do something similiar to this:

CREATE TABLE fk_test_lookup
(
  LU_ID INTEGER PRIMARY KEY,
  LU_Type CHAR
);

INSERT INTO fk_test_lookup (LU_ID,LU_Type) VALUES (1,'A');
INSERT INTO fk_test_lookup (LU_ID,LU_Type) VALUES (2,'A');
INSERT INTO fk_test_lookup (LU_ID,LU_Type) VALUES (3,'O');

CREATE TABLE fk_test_primary
(
  P_ID INTEGER PRIMARY KEY,
  FOREIGN KEY (P_ID,'A') REFERENCES fk_test_lookup (LU_ID,LU_Type)
);

CREATE TABLE fk_test_secondary
(
  S_ID INTEGER PRIMARY KEY,
  FOREIGN KEY (S_ID,'O') REFERENCES fk_test_lookup (LU_ID,LU_Type)
);

So that I can only insert P_ID 1 and 2 into fk_test_primary and S_ID 3 into
fk_test_secondary?

I'd rather do this without adding a 'constant' column, or messing too
extensively without triggers, but I fear that one of these might be
necessary.

Thanks,
Greg


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

Предыдущее
От: Christopher Murtagh
Дата:
Сообщение: Re: Slow update - index problem?
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: Foreign Key with Constant