MySql: drop all existing primary keys and insert a new auto-increment primary key

Question

The MySql database table already has two primary (compound) ones, and the table has about 50,000 rows.

Now I need to remove these primary keys and add a new auto increment primary key id.

I need my whole line not to change and be assigned a unique identifier.

Now which query string will be executed.

I tried the following code but got an error

# 1064 - You have an error in the SQL syntax; check the manual that matches your MySQL server version for the correct syntax to use next to "ADD idINT PRIMARY KEY AUTO_INCREMENT" on line 1

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

Answer 1

Got an answer, I missed a comma between two sql statements.

The correct code will be

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

Answer 2

Don't just drop existing primary keys. Replace them with restrictions not null unique, and then add the surrogate key. That's why. (PostgreSQL, but the principles are the same.)

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
;

If you just cancel the existing primary key restriction and add a surrogate key, like this.,.

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

., you lose the business rule that the chemical_character must be unique.

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

select * from elements
order by chemical_symbol;

chemical_symbol atomic_number element_name element_id
-
Be 4 beryllium 4
H 1 hydrogen 1
H 5 boron 5
He 2 helium 2
Li               3              lithium       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');

ERROR: duplicate key value violates unique constraint "elements_chemical_symbol_key"
SQL state: 23505
Detail: Key (chemical_symbol)=(H) already exists.