Foreign key constraint inconsistent

Question

Foreign key constraint inconsistent

We have the following scheme (simplified for readability):

CREATE TABLE `group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `device` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `device_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In our automated tests, create device, referencing an existing one group, and then try to delete group, which fails due to the standard sentence ON DELETE RESTRICT:

Error 1451: Cannot delete or update a parent row: a foreign key constraint fails
(`device`, CONSTRAINT `device_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `group`
(`id`))

However, in approximately 25% of cases, deletion groupsucceeds, despite the foreign key limitation. This leads to data inconsistency, where we have a string devicethat refers to group_idthat does not exist.