Re: Constraint on 2 column possible? - Mailing list pgsql-sql

On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <[email protected]> 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
[email protected]
GPLS -- PINES Development
Database Developer
http://open-ils.org


-- 
Mike Rylander
[email protected]
GPLS -- PINES Development
Database Developer
http://open-ils.org