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.

Framework News

I made some tests to store data from a form to the database more effectively. The CRUD functions and the transactions seem to work fine now. I am still looking for a more effective way to store data from a form to the database, without too much boilerplate code. And it should work with WinForms. Standard data binding with WinForms (bind a control to a data source) has not convinced me yet. I know WPF has better features for data binding, but I want to be independent from WPF (more on this topic another time).

Download NotReallyORM_0.51, try it out, read the comments in source code. And, yes, you can use the framework for ASP.NET, too (it should be thread safe).

Storing Binaries? In the Database, of course!

This blog is driven by WordPress. As far as I can see WordPress would never try to save pictures, videos or pdf files into its MySQL database – in the database you find only links to the file system. This makes sense to me. I cannot imagine a better way to deliver web pages fast and reliable, especially when you have scenarios with many requests per second.

In my job we normally do not program web pages. Will the principle “Do not store binaries in the database” always fit? I remember the time two years ago when I began to design a document management system. It should store 1.6 million pages per year, with multi-user access in a terminal server environment, powered by an SQL Server database on a 64 GByte machine as back end. The customer’s hardware provider gave me two advices: 1. Use transactions. 2. Store the documents in the database. Well, the system is now working productive for one year, and I am very grateful that I followed his hints.

Today I can warmly recommend storing binary data in the database. It is reliable and stable. You can use complex database transactions that include storing the binary, storing process information, storing foreign key relations and so on. You can easily handle concurrent access. And last but not least: The user experience when saving and retrieving documents is not “very slow”. It is just a bit slower compared to working with documents in the file system. I admit there is one restriction. You should not handle 500 MByte videos this way. The documents I am speaking about are 100 – 500 KByte, mostly PDF format. So this is not a good solution for YouTube…

There is a very good article from Microsoft Research that made me to dare this approach in a production environment. I also included the storing of binaries in my NotReallyORM framework.

What are your experiences on that? So far I have tried it only for Microsoft SQL Server. Would be interesting to know how a MySQL database would behave, for example.

Why I do not use ORM, or: The Beginning

“It’s time to become a real modern software company. We should begin to use an ORM framework in our new projects, maybe the latest version of the Microsoft Entity Framework. Everybody uses ORM nowadays, Microsoft pushes it, why we don’t?” That’s what I told the colleagues in my team some months ago.

It was the time when I began to read books about Microsoft Entity Framework and set up some experimental applications. I was fed up with writing boilerplate code for simple CRUD operations and hoped for some magic that was promised when you use these Entity Objects with data binding. Unfortunately the members of my team were not that enthusiastic about my ideas. You must know: We do a lot with SQL in our development, and we never made bad experiences with these “old school” handmade queries.

After some discussions I thought about my “modern” point of view and asked Mr. Google about some critical writing on ORM. One of the most inspiring texts for me was Kenneth Downs’  essay “Why I do not use ORM” in his blog. There you also find a newer version with some other aspects. Well, this was only the begin of the journey. But it was one of many impacts that led me to start this blog and to try an alternative with the NotReallyORM framework.

So I invite you to share your thoughts with me, to try out the framework, to make suggestions and to ask some questions about mainstream thinking.