Composite FK

You can have a composite PK but can you have a composite FK?
I want to make sure that the combination orderid and custid in
"orderhistory" table is always the same as a combination of orderid custid
in the "orders" table.
What's the best way to do this? Can I do something with a FK to enforece
this? Or do I need to use a trigger?Not a problem:
create table MyTable
(
id int primary key
, orderid int not null
, custid int not null
, foreign key FK1_MyTable references MyOtherTable (orderid, custid)
)
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dave" <dave@.nodomain.tv> wrote in message
news:eVje8kcEEHA.624@.TK2MSFTNGP10.phx.gbl...
You can have a composite PK but can you have a composite FK?
I want to make sure that the combination orderid and custid in
"orderhistory" table is always the same as a combination of orderid custid
in the "orders" table.
What's the best way to do this? Can I do something with a FK to enforece
this? Or do I need to use a trigger?|||Absolutely. First you need to ensure that the "parent" table has its primary
key defined as (orderid, custid). Once the primary key is in place, the DDL
to create your FK might look something like:
alter table foo add constraint foo_fk foreign key (orderid, custid)
references parenttable (orderid, custid)
--Brian
(Please reply to the newsgroups only.)
"Dave" <dave@.nodomain.tv> wrote in message
news:eVje8kcEEHA.624@.TK2MSFTNGP10.phx.gbl...
> You can have a composite PK but can you have a composite FK?
> I want to make sure that the combination orderid and custid in
> "orderhistory" table is always the same as a combination of orderid custid
> in the "orders" table.
> What's the best way to do this? Can I do something with a FK to enforece
> this? Or do I need to use a trigger?
>
>
>|||Thanks guys for the prompt and useful info.
"Dave" <dave@.nodomain.tv> wrote in message
news:eVje8kcEEHA.624@.TK2MSFTNGP10.phx.gbl...
> You can have a composite PK but can you have a composite FK?
> I want to make sure that the combination orderid and custid in
> "orderhistory" table is always the same as a combination of orderid custid
> in the "orders" table.
> What's the best way to do this? Can I do something with a FK to enforece
> this? Or do I need to use a trigger?
>
>
>