问题:

Google mostly answer the question about PRIMARY KEY/UNIQUE KEY difference. But what about KEY/UNIQUE KEY in MySQL? Does`t KEY in MySQL uniquely identify a tuple?

回答1:

No, KEY is alias for INDEX. INDEX doesn't have to be unique. If the KEY(INDEX) uniquely identified a tuple (a row) then it's a UNIQUE KEY (or a PRIMARY KEY depending on how you defined that key).

回答2:

Not at all - a column with a key can easily have duplicate values, the key helps with accessing them fast. A unique key will not allow to insert a row with an already existing value in that column.

回答3:

In database theory, 'key' is a synonym for a unique constraint. Some database management systems use an index (physical) to implement a unique constraint (logical) and is said to be a 'unique index'.

For the mySQL product, KEY is a synonym for a non-unique index and I think the usage is counter-intuitive.

回答4:

I know it's an old thread but @onedaywhen is correct here. In database theory, the term 'key' refers to candidate or super key and candidate key is a minimal super key. Primary key is chosen to be one of the candidate keys if several, hence 'key' IS unique. I can refer to my DB book used at university if necessary.