Add Auto Increment to ID with Foreign Key in MySQL

auto-incrementforeign keyMySQLmysql-workbenchuniqueidentifier

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!

Best Answer

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"))

Related Solutions

MySQL – Foreign Key Constraint Fails

You need to have already declared the table that the foreign key references, before you can define a foreign key that references it.

Once you declare the second table, you can then declare the first table. Tested here on MySQL 5.5.27.

If you need to bypass the validation, you can do this:

SET FOREIGN_KEY_CHECKS = 0;
-- declare tables
SET FOREIGN_KEY_CHECKS = 1;

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks

Mysql – Adding foreign keys constraint on databases used

OK - following my comment, here is what I would do.

Before shutting down the server or individual database.

Create backup table scripts for each table you need to enforce foreign keys on and their parent(s). Call them my_table_bak (or similar).
Run these CREATE TABLE my_table_bak(1..n) scripts on the database (don't definitively delete anything).
(removed during edit)

Prepare an SQL script file (salvage.sql? :-))

Now, the question is are you removing orphan records (i.e. child records that have no corresponding record in the parent table)? Or are you deleting barren parents (i.e. parents with no child records)? Below will do both.

INSERT INTO parent_bak 
SELECT * FROM parent 
WHERE parent.primary_key NOT IN 
(
    SELECT child.foreign_key 
    FROM child
); -- backup barren parents

INSERT INTO child_bak 
SELECT * FROM child 
WHERE child.foreign_key 
NOT IN 
(
    SELECT parent.primary_key 
    FROM parent
); -- backup orphan records

DELETE FROM parent 
WHERE parent.primary_key 
NOT IN 
(
    SELECT child.foreign_key 
    FROM child
); -- remove invalid records from your original tables.

DELETE FROM child 
WHERE child.foreign_key NOT IN 
(
    SELECT parent.primary_key 
    FROM parent
); -- remove invalid records from your original tables.

Add your FOREIGN KEY constraints.

Stop all clients connecting to the server or database (so updates can't occur while running the copy into _bak tables (invalid data) and the deletes.
Run:
```
mysql -h host -u username -ppassword yourdb < salvage.sql
```
...for each of your servers. For your 20 databases, you could write a script for all 20 and run that.

The time this will take will depend on the size of your databases and the number of tables. Warning - there may be typos in this post - I didn't run the scripts on a server, but you should get the idea.

Test on at least one database before running on your live server.

[EDIT] Forgot - add your FOREIGN KEY constraints at the end of salvage.sql also.

Best Answer

Related Solutions

MySQL – Foreign Key Constraint Fails

Mysql – Adding foreign keys constraint on databases used

Related Question