Thursday, May 10, 2007

How to create foreign key reference to part of composite key .......

Just say two tables tbl1 and tbl2. tbl1 contains composite key (A,B,C). Then tbl2 contains composite key (A,B). When we want to create foriegn key to table tbl1(A,B) from tbl2(A,B), it gives error....

Because there is not a unique key in tbl1...

eg:

tbl1

A B C
----------
sa tt w
sa tt q

tbl2

A B
------
sa tt

In that case, there is no unique key on tbl1 that match to tbl2.

solution is, you have to create unique index to that A and B feilds in tbl1 table.

eg:

CREATE UNIQUE INDEX tbl1_A_B ON tbl1 (A,B);

No comments: