Composite key relationship - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Composite key relationship

Author

Topic

Mir
Starting Member

19 Posts

Posted - 2011-02-07 : 05:16:23

Hi guru's out there, please help...

This column is just one example, i just to know and understand the consept of using composite key.

I have 1st table named A which has 3 columns
DATE|SHIFT|BUSID|
which all three are primary key.

I have 2nd table named B which has 4 columns
DATE|SHIFT|BUSID|LOCATION|
which i want to make DATE,SHIFT and BUSID a Foreign key.

How can I make the relationship using the composite key?
I'm stuck.

regards,
Mir TQ guys

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 06:39:20

the three columns in Table A referencing another three different entities (tables)?

Mir
Starting Member

19 Posts

Posted - 2011-02-08 : 01:03:47

quote:
Originally posted by MIK_2008

the three columns in Table A referencing another three different entities (tables)?

Thanks for ur reply.
In table B DATE,SHIFT and BUSID is foreign key.
How can i create the relationship from A to B.
I tought i can create 1 to many relationship for all
3 column from A to B.

regards,
Mir

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-08 : 01:38:52

well yes these appears to be the columns referencing the corresponding columns in Table A. However would you also let me know as if the the three columns in the table A (which you trying to make as PK) are declared individually as primary keys for some other tables except these two. If it does so then I think it would be better if you introduce a new column to Table A say tableName_ID (as identity) and make it as a foreign key in the Table B. this would give you much flexible structure

any how if you want to be stick with composite PK and FKs approach ..yours wish

use following code SNIPs

1) primary Key

alter table TableName add constraint pk_tablename_columns primary key (columnName1,ColumnName2,ColumnName3);

2) foreign key
Alter table TableNameB add constraint fk_tablename_columns Foreign Key (columnName1,ColumnName2,ColumnName3)
References tableNameA (columnName1,ColumnName2,ColumnName3)

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources