From: | Mike Rylander <mrylander(at)gmail(dot)com> |
---|---|
To: | PostgreSQL SQL <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Constraint on 2 column possible? |
Date: | 2005-01-27 12:02:43 |
Message-ID: | b918cf3d050127040219ccba91@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <klodoma(at)ar-sd(dot)net> wrote:
>
> Hi,
>
> I have a table:
> CREATE TABLE werke1(
> id SERIAL,
> id_hr int4 NOT NULL,
> id_wk int4 NOT NULL
> );
>
> CREATE TABLE contact(
> id SERIAL,
> type varchar(20),
> );
>
>
> Now id_hr and id_wk are all referencing the same table contact(id). In the
> contact table I have another column called type.
> How can I write a constraint that checks that id_hr references contact(id)
> and the contact(type='t1')
> and that id_wk references contact(id) and the contact(type='t2').
If I understand what you want, you can do this with a multi-column
foreign key and check constraints.
CREATE TABLE werke1 (
id SERIAL,
id_hr NOT NULL,
hr_contact NOT NULL CHECK (hr_contact = 't1'),
id_wk int4 NOT NULL,
wk_contact NOT NULL CHECK (hr_contact = 't2'),
CONSTRAINT werke1_hr FOREIGN KEY (id_hr,hr_contact) references contact(id,type),
CONSTRAINT werke1_wk FOREIGN KEY (id_wk, wk_contact) references contact(id,type)
);
This will cause the FKEY to match only contact entries that have the
correct combination of id and type.
Hope that helps!
>
> More explicit: the id_hr shows to the id from contact, and this line from
> contact must have the line type='t1'. The same for id_wk just the type is
> another.
>
> I can write:
> CREATE TABLE werke1(
> id SERIAL,
> id_hr int4 NOT NULL references contact(id),
> id_wk int4 NOT NULL references contact(id)
> );
> but how do I check also the type column?
>
> Best regards,
> Andy.
--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org
--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Davis | 2005-01-27 12:08:03 | Re: Constraint on 2 column possible? |
Previous Message | Andrei Bintintan | 2005-01-27 11:44:32 | Constraint on 2 column possible? |