From: Grigory O. Ptashko <trancer@bk.ru>
To: pgsql-sql@postgresql.org
Subject: Please help to wite the constraint.
Date: Sun, 20 Nov 2005 22:32:09 +0300
Message-ID: <1229661878.20051120223209@bk.ru> (raw)
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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: trancer@bk.ru
Subject: Re: Please help to wite the constraint.
In-Reply-To: <1229661878.20051120223209@bk.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox