Date: Sun, 20 Nov 2005 22:32:09 +0300
From: "Grigory O. Ptashko" <trancer@bk.ru>
Reply-To: "Grigory O. Ptashko" <trancer@bk.ru>
Message-ID: <1229661878.20051120223209@bk.ru>
To: pgsql-sql@postgresql.org
Subject: Please help to wite the constraint.
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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!