Friday October 9th

Thursday October 8th

Composite keys are evil

Composite keys (multi column primary keys) make any kind of Object/Relational mapping and persistence in general harder. Life is so much easier with surrogate keys. You can always make unique constraints where it's necessary.


>> From anybody older than me out there, why were Composite keys so widely used in older databases? Was there a real reason? All I've ever seen from composite keys is pain. <<

Unh? They were not used at all in older DBs at all. We did not have the concept. What we did have was pointer chains to tie the records (NOT rows) together into hierarchies (IMS) and networks (IDMS, et al). When OO people start using auto-increments, they are mimicking two old technologies and don't know it.

Sometimes the auto-increment replaces a record number in an old magnetic tape system, so they provide an exposed physical locator. Each record on the tape would begin with bit flag (which you would never use in SQL!) to mark it as deleted or active. At some point in the tape handling the physical records would be merged or moved onto a new tape and those records were skipped to save space. Auto-increments don't often do that, so they get gaps.

The other use is to fake pointer chains with auto-increment numbers. Unfortunately, this does not work. In the old network databases we had tools for recovering broken pointer chains, for garbage collection, etc. The "id-iot" ( nickname I give t newbie who put a magical universal "id" declared as IDENTITY on tables and think it is a key). The programmer has a to either write a complete procedural code implementation of a network DB or decide by default to give up any hope of data integrity. They usually pick the second choice by default.

Since we have text editors with macros and row constructors in SQL, the argument that a magical universal exposed physical locator like "id" saves time is pure laziness as well as dangerous and redundant.

Commenting on Stories is limited for now and will open up to those recommended by the community. Learn how
Loading DotNetKicks...
brought to you by the Kicks Network