Grant Fritchey (12/16/2008)

---

MGS (12/16/2008)

---

I would like to be able to create a foreign key reference in a child table to part of a compound primary key in the parent table. But I'm pretty sure that's not allowed. If it is will someone point out the proper syntax? If not is there another way I can use a constraint to enforce some referential integrity between part of the key in the parent table and that attribute in the child table? I'm using SQL Server 2000 but will ultimately have to port to 2005 and 2008.

I know I could do this using a trigger but I'd rather use a constraint if possible.

This all comes out of the requirement that I accomodate history (changes over time) in the same table as the active record. I would not have designed it this way but it is a hard specification that I cannot change.

Here's the scenario:

create table t1 col_1 int not null,

col_2 int not null,

constraint primary key (col1,col2)

create table t2 col_A int not null,

col_1 int not null FOREIGN KEY REFERENCES t1(col_1)

but this doesn't work because the foreign key must reference the whole primary key.

It's the whole key or you can use a unique index and then reference that, but you can't do a constraint against only part of the key or part of a unique index. I had a design similar to what you're doing and had to use triggers. Not the prettiest, but it works.