Creating a Composite Foreign Key

In previous post we discussed various types of primary keys such as natural key, surrogate key and composite key. To recap, a composite primary key is a combination of more than one columns. Ordinarily a single column primary key is preferable but there are situation as we discussed previously when it makes sense to create a composite primary key.

Naturally, if you want to use composite primary key as a foreign key in other tables, you will have to create the columns in the secondary table that corresponds to the primary table. For example, you have a table called Customers and you have a composite primary key "FirstName, LastName" in this table. To use this key as a foreign key in another table say "CustomerAddresses" you will have to create the FirstName and LastName columns in CustomerAddresses table and then add the referential integrity. (I am not saying this is a good way to design your table schema, this is just an example).

Below is the script that you can run to create a foreign key in CustomerAddresses table.

ALTER TABLE dbo.CustomerAddresses
   ADD CONSTRAINT FK_Customer
   FOREIGN KEY(FirstName, LastName)
   REFERENCES dbo.Customers(FirstName, LastName)

You can use the same script to create foreign key constraint on any other columns. For example, if your Customers table has "CustomerID" as primary key, then you would add "CustomerID" column in CustomerAddresses table and then use the script above replacing "FirstName, LastName" with "CustomerID".

Thank you.