Don’t use automatically generated unique IDs

Saturday, 22 February 2003

Think carefully before using an automatically-generated unique ID as a primary key in a SQL table. Using such automatically-generated IDs introduces extra implementation detail. This is a pain to manage, especially if they are used as primary keys or worse, foreign keys in tables.

The best use for them is in a non-primary key field, with a uniqueness constraint on them. This allows quick and easy reference to a particular row. For example, you could retrieve rows with a complex query, keep the UIDs, and then use the UIDs to delete or update the rows. Note that such fields are generated automatically by some databases, like Oracle’s ROWID pseudocolumn.

Using them as foreign keys means it is very likely they will leak out of the database layer into the business layer. This complicates the business logic with no benefit.



You can leave a comment, or trackback from your own site.

  1. This is one of the worst advices I ever read on the web. THe complete opposite is true. If this was intended as a pun it certainly is not made clear.

  2. If this is the worst advice you’ve ever read on the web, then you haven’t read very much.

Leave a comment