Compound primary keys sometimes seem a more natural approach than the use of auto-increment integer keys. Now I discovered some unexpected behaviour when I tried to reference them as foreign keys from another table with the SQL Server Manager.The basic documentation you find here and here on MSDN.
What I did not find there: The order of the compound primary keys as stored in the database is not necessarily the same order you see in the object explorer of the Server Manager.
So when you try to establish a foreign key constraint you may get an error and SQL Manager will not be able to create it. After some googling I found hints, but the best workaround was told me by a colleague. So this is the recipe:
Go to the table with the compound primary keys in the object explorer, right click on “Keys” and choose “Create Script”. You will find some Transact-SQL like
ALTER TABLE TableWithPrimaryKeysADD CONSTRAINT PK_TableWithPrimaryKeys_TransactionID PRIMARY KEY CLUSTERED (TransactionID_1, TransactionID_2, TransactionID_3)
When you created this table with the SQL Manager, the order of TransactionID_1, TransactionID_2, TransactionID_3 is the order you marked them with ALT-Click, before you chose them as primary keys (really, try it out!).
Now you want to construct the foreign key constraints. The foreign keys in this have the same names like the corresponding primary keys. To do this in Transact-SQL you will need something like this:
ALTER TABLE TableWithForeignKeys ADD CONSTRAINT FK_TableWithForeignKeys_TransactionID_1 FOREIGN KEY (TransactionID_1, TransactionID_2, TransactionID_3) REFERENCES TableWithPrimaryKeys (TransactionID_1, TransactionID_2, TransactionID_3)
You see the order of the foreign keys? They must have exactly the same order like in TableWithPrimaryKeys. For me this a reason to work more with Transact-SQL than before. It is better than to rely only on the the SQL Manager (nevertheless I still find the manager quite useful in many cases.).