
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