Reference with condition on other table column?

Поиск
Список
Период
Сортировка
От Andrei Bintintan
Тема Reference with condition on other table column?
Дата
Msg-id 20040603091627.258131E338@ar-sd.net
обсуждение исходный текст
Ответы Re: Reference with condition on other table column?
Re: Reference with condition on other table column?
Список pgsql-sql
Hi to all,
 
I have the following tables:

CREATE TABLE t1(
id serial PRIMARY KEY,
active boolean NOT NULL DEFAULT 'y'
,
n
um int4 NOT NULL,
);
CREATE UNIQUE INDEX t1_uniqueidx ON t1(num) WHERE active;

CREATE TABLE t2(
id serial PRIMARY KEY,
active boolean NOT NULL DEFAULT 'y'
,
n
um int4 NOT NULL,
);
CREATE UNIQUE INDEX t2_uniqueidx ON t2(num) WHERE active;

CREATE TABLE relations(
id serial PRIMARY KEY
,
id_t1 int4 NOT NULL REFERENCES t1(num),
id_t2 int4 NOT NULL REFERENCES t2(num)
);

On tables T1 and T2 the "num" columns have unique values for all lines that have active='y'(true).

How can I write a constraint on Table T1 and Table T2 that if the "num" from T1 and "num" from T2 are referenced from table "relation" than I cannot update the "active" field to "false". My target is that I don't want to have any reference from "relation" table to T1 and T2 where in the T1 and T2 the active field is "n"(false)

or with other words:

if a line from T1/T2 is referenced from table "relations" than I don't want to be able to put active='y'.


I hope I was so clear as possible.

Thnkx in advance for helping.

Andy.

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

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: bytea or blobs?
Следующее
От: Stef
Дата:
Сообщение: Re: Date format problems