Re: Please help to wite the constraint.

I can't understand why you are doing this big cycle.. but certainly
when constraints can't help you.. you can use triggers to enforce
integrity..

On 11/20/05, Grigory O. Ptashko <trancer(at)bk(dot)ru> wrote:
> 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
>