From: | "Grigory O(dot) Ptashko" <trancer(at)bk(dot)ru> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Please help to wite the constraint. |
Date: | 2005-11-20 19:32:09 |
Message-ID: | 1229661878.20051120223209@bk.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello, everybody!
I don't whether it is possible to do the following but anyway I can't.
I need to write a constraint as described below.
Here are four tables:
CREATE TABLE countries
(id SERIAL,
name VARCHAR(255),
PRIMARY KEY (id)
);
CREATE TABLE countries_names
(id INT NOT NULL,
id_lang INT NOT NULL,
name VARCHAR(255),
PRIMARY KEY (id, id_lang),
FOREIGN KEY (id) REFERENCES countries (id),
FOREIGN KEY (id_lang) REFERENCES lang (id)
);
CREATE TABLE contact_info_fields
(id SERIAL,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE contact_info_records
(id_user INT NOT NULL,
id_ci_field INT NOT NULL,
id_lang INT NOT NULL,
value TEXT,
PRIMARY KEY (id_user, id_ci_field, id_lang),
FOREIGN KEY (id_user) REFERENCES users (id),
FOREIGN KEY (id_ci_field) REFERENCES contact_info_fields (id),
FOREIGN KEY (id_lang) REFERENCES lang (id)
);
The last table contains contact information records of different types. These types are taken from the table contact_info_fields. In particular, there can be the type 'country' say with id=1. Then the contact_info_records table can contain the following info: id_ci_field=1 and the VALUE field must contain a country's name but ONLY if it exists in the countries table (column 'name'). So it turns out to be a wierd foreign key. Is it possible to write such a constraint?
Thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2005-11-20 23:35:57 | Re: Is it possible to redirect an update/insert/delete to a different table? |
Previous Message | Andy Ballingall | 2005-11-20 17:15:22 | Re: Is it possible to redirect an update/insert/delete to a different table? |