MySQL: Modify column adding auto_increment:

Question

I want to change a column of a table adding auto_increment:

ALTER TABLE t_bed MODIFY COLUMN id BIGINT auto_increment;

This is the error:

Cannot change column 'id': used in a foreign key constraint 't_bed_ibfk_3' of table 't_room',

But the table t_room is empty

Answer 1

Since the t_room table is empty, you can try simply removing the foreign key constraint which is causing the problem:

ALTER TABLE t_room DROP FOREIGN KEY t_bed_ibfk_3;

Then run your alter statement on the t_bed table to make id an auto increment column:

ALTER TABLE t_bed MODIFY COLUMN id BIGINT auto_increment;

Finally, you may add back the constraint in the t_room table if you still need it.