ansaurus

Question

foreign keys in sql server 2005

Answer 1

A:

No, you cannot do this - if you reference a parent table from a child table, you have to reference all columns of the primary key on the parent table:

ALTER TABLE dbo.ChildTable
  ADD CONSTRAINT FK_ChildTable_ParentTable
  FOREIGN KEY (col1, col2) REFERENCES dbo.ParentTable(pkCol1, pkCol2)

You cannot suddenly introduce for "static" value into your reference.

What you could do on the child table would be to put a CHECK CONSTRAINT on that second column to make it a "static" value - but then you can never have any other value in that column:

ALTER TABLE dbo.ChildTable
  ADD CONSTRAINT chk_col2_Static CHECK (col2 = 4)

Is that what you are looking for??

marc_s 2009-11-20 12:42:18

thanks for you answer. I'm happy to reference all columns of the primary key - just that I want to specify a static value for the 2nd column.e.g primary table goodsid type name1 fruit apple2 fruit orange3 veg carrot ...I may want a foreign key on a column that enforces fruit types only. so the foreign key will reference the id column but also the secondary primary key column of type should constrained to fruit.this must be a common problem when using a 'generic' table with a 'type' descriminator column to store different things

Bal 2009-11-20 16:49:10

@Bal: maybe - but that's a rather bad design, in my opinion. I don't know of any way to specify that as a foreign key constraint.

marc_s 2009-11-20 17:13:05

The only real option I see is in SQL Server **2008** only - have a unique, filtered index on the one column on your main table where the second column is = 1 - then you could reference that filtered unique index from your child table. It's a bit of a hack, but it probably would work.

marc_s 2009-11-20 17:14:46

ansaurus

tags:

views:

answers:

foreign keys in sql server 2005

related questions