Hi List,
I'm seeing some oddness with Foreign Keys and was wondering what was
going on.
A few days ago I did a refactor of my error codes, changing the numbers
to be more logically consistent with groupings. They're in a separate
table table which is referenced from a lookups table.
This was done using "ON UPDATE CASCADE," on the error_code field in
lookups table - this was temporary and has since been removed. I then
changed the error codes in the error table and SQLite automagically
updated the lookups table. Brilliant.
But today I'm seeing oddness.
I start by running:
pragma foreign_key_check;
pragma integrity_check;
Both return fine.
The below sample data replicates it for me.
My error table schema (with a subset of data) is:
CREATE TABLE error_codes (
error_code INTEGER PRIMARY KEY
UNIQUE,
error TEXT
);
INSERT INTO error_codes (error_code, error) VALUES (0, 'No Error');
INSERT INTO error_codes (error_code, error) VALUES (3, 'badness');
INSERT INTO error_codes (error_code, error) VALUES (60, 'more badness');
etc.
Referencing this I have a table called lookups (shortened here):
CREATE TABLE lookups (
lookup_id INTEGER PRIMARY KEY AUTOINCREMENT,
content_hash TEXT COLLATE NOCASE,
error_code INTEGER REFERENCES error_codes (error_code),
exp_content_type TEXT COLLATE NOCASE
);
CREATE INDEX idx__content_hash ON lookups (
content_hash
);
INSERT INTO lookups (lookup_id, content_hash, error_code,
exp_content_type) VALUES (661228, '0027f2c9b80002a6', 0, 'ogc_except');
(there are 4 million rows in the actual data)
So I try and do an update on my data:
UPDATE lookups set error_code=3 and exp_content_type='ogc_except'
WHERE content_hash = '0027f2c9b80002a6';
and it fails with:
"FOREIGN KEY constraint failed"
So here's where it gets weird, the following work:
-- error_code of 0
UPDATE lookups set error_code=0 and exp_content_type='ogc_except' WHERE
content_hash = '0027f2c9b80002a6';
-- setting the exp_content_type to something else
UPDATE lookups set error_code=3 and exp_content_type='SOMETHING_ELSE'
WHERE content_hash = '0027f2c9b80002a6';
-- Removing the exp_content_type field:
UPDATE lookups set error_code=3 and WHERE content_hash = '0027f2c9b80002a6';
For bonus oddness, the update script is actually meant to be running in
Python, which did run it successfully (even though I'm sure FK
constraints remain enabled), and yet when I select the row, it shows an
error_code of 1! Despite the fact the UPDATE SQL being run is very
definitely "set error_code = 60".
Note that error_code 1 USED to be what is now error_code 60!
TL;DR:
* For some reason the insertion value of an unrelated row seems to be
affecting referential integrity.
* For some reason when updating to error_code = 60 via Python (and I've
confirmed the SQL being run does have this) it actually gets updated to
error_code = 1 (what the code used to be).
Any thoughts what's going on here? I think either SQLite has its wires
crossed or maybe I'm missing something (probably simple).
SQLite 3.24.0
Thanks,
Jonathan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
