adding autoincrement to id already part of foreign key I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without dropping the foreign key constraint for all of them, adding the autoincrement property and re-creating the foreign key constraints?

Thank you very much!
Try temporary disabling foreign keys (make sure no ones allowed to update the db meanwhile):

create table t1 (id int not null primary key) engine = innodb;
create table t2 (id int not null primary key
                ,t1_id int not null
                ,    constraint abc foreign key (t1_id) 
                                    references t1 (id) 
) engine = innodb;

set foreign_key_checks = 0;
alter table t1 change column id id int auto_increment;
set foreign_key_checks = 1;


Note that set foreign_key_checks = 1; does not validate foreign keys, so if someone manages to add invalid values whilst foreign keys are disables, you end up with an inconsistent db:

insert into t1 (id) values (1);
set foreign_key_checks = 0;
insert into t2 (id, t1_id) values (1,1);
insert into t2 (id, t1_id) values (2,2); -- invalid
set foreign_key_checks = 1; -- does not validate foreign keys


I vaguely remember that this was also the case when adding foreign keys, but this bug seems to have been fixed since:

select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.2.14-MariaDB |
+-----------------+
1 row in set (0.00 sec)

alter table t2 drop foreign key abc;
alter table t2 add constraint abc foreign key (t1_id)                                      references t1 (id);
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails ("test"."#sql-5fa_a", 
CONSTRAINT "abc" FOREIGN KEY ("t1_id")
REFERENCES "t1" ("id"))