mysql将唯一键更改为主键(mariadb) [英] mysql changing unique key to primary (mariadb)

问题描述

我有我的桌子用户:

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| userid             | varchar(30)  | YES  |     | NULL    |       |
| lname              | varchar(40)  | YES  |     | NULL    |       |
| fname              | varchar(20)  | YES  |     | NULL    |       |
| system_id          | varchar(12)  | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

，我想将 system_id 和 userid 更改为唯一键.

and I want to change the system_id and the userid to be unique keys.

如果我通过 ALTER TABLE用户更改了它们，则修改了userid varchar(30)NOT NULL UNIQUE KEY; 我得到了:

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| userid             | varchar(30)  | NO   | PRI | NULL    |       |
| lname              | varchar(40)  | YES  |     | NULL    |       |
| fname              | varchar(20)  | YES  |     | NULL    |       |
| system_id          | varchar(12)  | YES  |     | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

然后如果我更改system_id ALTER TABLE用户修改system_id varchar(30)NOT NULL UNIQUE KEY;

and then if I change the system_id ALTER TABLE users MODIFY system_id varchar(30) NOT NULL UNIQUE KEY;

+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| userid             | varchar(30)  | NO   | PRI | NULL    |       |
| lname              | varchar(40)  | YES  |     | NULL    |       |
| fname              | varchar(20)  | YES  |     | NULL    |       |
| system_id          | varchar(12)  | NO   | UNI | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

为什么我从未指定将我的 UNIQUE 键更改为 PRI 的原因?

Why is MySQL changing my UNIQUE key to PRI when I never specified it to?

推荐答案

如果UNIQUE索引不能包含NULL值并且表中没有PRIMARY KEY，则它可能显示为PRI.如果多个列构成一个复合UNIQUE索引；尽管这些列的组合是唯一的，但每个列仍可以容纳多次出现的给定值."

"A UNIQUE index may be displayed as PRI if it cannot contain NULL values and there is no PRIMARY KEY in the table. A UNIQUE index may display as MUL if several columns form a composite UNIQUE index; although the combination of the columns is unique, each column can still hold multiple occurrences of a given value."

https://dev.mysql.com/doc/refman/5.7/en/show-columns.html

找到了我的答案.

这篇关于mysql将唯一键更改为主键(mariadb)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！