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 [124] [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
Comments
Will this do, to have both delete and update:
CREATE TABLE nodes2 (
name VARCHAR(50) NOT NULL,
parent VARCHAR(50),
PRIMARY KEY (name),
FOREIGN KEY(parent) REFERENCES nodes (name) ON UPDATE cascade on DELETE cascade
)Right, but I need it without ON DELETE cascade
DELETE %NOCHECK FROM nodes
Yes, it will work, but it will just ignore the checks and will be an issue if I have where
If I add %NOCHECK, it will delete these items, while it should fail
DELETE %NOCHECK FROM nodes where name in ('n11', 'n12', 'n13');And looks like, I have no way to check if I could add %NOCHECK for a particular DELETE query. It may cause integrity issues, in case if it uses another table, not the same one.
Implemented it this way, as a workaround. But not sure in it.
def visit_delete(self, delete_stmt, **kw):
if not delete_stmt._where_criteria and delete_stmt.table.foreign_keys:
table = delete_stmt.table
nocheck = False
for fk in table.foreign_keys:
nocheck = not fk.ondelete and fk.parent.table == table
if not nocheck:
break
if nocheck is True:
delete_stmt = delete_stmt.prefix_with('%NOCHECK', dialect='iris')
text = super().visit_delete(delete_stmt, **kw)
return text
did you try the DELETE %NOTRIGGER ?