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 '12 at 03:24 PM in Default

paocar75 gravatar image

paocar75
30 1 1 2

Forgot the DB: Sql Server 2008
Aug 21 '12 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 '12 at 05:22 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

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 '12 at 07:30 AM paocar75
Yes the FK defintion needs to match the PK definition
Aug 22 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1816
x32
x5

asked: Aug 21 '12 at 03:24 PM

Seen: 645 times

Last Updated: Aug 22 '12 at 07:36 AM