SQLite Forum

Can't see why: FOREIGN KEY constraint failed
Login

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)

Thank you.

(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.

https://sqlite.org/foreignkeys.html#fk_actions

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.