Composite keys are evil

home
database
view story

12 Kicks

submitted by dalziel(6230) 5 years, 3 months ago

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.

1 comment |category: Database |Views: 14
Tweet

tags: CompositeKeys CodeBetter Database another

Everyones tags:

Your Tags:

Please login or signup.

Add a live kick counter to your blog >> liveImage

You can even customize the image by choosing your own colors, and then clicking the button below to update the preview and the html code:

"Kick It" text
"Kick It" background
kick count text
kick count background
border

Simply copy and paste this HTML into your blog post.

Users who kicked this story:

goal(0) -

DotKicker(0) -

pmbrown(0) -

akcoder(830)

More who kicked this story...

Comments:

posted by jcelko(0) 4 years, 3 months ago 0

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

Most Active Users

What's this?