How to create composite foreign key in sql server management studio 2012

Question

I can successfully create composite primary key in sql server management studio 2012 by selecting two columns (OrderId, CompanyId) and right click and set as primary key. But i don't know how to create foreign key on two columns(OrderId, CompanyId) in other table by using sql server management studio 2012.

Answer 1

In Object Explorer, go to your table and select Keys > New Foreign Key from the context menu:

From the dialog box that pops up, click on the Add button to create a new foreign key:

Give it a meaningful name and then click on the ... button to open the Tables and Columns specification dialog box:

Fill in the necessary columns for the parent and the child tables, click OK and you're done!

Or much easier and more efficiently - use a T-SQL script!

ALTER TABLE dbo.OtherTable
ADD CONSTRAINT FK_OtherTable_ParentTable
FOREIGN KEY(OrderId, CompanyId) REFERENCES dbo.ParentTable(OrderId, CompanyId)

Answer 2

If you open the submenu for a table in the table list in Management Studio, there is an item Keys. If you right-click this, you get New Foreign Key as option. If you select this, the Foreign Key Relationships dialogue opens. In the section (General), you will find Tables And Columns Specifications. If i open this, i can select multiple columns.

Answer 3

Add two separate foreign keys for each column.