Please help to wite the constraint.

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!

Responses

Browse pgsql-sql by date

  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?