删除 MySQL 中的主键 [英] Remove Primary Key in 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;

这篇关于删除 MySQL 中的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！