x

composite foreign key relationship

Hi all,

I have to build the relationship between theese two tables: (orders head and orders details) So one record of TST have multiple records in DET

TST (parent table), which composite PK is:

numrif (int) datrif (date)

DET (child table), which composite PK is:

numrif (int) datrif (date) numdet (int)

How can I set up this one-to-many relationship?

Thanks

more ▼

asked Aug 21, 2012 at 03:24 PM in Default

avatar image

paocar75
30 1 1 4

Forgot the DB: Sql Server 2008

Aug 21, 2012 at 03:32 PM paocar75
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Follow this example

 if object_id('DET','U') is not null drop table DET
 if object_id('TST','U') is not null drop table TST
 go
 create table TST (numrif int not null, datrif date not null)
 alter table TST add constraint PK_TST primary key (numrif, datrif)
 
 create table DET (numrif int not null, datrif date not null, numdet int not null)
 alter table DET add constraint PK_DET primary key (numrif, datrif, numdet)
 
 alter table DET add constraint FK_DET_TST foreign key (numrif, datrif) 
                                references TST(numrif, datrif)
 
 --insert some data into TST
 insert into TST select 1, '1 Jan 2012'
 insert into TST select 2, '1 Feb 2012'
 insert into TST select 3, '1 Mar 2012'
 
 --insert some data into DET, enforcing the FK
 insert into DET select 1, '1 Jan 2012', 10
 insert into DET select 1, '1 Jan 2012', 20
 insert into DET select 2, '1 Feb 2012', 100
 
 --this will fail the FK
 insert into DET select 3, '1 Feb 2012', 999
more ▼

answered Aug 21, 2012 at 05:22 PM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

Hello Kev, thank you for your response. I followed your example and all works, also with integrity cascade reference. And I eventually found what was going wrong.. The PK in TST table was datrif,numrif and not numrif,datrif... :( thank you again, bye

Aug 22, 2012 at 07:30 AM paocar75

Yes the FK defintion needs to match the PK definition

Aug 22, 2012 at 07:36 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2070
x39
x5

asked: Aug 21, 2012 at 03:24 PM

Seen: 961 times

Last Updated: Aug 22, 2012 at 07:36 AM

Copyright 2016 Redgate Software. Privacy Policy