MySql：删除所有现有的主键并插入一个新的自增主键。

Question

在一个MySql数据库表中已经有两个主键（复合），该表大约有50000行。现在我需要删除这些主键并添加一个新的自动递增主键id。我需要所有的行都不变，并分配唯一的id。现在执行此操作的查询字符串是什么？我已经尝试了以下代码，但出现错误：“#1064-您的SQL语法有误；请检查与您的MySQL服务器版本相对应的手册以使用'ADD id INT PRIMARY KEY AUTO_INCREMENT'”。

ALTER TABLE `table_name` DROP PRIMARY KEY ADD `id` INT PRIMARY KEY AUTO_INCREMENT;

- Scarecrow

Answer 1

不要简单地删除现有的主键。用“非空唯一”约束条件替换它们，然后添加一个代理键。这是原因。（PostgreSQL，但原则相同。）

create table elements (
  chemical_symbol varchar(2) primary key,
  atomic_number int not null unique,
  element_name varchar (35) not null unique
);

insert into elements values
('H', 1, 'hydrogen'),
('He', 2, 'helium'),
('Li', 3, 'lithium'),
('Be', 4, 'beryllium')
-- many more rows
;

如果您只是删除现有的主键约束并添加一个代理键，就像这样...

-- DON'T DO THIS.
alter table elements drop constraint elements_pkey,
add column element_id serial primary key;

. . . 如果你不唯一地使用"chemical_symbol"，就会失去"chemical_symbol"必须是唯一的业务规则。

insert into elements (chemical_symbol, atomic_number, element_name)
values ('H', 5, 'boron');

select * from elements
order by chemical_symbol;

化学符号  原子序数  元素名称  元素ID
--
Be        4        铍         4
H         1        氢         1
H         5        硼         5
He        2        氦         2
Li        3        锂         3

现在有两个元素的化学符号是'H'。这是一个错误。

相反，用not null和unique约束替换现有的主键约束。然后添加一个代理键。

-- Do this instead.
alter table elements drop constraint elements_pkey,
add constraint elements_chemical_symbol_key unique (chemical_symbol),
alter column chemical_symbol set not null,
add column element_id serial primary key;

现在，如果您尝试插入一个具有重复化学符号的行，则DBMS会引发错误。

insert into elements (chemical_symbol, atomic_number, element_name)
values ('H', 5, 'Boron');

错误：重复的键值违反了唯一约束“elements_chemical_symbol_key” SQL状态：23505 详细信息：键（chemical_symbol）=（H）已经存在。

- Mike Sherrill 'Cat Recall'

Answer 2

找到答案了，我在两个 SQL 语句之间漏了一个逗号。

正确的代码应该是

  ALTER TABLE `table_name` DROP PRIMARY KEY, ADD `id` INT PRIMARY KEY AUTO_INCREMENT;

- Scarecrow