Script to Create Foreign Key on the Compound Primary Key


Compound Primary key is a primary key which is created on more than one column. Now the questions is how to create the foreign key for the compound primary key where it references more than one column.

Check the below example.

create table employee
(
	empID int not null,
	SSN int not null,
	name varchar(20)
)


ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)


create table EmpDetail
(
		empID int,
		SSN int,
		address varchar(20),
		city varchar(20),
		pin varchar(20)
)

ALTER TABLE dbo.empDetail
   ADD CONSTRAINT FK_Employee
   FOREIGN KEY(empID, SSN)
   REFERENCES dbo.employee(empID, SSN)


SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('employee','employeeDetail')

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s