David Portas (10/13/2010)
No it's not the same for the reasons I already stated: Constraints are standard SQL, unique indexes are not; Constraints are understood and used by more people; Some software tools will recognise keys declared as constraints but will not recognise those created using the CREATE INDEX syntax. In my view those are all very good reasons to use uniquness constraints and avoid creating unique indexes directly.
Here I feel we must disagree. Since finding a free copy of the 97 standard (or 99) has been dubious at best, I can't double check your comment, so I'll just assume it's accurate. Seems to make sense. However, I don't work in the SQL standard. 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.
You make a good point though about 3rd party tools. I rarely use them so it rarely comes up as a difficulty for me, and that may be part of the difference.
That's more or less what I thought you meant, but it's not technically correct. Two things make a key (aka "candidate key") and they are 1) uniqueness and 2) irreducibility.
...and is also known as the minimal superkey. I'm familiar with the theory. Practice, however, dictates other usage...
Those are the two fundamental qualifications for a key. Stability is usually desirable as well but the fact that a key changes does not make it any less of a key than some other key that doesn't change. Of course that's just terminology I suppose, the effect is exactly the same whether you choose to call it a key or not - the same considerations apply. But I think it's worth saying because a lot of people do seem to get very confused about what keys are and why they are important.
I'm avoiding the term "alternate key" because primary and alternate keys are really the same thing. All are candidate keys.
You are absolutely correct according to the dictionary, and theoretical design. I grant that the terminology is accurate, and I went and double checked just to make sure I remembered it correctly. 🙂
This, perhaps, is where we are disconnecting in our discussion, because theoretical design is like the ISO standard. Nice to have, but not always practical. Identification of candidate keys are useless in practical design other then as the first step to identifying useful primary and alternate keys, or perhaps to assist in search mechanic selectivity to acquire more speed and better index usage.
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.
[EDIT:] Aaaaaand I finally look at the top of the screen and realize I'm in the "Relational Theory" Forum... I'll shaddup now. [/EDIT]
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA