DELETE FROM table with self-referencing FK

[F. D.Castel]

Reposted from https://github.com/FirebirdSQL/firebird/discussions/7551

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?

[Dimitry Sibiryakov]

F. D.Castel wrote 04.05.2023 17:38:
> *- Is this the expected result from SQL Specification?

Yes because constraints are immediate by default.

> - Is there any plans to change this?

Yes, there is a ticket for deferrable constraints but no one is eager to
offer implementation: https://github.com/FirebirdSQL/firebird/issues/2859

> - Is there any plans to support TRUNCATE TABLE in future Firebird versions?*

Yes, there is a ticket for that too:
https://github.com/FirebirdSQL/firebird/issues/2892

--
WBR, SD.

[Karol Bieniaszewski]

This is not releated to self referencing, this is for all FK.

 

Change table CONSTRAINT definition. If you have restrict rule on it, what do you expect?

Change it to On Delete Cascade and problem gone.

 

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/9450bbcd-1b40-42c3-b489-79769516f9den%40googlegroups.com.

 

[Adriano dos Santos Fernandes]

On 04/05/2023 12:53, 'Dimitry Sibiryakov' via firebird-devel wrote:
> F. D.Castel wrote 04.05.2023 17:38:
>> *- Is this the expected result from SQL Specification?
>
>   Yes because constraints are immediate by default.
>

No, it's a bug. Immediate constraints should be checked after SQL
statement end, not after each record.

>> - Is there any plans to change this?
>
>   Yes, there is a ticket for deferrable constraints but no one is eager
> to offer implementation:
> https://github.com/FirebirdSQL/firebird/issues/2859
>

Deferred (at commit time) is another thing, not necessarily the solution
for the given problem.


Adriano

[F. D.Castel]

Change table CONSTRAINT definition. If you have restrict rule on it, what do you expect?

The same that PostgreSQL, Oracle, Microsoft SQL Server, MySQL and SQLite does: to check the constraint only after the full statement completion.

[Karol Bieniaszewski]

Firebird does not support deffered CONSTRAINT.

Personally i hate deffered CONSTRAINT, as for me it is a disaster.

You can delete parent and leave childs. All code still see all childs, can sum on, count etc.

If Firebird will support it, i pray to have this as an option, and stiil be able to use current behawior.

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/95d0a75c-7c74-4176-ac95-685ff16bcf40n%40googlegroups.com.

 

[Mark Rotteveel]

On 05-05-2023 09:07, Karol Bieniaszewski wrote:
> Firebird does not support deffered CONSTRAINT.
>
> Personally i hate deffered CONSTRAINT, as for me it is a disaster.

Deferred constraints are something else entirely. Deferred constraints
are constraints which are checked at commit time, which is not what is
at hand here.

Mark
--
Mark Rotteveel

[Karol Bieniaszewski]

Yes, exactly, it is checked not on execute time only later on commit time, for me it is disaster, but this is only my humble opinion.

 

Regards,

Karol Bieniaszewski

--

You received this message because you are subscribed to the Google Groups "firebird-devel" group.

To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/b188d94e-eacd-6976-af0c-20b682c97ae0%40lawinegevaar.nl.

 

[F. D.Castel]

I searched github issues again but I couldn't find anything else. 

Anyone against opening a new issue for this one?

Just to keep everyone on the same page:

- We are NOT talking about DEFERRED constraints (checked at commit-time)

- We are talking about SIMPLE constraints (immediate). Which in Firebird have a different behavior from the other major RDBMS.

    - As Adriano put it well: Immediate constraints should be checked after SQL statement end, not after each record.

[Roman Simakov]

IMO it's worth being an issue.

вт, 11 июл. 2023 г. в 19:49, F. D.Castel <fdca...@gmail.com>:

> To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-devel/d12cd3ec-059c-4be9-8032-5254d1f8c268n%40googlegroups.com.



--
Роман Симаков