SQLite Forum

RETURNING clause returns value despite constraint violation
Login

RETURNING clause returns value despite constraint violation

(1) By anonymous on 2021-12-01 17:56:39 [link] [source]

As stated in https://www.sqlite.org/lang_returning.html#processing_order:

"The first prototype of the RETURNING clause returned values as they were generated. That approach used less memory, but it had other problems
...
For these reasons, the current implementation was modified so that all database changes happen before any RETURNING output is emitted."

In following case the foreign key constraint is violated, but SQLite still returns generated ID, which is confusing a behavior.

To reproduce:

sqlite> PRAGMA foreign_keys(1);
sqlite> CREATE TABLE Parent(id INTEGER PRIMARY KEY);
sqlite> CREATE TABLE Child(id INTEGER PRIMARY KEY, parent_id INTEGER NOT NULL REFERENCES Parent(id));
sqlite> INSERT INTO child (parent_id) VALUES (666) RETURNING id;
1.         // <--- generated ID returned
Error: FOREIGN KEY constraint failed // <---- error afterwards

(2) By Richard Hipp (drh) on 2021-12-01 19:18:58 in reply to 1 [source]

Thanks for the bug report. Should now be fixed by check-in a818ba2ed635b91e.