Re: Constraint on 2 column possible?

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.