Date: Tue, 22 Nov 2005 13:10:29 +0300
From: "Grigory O. Ptashko" <trancer@bk.ru>
Reply-To: "Grigory O. Ptashko" <trancer@bk.ru>
Message-ID: <593430302.20051122131029@bk.ru>
To: Samer Abukhait <abukhait@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Please help to wite the constraint.
In-Reply-To: <7d215b0c0511212155i53c44f0en3f91cd6bde3b2140@mail.gmail.com>
References: <1229661878.20051120223209@bk.ru>
	<7d215b0c0511212155i53c44f0en3f91cd6bde3b2140@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> 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..


In my system I have to have arbitrary contact info records about my users. I mean not only fixed like names, email and phone but many many other fields which could be created dynamically. And all of them can ba in different languages. So this was kind of example:

contact_info_fields - arbitrary contact info fields
contact_info_records - users' contact info records

countries - sample contact info field with fixed range of values
countires_names - values of the field

Well, that was it, nothing more. Does it really look so wierd?
Please, I appreciate any comments.


> On 11/20/05, Grigory O. Ptashko <trancer@bk.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
>>