From: Grigory O. Ptashko <trancer@bk.ru>
To: Samer Abukhait <abukhait@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Please help to wite the constraint.
Date: Tue, 22 Nov 2005 13:10:29 +0300
Message-ID: <593430302.20051122131029@bk.ru> (raw)
In-Reply-To: <7d215b0c0511212155i53c44f0en3f91cd6bde3b2140@mail.gmail.com>
References: <1229661878.20051120223209@bk.ru>
<7d215b0c0511212155i53c44f0en3f91cd6bde3b2140@mail.gmail.com>
> 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
>>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: trancer@bk.ru, abukhait@gmail.com
Subject: Re: Please help to wite the constraint.
In-Reply-To: <593430302.20051122131029@bk.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox