how to change primary key column to auto increment when its a foreign key of different table in mysql

Question

Can not alter table because animal id is foreign key in different table

 ALTER TABLE 'animal' CHANGE 'animal_id' 'animal_id' INT(11) NOT NULL AUTO_INCREMENT;

Please post code as text, not screenshots. http://idownvotedbecau.se/imageofcode — Barmar, Dec 13 '21 at 20:56

score 0 · Answer 1 · answered Dec 15 '21 at 21:31

0

Because I can't see your full code, however, I think you can try this:

ALTER TABLE 'animal' MODIFY 'animal_id INT(11) AUTO_INCREMENT;

answered Dec 15 '21 at 21:31

leonguyen.to

Thank you leo but i tried this way but because of foreign key constraint it's not working – Hardi Patel Dec 15 '21 at 23:36

score 0 · Answer 2 · answered Nov 24 '22 at 18:39

I think you could do something similar to this response: How to update primary key. When you update the PK, I think you could do something like this response: Alter a MySQL column to be AUTO_INCREMENT.

But since this response is not best practice, if you don't need the data in the table, you could drop and then recreate the table.

2 Answers2