what is the use of composite key? [Resolved]

Posted by Sangeetha Mani under Sql Server on 12/6/2013 | Points: 10 | Views : 1351 | Status : [Member] | Replies : 4
hi all, i have three tables,

1. master table

2. transaction table

3. master_transaction_link table

here my question is, in link table, which has id,mstrid,transid - mstrid is id of mster table and transid is id of transction table

why should i set the mstrid and transid as composite key in link table.?


what is the use of composite key in link table?

sangeetha.m


Responses

Posted by: Bandi on: 12/6/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Simply we can say that master and transaction id comibination should not be duplicated..
If you have Many-to-Many relationship among entities, you have to choose composite foreignkeys


For example, Courses and Faculty is many to many relationship
More than one course can be handled(teach) by many faculty. Here we must represent Many-to-Many relationship
and have an unique id for each combination so that we can directly asssign ID to a student

--sample Script
create table master1 (id int primary key, name varchar(100)) 
insert master1
select 1, 'master1' union all
select 2 , 'master2'

create table transaction1 (id int primary key, name varchar(100))
insert transaction1
select 1 , 'tran1' union all
select 2 , 'tran2' union all
select 3 , 'tran3'

create table master_transaction_link (id int primary key, masterid int references master1(id) , tranId int references transaction1(id), constraint com_UK UNIQUE(masterid,tranId) )
insert master_transaction_link
VALUES
(1, 1 , 1) ,
(2, 1 , 2) ,
(3, 1 , 3) ,
(4, 2 , 1) ,
(5, 2 , 2) ,
(6, 2 , 3) ,
(7, 1 , NULL) ,
(8, 2 , NULL) ,
(9 , NULL, 1) ,
(10, NULL, 2) ,
(11, NULL, 3) ,
(12, NULL, NULL)

SELECT * FROM master1
SELECT * FROM transaction1
SELECT * FROM master_transaction_link



Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sangeetha Mani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sangeetha Mani on: 12/6/2013 [Member] Starter | Points: 25

Up
0
Down

Resolved
Hi all , thanks for your valuable answers.,which help me to get clear in my concept. once again thanks for that.

Let i wanna be share my answer about composite key what i handled in my project.

in my project , there is neighborhood group and training program provided by government.

so, each group should attend one training program(lot of program) provided by government. here we must represent 1 to n relationship. and have an unique id for an each combination.

eg:

training program - master table
neighborhood group - transaction table

Master_Transanction_link

id traingId nhgoupId
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2

so , each group can attend a single program provided by government.

sangeetha.m

Sangeetha Mani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/6/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,

Composite keys are used when there is no a single field uniquelly identifying each row i a table. Then you need to take more fields together to get a primary key of a table, which is then called composite key.

For example, you have a customers table with the following fields: First name, Last name, Date of birth, Address, Gender. To uniquelly identify a customer, you have to know his First name, Last name, Date of birth and Address - this would be a composit key (two customers with the same name and address can exist as well as two with the same name and birthday).

In database design surrogate keys, which are mostly automatically generated numbers, are often used instead of composite ones. Unique constraint is defined on the composite key and the surrogate key is used as a primary key of the table.

Sangeetha Mani, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: vishalneeraj-24503 on: 12/6/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
A composite key is a key made of more than one COLUMN.
It is used when more than COLUMN is required in the KEY.

Sangeetha Mani, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response