[GH-ISSUE #41] Order of Create Tables is wrong when using foreign keys #26

New issue

Open

opened 2026-02-25 21:33:04 +03:00 by kerem · 4 comments

kerem commented

2026-02-25 21:33:04 +03:00

Owner

Originally created by @CodingPeak on GitHub (Mar 3, 2017).
Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/41

Just a quick test resulted in 2 tables that should be created in a different order because the first references the second in a foreign key constraint.

CREATE TABLE permissions (
action_id int(11) NOT NULL,
user_type_id int(11) NOT NULL,
PRIMARY KEY (action_id,user_type_id),
KEY FK_permissions_user_types (user_type_id),
CONSTRAINT FK_permissions_actions FOREIGN KEY (action_id) REFERENCES sec_action (ActionId),
CONSTRAINT FK_permissions_user_types FOREIGN KEY (user_type_id) REFERENCES user_types (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user_types (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Originally created by @CodingPeak on GitHub (Mar 3, 2017). Original GitHub issue: https://github.com/DBDiff/DBDiff/issues/41 Just a quick test resulted in 2 tables that should be created in a different order because the first references the second in a foreign key constraint. > CREATE TABLE `permissions` ( `action_id` int(11) NOT NULL, `user_type_id` int(11) NOT NULL, PRIMARY KEY (`action_id`,`user_type_id`), KEY `FK_permissions_user_types` (`user_type_id`), CONSTRAINT `FK_permissions_actions` FOREIGN KEY (`action_id`) REFERENCES `sec_action` (`ActionId`), CONSTRAINT `FK_permissions_user_types` FOREIGN KEY (`user_type_id`) REFERENCES `user_types` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >CREATE TABLE `user_types` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

kerem added the

enhancement

label

2026-02-25 21:33:04 +03:00

kerem commented

2026-02-25 21:33:04 +03:00

Author

Owner

@ali4533 commented on GitHub (Mar 11, 2017):

@ali4533 commented on GitHub (Mar 11, 2017): +1

kerem commented

2026-02-25 21:33:05 +03:00

Author

Owner

@geremora commented on GitHub (Nov 23, 2017):

We found the same situation. Generated script does not have dependency order.

@geremora commented on GitHub (Nov 23, 2017): We found the same situation. Generated script does not have dependency order.

kerem commented

2026-02-25 21:33:05 +03:00

Author

Owner

@llagerlof commented on GitHub (Nov 24, 2017):

Same issue here.

@llagerlof commented on GitHub (Nov 24, 2017): Same issue here.

kerem commented

2026-02-25 21:33:05 +03:00

Author

Owner

@jasdeepkhalsa commented on GitHub (Nov 24, 2017):

Thanks for pointing this out with an example! I can see why this could be problematic - it's also a complex problem to solve.

Essentially DBDiff needs to know exactly which tables have foreign key constraints in another table and use that information to effectively sort the order of the output SQL migration statements

@jasdeepkhalsa commented on GitHub (Nov 24, 2017): Thanks for pointing this out with an example! I can see why this could be problematic - it's also a complex problem to solve. Essentially DBDiff needs to know exactly which tables have foreign key constraints in another table and use that information to effectively sort the order of the output SQL migration statements

kerem referenced this issue

2026-03-07 20:41:36 +03:00

[GH-ISSUE #26] Order of Fields while Calculating MD5 #153