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! ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
