what is the use of composite key? [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

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

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

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