how to create composite key in Table of SQL Server.

Description:-

In this example we explain that how to create composite key in SQL Server. How to reference foreign key to different child table with composite primary key. Or how to create many too many relationship in SQL Server. For example DemoTable1 have primary key (col1, col2, col3) so how to create a foreign key in DemoTable2 which will referred only (col1, col2) field of DemoTable1?

This scenario is not possible because error is generated like “The columns in table do not match an existing primary key or UNIQUE constraint.”  So to achieve this you have to define (col1, col2) are unique in a table then you can easily define a foreign key relationship, but it must be on a separate unique key, and not on the primary key

To achieve this following is script for table to define relationship with multiple primary key with multiple table

create table kk11

( A integer not null

, B char(2) not null

, C int not null

, primary key (A,B,C)

, unique (A,B,C)

);

create table kk21

( M integer not null

, N char(2) not null

, Z int not null

, foreign key (M,N,Z) references kk11 (A,B,C)

)