删除 MySQL 中的主键
我有以下表架构，它将 user_customers 映射到实时 MySQL 数据库的权限:
I have the following table schema which maps user_customers to permissions on a live MySQL database:
mysql> describe user_customer_permission;
+------------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_customer_id | int(11) | NO | PRI | NULL | |
| permission_id | int(11) | NO | PRI | NULL | |
+------------------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
我想删除 user_customer_id 和 permission_id 的主键并保留 id 的主键.
I would like to remove the primary keys for user_customer_id and permission_id and retain the primary key for id.
当我运行命令时:
alter table user_customer_permission drop primary key;
我收到以下错误:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
如何删除列的主键?
推荐答案
如果没有索引，维护自增列会变得太昂贵，这就是为什么 MySQL 要求自增列作为最左边的部分索引.
Without an index, maintaining an autoincrement column becomes too expensive, that's why MySQL requires an autoincrement column to be a leftmost part of an index.
您应该在删除键之前删除自动增量属性:
You should remove the autoincrement property before dropping the key:
ALTER TABLE user_customer_permission MODIFY id INT NOT NULL;
ALTER TABLE user_customer_permission DROP PRIMARY KEY;
请注意，您有一个复合 PRIMARY KEY，它涵盖了所有三列，并且 id 不能保证是唯一的.
Note that you have a composite PRIMARY KEY which covers all three columns and id is not guaranteed to be unique.
如果它恰好是唯一的，您可以再次将其设为 PRIMARY KEY 和 AUTO_INCREMENT:
If it happens to be unique, you can make it to be a PRIMARY KEY and AUTO_INCREMENT again:
ALTER TABLE user_customer_permission MODIFY id INT NOT NULL PRIMARY KEY AUTO_INCREMENT;
相关文章