A little advantage of Django's automatic primary keys

January 31, 2011

If you don't specify manual primary keys on your schema tables, Django will automatically give you one in the standard form of an auto-incrementing integer. I believe that this is standard ORM behavior and you'll find it in pretty much any ORM interface. After coming to my SQL foreign key realization I've realized that there's an additional advantage to having this sort of primary key (over and above the ability to fix oopses in what is normally that table's primary key).

The advantage is simple: the primary key is unlikely to ever repeat, even after you delete entries.

(I think that technically you could have ID counter rollover, but this would require a lot of row inserts even with a 32-bit ID field.)

That the primary key doesn't repeat means that the primary key will uniquely identify a particular record even after the record is deleted. This is an extremely valuable property for historical records, such as audit log information. All you have to do is explicitly record the primary key value and you're done, you're now pretty much guaranteed to be able to accurately identify just what object you were talking about.

This property is not shared by many explicit primary key fields. While they might be unique in the live data, there's no guarantee that they will not repeat over time. For example, a simple account request system might use the requested login as the primary key on the grounds that you can't allow two requests for the same login. But then you have a request for a login name that is denied and deleted, and later you have another request for the same login; now any audit data or the like might be referring to two different requests.

So now I have two reasons to be happy that I've switched my Django schemas to not using explicit primary keys and let Django handle it for me.

(All of this is probably making SQL people twitch. Certainly it's doing that to the 'normalized SQL database design' portion of my brain.)

Written on 31 January 2011.
« An obvious realization about SQL foreign keys
There are two sorts of standards in the world »

Page tools: View Source, Add Comment.
Login: Password:
Atom Syndication: Recent Comments.

Last modified: Mon Jan 31 02:10:06 2011
This dinky wiki is brought to you by the Insane Hackers Guild, Python sub-branch.