Postgres Pro
Downloads
Home   >   mailing lists

Thread: Constraint on 2 column possible?

Constraint on 2 column possible?

From
"Andrei Bintintan"
Date:
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').
 
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.

Re: Constraint on 2 column possible?

From
Mike Rylander
Date:
On Thu, 27 Jan 2005 13:44:32 +0200, Andrei Bintintan <klodoma@ar-sd.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@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org


-- 
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org

Re: Constraint on 2 column possible?

From
Sean Davis
Date:
On Jan 27, 2005, at 6:44 AM, Andrei Bintintan 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),
> );
>


It looks like you want a two-column primary key for table contact and
then you can reference contact(id,type).

Sean


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