Can't see why: FOREIGN KEY constraint failed
(1) By anonymous on 2020-08-24 18:39:58 [link] [source]
Can someone please point out where my [reduced] definition is wrong and
DELETE won't work?
drop table if exists persons; create table persons(id integer primary key, name text); insert into persons values(1,'name'); ------------------------------------ drop table if exists users; create table users( id integer references persons(id) on delete cascade on update cascade, userid text unique); insert into users values (1,'username'); ------------------------------------ drop table if exists log; create table log(userid text references users(userid) on update cascade); insert into log values ('username'); PRAGMA foreign_keys=ON; delete from persons where id = 1; --ERROR: FOREIGN KEY constraint failed
I've read foreign keys and I just can't see which of the requirements I violate. (Using SQLite3 v3.33.0)
(2) By Jim Morris (jimmorris33215) on 2020-08-24 18:55:06 in reply to 1 [link] [source]
Looks like your log table needs an "on delete cascade"
(3) By anonymous on 2020-08-24 18:58:05 in reply to 2 [link] [source]
But I want
ON DELETE NO ACTION (to preserve the log entry) which is the default action if none specified, but same error even if I make it explicit.
(4) By Dan Kennedy (dan) on 2020-08-24 21:35:32 in reply to 3 [link] [source]
NO ACTION doesn't mean "silently leave the row unmodified", it means "take no special action to resolve the FK constraint violation". As a result when the statement finishes the database is still in a non-compliant state and so the changes are rolled back and an error returned.
(5) By anonymous on 2020-08-24 22:05:38 in reply to 4 [link] [source]
I see. Then it's practically no different than
ON DELETE RESTRICT. Thanks.
(6.1) By Keith Medcalf (kmedcalf) on 2020-08-24 22:43:26 edited from 6.0 in reply to 5 [source]
That depends on the circumstance see here for the difference.
It might be advised to read the entire page.
From that section:
The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.
This means that if the constraint is deferred there is a big difference between RESTRICT and NO ACTION.
For immediate constraints there is probably no difference unless one could perhaps write a trigger program of some sort that executes within the context of the statement that resolves the integrity violation before the end of the statement.