![]() ![]() Maybe I have misunderstood your complaint. They produce logically identical errors, differing only in formatting, saying "Error: FOREIGN KEY constraint failed". So I pulled source for 3.35.5 and built it, then ran the above code against that and a current version. However, because your report, and that discussion you linked, are vague as to what is actually happening at the SQLite API level, I decided to see for myself what happens. The docs state the likely cause for various return error codes, but they do not, as a rule, promise that specific error codes are returned for specific errors. I was (and remain) dubious that this is a SQLite "regression", because the form of an error is not generally guaranteed. Insert into tweets (user_id, message) values (2, 'tx') returning id, user_id, message print Destined-to-fail insert with returning: Insert into tweets (user_id, message) values (1,'t1') ![]() print Extraneous (to the complaint) insert: Insert into users (username) values ('u1') User_id integer not null references users(id), I think it can be reduced to this: The following input to the sqlite3 shell produces a different error between versions 3.35.5 and 3.38.0 :Ĭreate table users (id integer not null primary key, username text) Ĭreate table tweets (id integer not null primary key, I puzzled over your post #1 for a while, trying to understand the complaint. Sqlite> insert or ignore into tweets (user_id, message) select value, 'crap' from generate_series where start=1 and stop=3 returning id, user_id, message Sqlite> insert into tweets (user_id, message) select value, 'crap' from generate_series where start=1 and stop=3 returning id, user_id, message More clearly demonstrated: SQLite version 3.36.0 22:56:44 Note that the version numbers are different because my private build includes other enhancements and does not always perfectly track the official fossil repository. Richard may know at what version this fix was made (I do not use RETURNING because the entire concept is based on an inherently flawed design methodology). So it would appear that the bug in 3.35.5 which "returns" results despite the error has been fixed.Ĭlearly no one actually bothered to look at what the underlying SQLite3 library was doing. ![]() Runtime error: FOREIGN KEY constraint failed Sqlite> insert into tweets (user_id, message) values (?, ?) returning id, user_id, message Sqlite> insert into tweets (user_id, message) values (?, ?) Sqlite> insert into users (username) values (?) Sqlite> create table tweets (id integer not null primary key, user_id integer not null references users(id), message text) Sqlite> create table users (id integer not null primary key, username text) Use ".open FILENAME" to reopen on a persistent database. SQLite version 3.36.0 22:56:44Ĭonnected to a transient in-memory database. Here is what happens with those two versions of the SQLite3 library. I tried searching the issues/forum and could not find anything. I cannot see anything in the release history that would indicate it should be expected. I do not know if this is a regression or not, but it seems like it is as I now need to handle different error cases depending on sqlite version. In 3.38 it looks like the error occurs during query compilation (perhaps) and is a generic error And furthermore seems the code is SQLITE_CONSTRAINT. The error code returned by SQLite 3.35 does not occur until one starts stepping through the results. To quote the pysqlite3-binary package maintainer which I use for 3.38.0 on Linux (given discussion here): 'user_id integer not null references users(id), 'Ĭonn.execute('insert into users (username) values (?)', ('u1',))Ĭonn.execute('insert into tweets (user_id, message) values (?, ?)', (1, 't1'))Ĭursor = conn.execute('insert into tweets (user_id, message) values (?, ?) returning id, user_id, message', (2, 'tx'))Įxtra code to cause IntegrityError on 3.35.5: cursor.fetchall() RETURNING will give an OperationalError.Įxample source code that gives the OperationalError error on 3.38.0: import sqlite3Ĭonn.execute('create table users (id integer not null primary key, username text)')Ĭonn.execute('create table tweets (id integer not null primary key, ' ![]() For 3.38.0, the INSERT will give an IntegrityError and the INSERT. On the Python side, an INSERT and INSERT RETURNING will give an IntegrityError for 3.35.5. I am seeing different errors for a foreign key conflict (no record exists) for an INSERT versus an INSERT. I use the Python sqlite3 bindings for sqlite, and on Windows I have 3.35.5 and on Linux I have 3.38.0. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |