Compound Primary Keys and Foreign Key Constraints – Traps and Oddities in SQL Server Manager

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.).

Framework News

During the last week I had to do with compound primary keys at work. Sometimes I find them more appropriate than my beloved auto-increment integer keys.

Reason enough to test the NotReallyORM framework with them. The framework was already equipped to use compound keys. After the tests I added some fixes. It now works fine with those keys, as far as I can see. In one of the next versions I plan to retrieve those keys automatically from the database.  I found a nice SQL expression for SQL Server for this purpose (explanations only in German, sorry).

Download the latest version NotReallyORM_0.52 and try it out.