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 <[EMAIL PROTECTED]> 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
>
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
