Craig Farrell (10/13/2010)
I work in MS SQL Server and T-SQL. Because of that, and because an index can give me performance where a constraint will not, but still takes up the same system space and engine mechanics as a declared index under the hood, I would prefer the index.
I don't understand your claim that an index alone provides better performance than a uniqueness constraint. Can you back that up with an example? I'm pretty sure you'll find they are always exactly the same, in which case the constraint still has all the advantages I mentioned before.
To me, a useful key is something that if I found it yesterday with that value, I'll find it tomorrow with the same value, and I can use it to connect to data throughout the design's structure in place of the existing 'Primary Key' if I so chose. While this may not fall properly under the definition of generic db design standards (that I can't seem to get my hands on), it does fall under the definition of what myself and a number of DBAs I've worked with understand them to be in practical usage.
However, database users would disagree with you. End users don't use surrogate keys to identify data, they use natural keys (actually I much prefer the term "business keys" but it means exactly the same thing). Therefore identifying and enforcing the business keys is normally essential for the users to make sense of the data and to get correct results from it. That includes keys that change. If keys are being used by the business process as identifiers then they are least as important as other keys that don't change - perhaps more so. Keys therefore need to be enforced and properly identified in the database if you want to ensure correct results from it.
Furthermore, the distinction you are trying to make between changing and unchanging keys is ultimately impossible to determine. If all the key values on a row change then there is no sound basis for saying that any of them "changed" - it is simply a different row because rows are identified by their keys. It is usually impractical to prevent data from being changed at some point during its lifetime and often you may not even know if it has been. Therefore the defining property of a key that really matters is uniqueness, not immutability.