Technically, a key is any column (or a set of columns) that you use to find records. It does not need to be unique. But it tends to be more useful if it is more selective, I guess that's why they say 'to some degree'.
So now, if a key is also unique, it is called a 'candidate key'.
In general, you can have more than one candidate key in a table. For example, a natural key (such as order#) and a surrogate key (e.g. 'id').
Among candidate keys, you pick one, and call it 'primary key'.
Isn't that an index rather than a key?
I thought 'key' meant:
- a superkey (any set of columns the values of which must be unique in any given row; in a properly relational model, any relation will have at least one, the trivial superkey, the set of all attributes in the relation)
- a candidate key (a minimal superkey, i.e. one without redundant columns -- if you remove any column from the key, it will cease to be unique)
- a primary key (the candidate key considered to be and designated as the most important)
- a foreign key.
I am not familiar with that usage, I am used to 'key' being used as a logical concept, a constraint, while 'index' is a physical concept, some structure that is there 'just' to make shit go faster.
Anyways, some sources don't even define the word 'key', but use it to mean 'unique key'.
And some other sources (like the one by OP) use it to mean something else (totally not unique), as they recommend that it be 'unique to some degree'
And then some others use the definition I provided above. Is that the one meant by OP? I don't know.
I any case, I guess OP meant something like "indexes should be highly selective'. Which BTW is a performance tuning advice, I am not sure that that belongs in 'SQL Style Guide'.
As far as I'm aware, an index is not a constraint, except for a unique index, which implies a unique constraint.
Having said that, I can't think of an explanation more likely than yours.
An illustration in bad pseudocode:
PRIMARY KEY (account_number, transaction_date)Apologies, I didn't realise. That was crass of me.
But could you explain what you mean by unique in some degree? Surely in the example you give the key is just unique, with no further qualification?
What I meant is that if you don't have a unique column to make, say, a primary key with then you need to make it unique by adding (an)other column(s) to it in the key definition. Each additional column being a degree of difference/separation in my mind as I was writing it.
I hope that explanation makes sense and doesn't confuse the issue further.
I'd like to get your feedback on what you think it could say to make this clearer - I want to convey:
a. it must be unique.
b. you can have multi-column keys (some users - especially those tied to an ORM - don't actually know that you can do this).
> 1. The key should be unique to some degree.
If it's talking about primary keys (which it seems to be), it should be unique, full stop.
If it's not, where does uniqueness come in? Is it actually talking about indices, which, if I understand correctly, should have a wide spread of values (without any of them necessarily being unique) if they're to be useful?