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)