SQL Foreign Key Constraint check on Delete
I have table
CREATE TABLE nodes ( name VARCHAR(50) NOT NULL, parent VARCHAR(50), PRIMARY KEY (name), FOREIGN KEY(parent) REFERENCES nodes (name) ON UPDATE cascade );
I put some data
INSERT INTO nodes (name, parent) VALUES ('n1', NULL); INSERT INTO nodes (name, parent) VALUES ('n11', 'n1'); INSERT INTO nodes (name, parent) VALUES ('n12', 'n1'); INSERT INTO nodes (name, parent) VALUES ('n13', 'n1');
Let's delete all
DELETE FROM nodes;
Nope, no way.
SQL Error  [S1000]: [SQLCODE: <-124>:<FOREIGN KEY constraint failed referential check upon DELETE of row in referenced table>] [Location: <ServerLoop>] [%msg: <At least 1 Row exists in table 'SQLUser.nodes' which references key 'NODESPKey2' - Foreign Key Constraint 'NODESFKey3', Field(s) 'parent' failed on referential action of NO ACTION>]
Even, if it knows that I want to delete everything, it still complains about integrity.
And Yes, I know, that I can delete n11, n12, and n13, then n1, and it will work. But this is not what I need.
Is there any way, to keep the check working, but at the same time have a possibility to delete all needed rows, when it is logically supposed to work?
It's part of my work on SQLAlchemy dialect for Python