SQL Foreign Key Constraint check on Delete
I have table
CREATETABLE nodes (
nameVARCHAR(50) NOTNULL,
parentVARCHAR(50),
PRIMARY KEY (name),
FOREIGN KEY(parent) REFERENCES nodes (name) ONUPDATEcascade
);
I put some data
INSERTINTO nodes (name, parent) VALUES ('n1', NULL);
INSERTINTO nodes (name, parent) VALUES ('n11', 'n1');
INSERTINTO nodes (name, parent) VALUES ('n12', 'n1');
INSERTINTO nodes (name, parent) VALUES ('n13', 'n1');
Let's delete all
DELETEFROM 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:
CREATETABLE nodes2 (
nameVARCHAR(50) NOTNULL,
parentVARCHAR(50),
PRIMARY KEY (name),
FOREIGN KEY(parent) REFERENCES nodes (name) ONUPDATEcascadeonDELETEcascade
)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 %NOCHECKFROM nodes wherenamein ('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.
defvisit_delete(self, delete_stmt, **kw):ifnot delete_stmt._where_criteria and delete_stmt.table.foreign_keys:
table = delete_stmt.table
nocheck = Falsefor fk in table.foreign_keys:
nocheck = not fk.ondelete and fk.parent.table == table
ifnot nocheck:
breakif nocheck isTrue:
delete_stmt = delete_stmt.prefix_with('%NOCHECK', dialect='iris')
text = super().visit_delete(delete_stmt, **kw)
return text
did you try the DELETE %NOTRIGGER ?