May 2, 2014 at 6:50 am
A meaningless PK is DEFINITELY my number one rule. I've been programming databases for over 25 years and I've been burned so many times by supposedly immutable keys that I no longer listen to management when they try to tell me what to use as a primary key. I'll keep my own immutable key, thank you.
May 2, 2014 at 7:38 am
Bill Talada (5/2/2014)
I've been programming databases for over 25 years and I've been burned so many times by supposedly immutable keys that I no longer listen to management when they try to tell me what to use as a primary key.
Totally agree with that. Immutability is a myth and that's true even of "meaningless" surrogate keys sometimes. Uniqueness is no less important even for key values that can change though. Often the enforcement of uniqueness is important precisely because values can change and therefore might be at risk of causing key violations.
A very common example of a changing identifier is a login name. User login names are required to be unique in a system but that doesn't mean the user can't decide to change his/her, name as long is it always remains unique. It would be careless and poor database design not to put a key constraint on user names where the name was required to be unique. Either PRIMARY KEY or UNIQUE NOT NULL serve that purpose equally well of course, so there isn't any special reason to require primary keys to be unchanging because you can always have a surrogate key in the same table as well if you need it.
When it comes to key design, aim to implement keys that are relatively stable rather than chase the illusion of absolute immutability. To do anything less would mean neglecting the business rules that are implicitly required of any useful data management system.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply