Michael Valentine Jones (10/15/2010)
David Portas (10/15/2010)
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).A primary key means exactly the same thing as an alternate key so as you've written it this advice doesn't make much sense. I expect you mean "avoid using business data in any foreign key references" but that's different. Anyway, using natural keys for foreign key references can be very useful, advantageous and sometimes necessary.
That seems like a bit of nit picking. I think it's obvious in the context that what I meant is to make the surrogate key the primary key constraint and to put unique constraints on the remaining keys.
In light of your later comments I think it's far from obvious what you mean. You say:
I have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.
But if a "key" is not unique then it isn't suitable as a natural key whether referenced by a foreign key or not! So how can you say that you are in favour of such natural keys but opposed to their use as foreign keys? Similarly for immutability - especially since immutability of keys is anyway an illusion or at least a rather arbitrary point of view. What matters is not whether key values can change but whether they accurately identify the things they are supposed to identify.