Description:
If you have a table which has a cascade-deleting foreign key through another table, and then back to itself, the count returned when running a delete query will not match the count that was actually deleted.
Even if you run a "delete all" on this table, if any of the elements are deleted from this table that would also have been otherwise deleted through this multi-hop cascade delete, the deletion count will not include that row.
Note that this works fine when the FK points directly at the same table, even if you point a row at another row which itself points at another row, etc. It seems to be specific to the FK jumping out to another table and then back.
How to repeat:
CREATE TABLE Entity
(
Id VARCHAR(1) PRIMARY KEY,
LinkId VARCHAR(1)
);
CREATE TABLE Link(
Id VARCHAR(1) PRIMARY KEY,
EntityId VARCHAR(1) NOT NULL
);
ALTER TABLE Link ADD FOREIGN KEY (EntityId) REFERENCES Entity (Id) ON DELETE CASCADE;
ALTER TABLE Entity ADD FOREIGN KEY (LinkId) REFERENCES Link(Id) ON DELETE CASCADE;
INSERT INTO Entity(Id)VALUES ('A');
INSERT INTO Link(Id, EntityId) VALUES ('X', 'A');
INSERT INTO Entity(Id, LinkId) VALUES ('B', 'X');
# The above 'B' row would be deleted even by just running `DELETE FROM Entity WHERE Id = 'A'`,
# since it has a cascade delete to Link 'X' which in turn has a cascade delete to Entity 'A'
SELECT * FROM Entity; # Returns 2 results
DELETE FROM Entity; # Returns "1 row affected" even though both Entity entries are deleted
Description: If you have a table which has a cascade-deleting foreign key through another table, and then back to itself, the count returned when running a delete query will not match the count that was actually deleted. Even if you run a "delete all" on this table, if any of the elements are deleted from this table that would also have been otherwise deleted through this multi-hop cascade delete, the deletion count will not include that row. Note that this works fine when the FK points directly at the same table, even if you point a row at another row which itself points at another row, etc. It seems to be specific to the FK jumping out to another table and then back. How to repeat: CREATE TABLE Entity ( Id VARCHAR(1) PRIMARY KEY, LinkId VARCHAR(1) ); CREATE TABLE Link( Id VARCHAR(1) PRIMARY KEY, EntityId VARCHAR(1) NOT NULL ); ALTER TABLE Link ADD FOREIGN KEY (EntityId) REFERENCES Entity (Id) ON DELETE CASCADE; ALTER TABLE Entity ADD FOREIGN KEY (LinkId) REFERENCES Link(Id) ON DELETE CASCADE; INSERT INTO Entity(Id)VALUES ('A'); INSERT INTO Link(Id, EntityId) VALUES ('X', 'A'); INSERT INTO Entity(Id, LinkId) VALUES ('B', 'X'); # The above 'B' row would be deleted even by just running `DELETE FROM Entity WHERE Id = 'A'`, # since it has a cascade delete to Link 'X' which in turn has a cascade delete to Entity 'A' SELECT * FROM Entity; # Returns 2 results DELETE FROM Entity; # Returns "1 row affected" even though both Entity entries are deleted