I have one of those, "it happens sometimes" problems, and I don't know where to begin even.
I have the following database structure:
CREATE TABLE `group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8;
CREATE TABLE `person` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`group_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_group` (`group_id`),
CONSTRAINT `user_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
So, I have a group table, and a person table. The person can only belong to one group, so it has a foreign key called group_id;
If I try to delete a group which has a person referencing to it, I usually get a foreign key error, could not delete group. BUT, sometimes, mysql happily removes the group, leaving me with a person referencing a non-existent group.
Now, before actually trying to delete a group, I'm running a select on both tables to see their content; maybe that helps.
I have something like this:
### Groups ####
id: 1, name: My group!
id: 2, name: Hello!
id: 3, name: Wat!
id: 22, name: My Group!
id: 23, name: My Other Group!
id: 24, name: Completely Different Group!
### People ####
id: 26, group_id: 1
id: 28, group_id: 3
id: 25, group_id: 23
Then the DELETE comes in... and I end up with the following:
### Groups ####
id: 1, name: My group!
id: 2, name: Hello!
id: 3, name: Wat!
id: 22, name: My Group!
id: 24, name: Completely Different Group!
### People ####
id: 26, group_id: 1
id: 28, group_id: 3
id: 25, group_id: 23
The Group with the id: 23 was deleted even though the person:25 is still referencing it.
MySQL logs look like this in both cases (when a foreign key constraint error is thrown, or not):
....
114 Query SELECT id, name FROM group
113 Query SELECT id, group_id FROM person
114 Prepare DELETE FROM group WHERE id = ?
114 Execute DELETE FROM group WHERE id = 23
114 Close stmt
As I said already multiple times, this only happens ~20% of the case. I'm using the go-sql-driver/mysql to access the database.
Sample code that does all this:
getAllGroups := "SELECT id, name FROM group"
getAllUsers := "SELECT id, group_id FROM person"
rows, err := DB.Query(getAllGroups)
checkErr(err)
defer rows.Close()
// Print all the groups
rows, err = DB.Query(getAllUsers)
checkErr(err)
defer rows.Close()
// Print all the users
// Each group is referenced by a user
query := "DELETE FROM group WHERE id = ?"
_, err = DB.Exec(query, groupID)
// Detect if the deletion failed
if err != nil {
if strings.Contains(err.Error(), "a foreign key constraint fails") {
// FOREIGN KEY CONSTRAINT FAILED
}
// SOMETHING ELSE FAILED
}
// EVERYTHING WORKED... even though it shouldn't have