DELETE FROM table with self-referencing FK

[fdcastel]

Given

CREATE TABLE SOME_TABLE (
    ID         INTEGER NOT NULL,
    DATA       VARCHAR(50),
    PARENT_ID  INTEGER
);
ALTER TABLE SOME_TABLE ADD PRIMARY KEY (ID);
ALTER TABLE SOME_TABLE ADD FOREIGN KEY (PARENT_ID) REFERENCES SOME_TABLE (ID);
COMMIT;

INSERT INTO SOME_TABLE (ID, DATA, PARENT_ID) VALUES (1, 'd1', NULL);
INSERT INTO SOME_TABLE (ID, DATA, PARENT_ID) VALUES (2, 'd2', 1);
INSERT INTO SOME_TABLE (ID, DATA, PARENT_ID) VALUES (3, 'd3', 1);
INSERT INTO SOME_TABLE (ID, DATA, PARENT_ID) VALUES (4, 'd4', 3);
INSERT INTO SOME_TABLE (ID, DATA, PARENT_ID) VALUES (5, 'd5', 3);
COMMIT;

How could someone delete all records from this table?

DELETE FROM some_table;

violation of FOREIGN KEY constraint "".
violation of FOREIGN KEY constraint "INTEG_8" on table "SOME_TABLE".
Foreign key references are present for the record.
Problematic key value is ("ID" = 1).

DELETE FROM some_table WHERE parent_id IS NOT NULL;

violation of FOREIGN KEY constraint "".
violation of FOREIGN KEY constraint "INTEG_8" on table "SOME_TABLE".
Foreign key references are present for the record.
Problematic key value is ("ID" = 3).

I'm working on Firebird dialect for SQL Alchemy and I got stuck with this problem.

It appears all "main" databases (PostgreSQL, Oracle, Microsoft SQL Server, MySQL and SQLite) supported by SQL Alchemy test suite pass without problem with a full DELETE FROM some_table.

Questions:

• Is this the expected result from SQL Specification?
• Is there any plans to change this?
• Is there any plans to support TRUNCATE TABLE in future Firebird versions?

[fdcastel]

@mariuz @dyemanov @pcisar Is this space being monitored?

[mrotteveel]

I'd suggest asking on firebird-devel instead.

[mrotteveel]

In any case, the workaround should be to use on delete set null (or on delete cascade).

[fdcastel]

I'd suggest asking on firebird-devel instead.

Thanks @mrotteveel. I just joined the group but it appears I'm not allowed to post any messages there.

On group web interface It seems I don't have any way to start a new conversation.

[fdcastel]

Ok. I'm blind.

It seems a big button written "+ New conversation" on the upper-left side of UI isn't much visible, anyway 😅 .

[mrotteveel]

I also have that problem when going to the Google Groups web UI (every time!), but I generally use the group as a mailing list.

[asfernandes]

It's well know problem, Firebird checks integrity after each row is deleted instead of after the full statement execution.

One workaround is ORDER BY clause that can be used in DELETE and UPDATE.

[fdcastel]

Moved to firebird-devel.

[hvlad]

I've a big doubt that such order by could exists in practice.
Actually it requires to build whole path from root to the every record and order by this path.

Another workaround is to use undo log with the self-FK checks, like it is used for the stable cursor feature.