SQL Foreign Key Constraint check on Delete

Question

Question

Dmitry Maslennikov · Nov 13, 2022

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

Product version: IRIS 2022.1

$ZV: IRIS for UNIX (Ubuntu Server LTS for x86-64 Containers) 2022.3 (Build 539U) Fri Nov 4 2022 00:27:00 EDT

Discussion (7)2

Log in or sign up to continue

score 0 · Answer 1 · 2022-11-13T15:44:48-05:00

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
)

score 0 · Answer 2 · 2022-11-14T01:25:26-05:00

Dmitry Maslennikov · Nov 14, 2022

Right, but I need it without ON DELETE cascade

0 0

score 0 · Answer 3 · 2022-11-14T02:45:09-05:00

Vitaliy Serdtsev · Nov 14, 2022

DELETE %NOCHECK FROM nodes

0 0

score 0 · Answer 4 · 2022-11-14T04:08:35-05:00

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');

score 0 · Answer 5 · 2022-11-14T05:09:55-05:00

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.

score 0 · Answer 6 · 2022-11-14T05:35:07-05:00

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

score 0 · Answer 7 · 2022-12-21T08:49:21-05:00

Ariel Arias · Dec 21, 2022

did you try the DELETE %NOTRIGGER ?

0 0