Re: [SQL] Constraint on 2 column possible?

[Sean Davis]

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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
     joining column's datatypes do not match