Mysql表删除主键和新增主键

╰+哭是因爲堅強的太久メ 2023-10-04 17:34 31阅读 0赞

先创建一个表：并设置主键uuid

CREATE TABLE JALYYQKZXX
    (
        S_LAST_UPDATETIME timestamp    NOT NULL DEFAULT NOW() COMMENT '最近更新时间戳',
        uuid            varchar(255) NOT NULL COMMENT 'uuid',
        SSYQ            varchar(255)          DEFAULT '' COMMENT '所属园区',
        SSLY            double                DEFAULT '0' COMMENT '所属楼宇',
        PRIMARY KEY (uuid)
    ) ENGINE = InnoDB
      DEFAULT CHARSET = utf8;

此时再增加主键就会报错：Multiple primary key defined

![822e495dfdb9438082665a9befb0d60a.png][]

需要先删除主键：

alter table JALYYQKZXX
        drop primary key;

然后再增加主键：也可以增加复合主键

add primary key (ID, uuid)

alter table JALYYQKZXX
        add column ID int,
        add primary key (ID);

![d91b0b79ae7c42f9ab590021ba2f98b2.png][]

如果是有主键自增的约束，还需要先将这个列的自动增长属性去掉，才可以删除主键。

Incorrect column specifier for column 'uuid' : 说明主键设置了自动增长，类型要为int

删除的时候抱错：

Incorrect table definition; there can be only one auto column and it must be defined as a key

说明需要先讲自动增长属性去掉：

alter table JALYYQKZXX
        modify column uuid int;

再删除：

alter table JALYYQKZXX
        drop primary key;

然后查询一下主键：

table\_schema 是数据库名称

select table_schema, table_name, column_name
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE t
    where t.table_schema = 'local_database';

![68efe92f76fb4cf899e0d37a1f273cfc.png][]

[822e495dfdb9438082665a9befb0d60a.png]: https://img-blog.csdnimg.cn/822e495dfdb9438082665a9befb0d60a.png
[d91b0b79ae7c42f9ab590021ba2f98b2.png]: https://img-blog.csdnimg.cn/d91b0b79ae7c42f9ab590021ba2f98b2.png
[68efe92f76fb4cf899e0d37a1f273cfc.png]: https://img-blog.csdnimg.cn/68efe92f76fb4cf899e0d37a1f273cfc.png