How to initialize auto-increment ID when Mysql table has foreign key association

When you want to initialize mysql auto-increment ID, you can execute：

alter table testTable drop id;
alter table testTable add id bigint primary key not null auto_increment first;

Tables without foreign keys can be directly operated in this way without affecting the original data. After adding, the ID will automatically increase and cover the original data.

If the table has a foreign key association, and the main table (example_table) and the secondary table (example_table_ext) use the same field as ID (example_table_id).

Then mysql will report an error when deleting the primary key ：

MySQL Cannot drop index needed in a foreign key constraint。

At this time, follow the steps below

Delete the foreign key associated with the secondary table,

Delete the auto-increment ID of the secondary table,

Add the self-incrementing ID of the secondary table,

Delete the auto-increment ID of the main table,

Add the main table auto-increment ID,

Add a secondary table association foreign key.

The steps of deleting and adding the auto-increment ID of the main table are necessary, otherwise an error will be reported when adding the associated foreign key of the secondary table. The following is the code：

ALTER TABLE example_table_ext DROP FOREIGN KEY fk_example_table_ext;

ALTER TABLE example_table_ext DROP example_table_id;

ALTER TABLE example_table_ext ADD example_table_id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE example_table DROP example_table_id;

ALTER TABLE example_table ADD example_table_id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;

ALTER TABLE example_table_ext 
 ADD CONSTRAINT `fk_example_table_ext` FOREIGN KEY (`example_table_id`) REFERENCES `example_table` (`example_table_id`);