I wonder if someone has stumbled upon strange behavior of sqlite foreign keys.
My problems occurs when I update a key variant (parcours_id) that points to another tables primary key parcours (eid) and is declared a foreign key.
The involved tables are:
CREATE TABLE parcours(
eid character varying(36) PRIMARY KEY,
name character varying(50),
comment text,
location_id character varying(36),
owner_id character varying(36),
change_id integer NOT NULL,
change_flag smallint NOT NULL DEFAULT 1,
CONSTRAINT fk_location_id FOREIGN KEY (location_id)
REFERENCES location (eid)
ON UPDATE CASCADE
ON DELETE RESTRICT
)
CREATE TABLE variant (
eid character varying(36) PRIMARY KEY,
parcours_id character varying(36) NOT NULL,
name character varying(50),
comment text,
created TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
active smallint NOT NULL DEFAULT 1,
selected smallint NOT NULL DEFAULT 0,
tgt_count integer NOT NULL,
owner_id character varying(36)
DEFAULT '687baa6e-556f-43f2-897a-f1dfb371541a',
change_id integer NOT NULL,
change_flag smallint NOT NULL DEFAULT 1,
CONSTRAINT fk_parcours_id FOREIGN KEY (parcours_id)
REFERENCES parcours (eid)
ON UPDATE CASCADE
ON DELETE RESTRICT
)
The foreign key relationship from of variant( parcours_id ) to parcours( eid ) works perfectly on insert but fails on update with
android.database.sqlite.SQLiteException: foreign key mismatch: , while compiling: UPDATE variant SET parcours_id=?,name=?,comment=?,created=?,active=?,selected=?,tgt_count=?,owner_id=?,change_id=?,change_flag=? WHERE eid=?
Now this problem is slightly philosophical because at this point I do not need to update the key variant (parcours_id) because it has not changed. I decided to implement the update funtion only once allways updating all fields except for the primary key. Generally I do not expect the problematic value to ever change so I could just leave it out of the update.
It looks to me like my implemention is ok, because if I disable foreign keys on the database without changing anything else it works, the foreign key is updated correctly and the integrity of the database is intact after the update.
To me this proves that the problem is not caused by my implementation but seems to be a general misunderstanding of how sqlite handles foreigng key updates.
Any suggestions ?
Aucun commentaire:
Enregistrer un commentaire