3 ms·

Can anyone explain what this means? > 1. The key should be unique to some degree. If it's talking about primary keys (which it seems to be), it should be uniq

by dghf 10y ago

Can anyone explain what this means? > 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?

Treffynnon 10y ago
Thinking multi-column indexes here.
- dghf 10y ago
  Yes, but it's in the 'Constraints and keys' section. It even says 'Constraints, and their subset, keys'. 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.
  - Treffynnon 10y ago
    Well that's good seeing as though I wrote the guide in the first place. An illustration in bad pseudocode: PRIMARY KEY (account_number, transaction_date)
    - dghf 10y ago
      > Well that's good seeing as though I wrote the guide in the first place. 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?
      - Treffynnon 10y ago
        OK, so I think this might be a language/understanding difference here - perhaps what I have written isn't clear enough - not sure. The statement to some degree, in my opinion, doesn't imply that the key isn't actually unique. It still means unique. 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).
carterehsmith 10y ago
It is just the terminology. 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'.
- dghf 10y ago
  > Technically, a key is any column (or a set of columns) that you use to find records. 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.
  - carterehsmith 10y ago
    I agree, the definition I provided (not mine) sounds more like an 'index' than a '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'.

by dghf 10y ago

Treffynnon 10y ago

Thinking multi-column indexes here.

dghf 10y ago
Yes, but it's in the 'Constraints and keys' section. It even says 'Constraints, and their subset, keys'. 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.
- Treffynnon 10y ago
  Well that's good seeing as though I wrote the guide in the first place. An illustration in bad pseudocode: PRIMARY KEY (account_number, transaction_date)
  - dghf 10y ago
    > Well that's good seeing as though I wrote the guide in the first place. 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?
    - Treffynnon 10y ago
      OK, so I think this might be a language/understanding difference here - perhaps what I have written isn't clear enough - not sure. The statement to some degree, in my opinion, doesn't imply that the key isn't actually unique. It still means unique. 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).

carterehsmith 10y ago

It is just the terminology. 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'.

dghf 10y ago
> Technically, a key is any column (or a set of columns) that you use to find records. 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.
- carterehsmith 10y ago
  I agree, the definition I provided (not mine) sounds more like an 'index' than a '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'.