mysql - 需要将MUL key 更改为PRI key

Question

最初主键是

(caseId,osName)

   +--------------------+---------------+------+-----+-------------------+-------+
    | Field              | Type          | Null | Key | Default           | Extra |
    +--------------------+---------------+------+-----+-------------------+-------+
    | createdBy          | varchar(200)  | NO   |     | NULL              |       |
    | createdOn          | timestamp     | NO   |     | CURRENT_TIMESTAMP |       |
    | osName             | varchar(200)  | NO   |     | NULL              |       |
    | caseId             | varchar(100)  | NO   | MUL | NULL              |       |
    | issueType          | varchar(100)  | NO   |     | NULL              |       |
    | observationState   | varchar(60)   | YES  |     | NULL              |       |
    | problemSolving     | int(11)       | NO   |     | NULL              |       |
    | structure          | int(11)       | NO   |     | NULL              |       |
    | logicalTransition  | int(11)       | NO   |     | NULL              |       |
    | ownership          | int(11)       | NO   |     | NULL              |       |
    | expectationSetting | int(11)       | NO   |     | NULL              |       |
    | empathy            | int(11)       | NO   |     | NULL              |       |
    | valueAdd           | int(11)       | NO   |     | NULL              |       |
    | comments           | varchar(2000) | NO   |     | NULL              |       |
    | version            | int(11)       | YES  |     | 0                 |       |
    | showcase_escalate  | varchar(30)   | YES  |     | NULL              |       |
    | ringback_state     | varchar(200)  | YES  |     | NULL              |       |
    | trt_state          | varchar(200)  | YES  |     | NULL              |       |
    | srp_state          | varchar(200)  | YES  |     | NULL              |       |
    | id                 | int(11)       | NO   | PRI | NULL              |       |
    +--------------------+---------------+------+-----+-------------------+-------+

在上面的架构中，id是AUTO INCREMENT 。我删除了它，这样我就可以制作 caseId as PRIMARY KEY 。但由于有多个记录具有相同的 caseId，我无法进行更改。

最后我只想把 caseId 作为我的主键。

最初主键是 (caseId,osName)。

Answer 1

如 MySQL Glossary 中所定义:

primary key

A set of columns -- and by implication, the index based on this set of columns -- that can uniquely identify every row in a table. As such, it must be a unique index that does not contain any NULL values.

InnoDB requires that every table has such an index (also called the clustered index or cluster index), and organizes the table storage based on the column values of the primary key.

When choosing primary key values, consider using arbitrary values (a synthetic key) rather than relying on values derived from some other source (a natural key).

See Also clustered index, index, natural key, synthetic key.

由于您有多条记录的 caseId 值相同，因此仅 caseId 无法“唯一地标识表中的每一行”；因此 caseId 单独不能成为PRIMARY KEY。