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),FOREIGNKEY (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),FOREIGNKEY (id_user) REFERENCES users (id),FOREIGN KEY (id_ci_field) REFERENCES contact_info_fields
(id),FOREIGNKEY (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
cancontain the following info: id_ci_field=1 and the VALUE field must contain a country's name but ONLY if it exists in
thecountries table (column 'name'). So it turns out to be a wierd foreign key. Is it possible to write such a
constraint?
Thanks!