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.

Initiate

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)

Cloutier

I know the above query, but problem is that i want to do it visually. You mentioned foreign key in screenshot. its fine but in case of composite primary foreign key relation it did not mentioned in screenshots...... – Initiate

@user3004110: if you need to reference a compound primary key, you just have to select more than one column in the dialog to define the foreign key! – Cloutier

GUI way was not working, T-SQL script worked right away – Brewton

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.

Devilkin

Answer 3

Add two separate foreign keys for each column.

Cellaret

If there's only a composite primary key on the referenced table, and no other declared keys, you cannot create a foreign key that only references one of the columns. – Purely

is it possible? I don't think so! – Mireille