On Fri, 14 Dec 2018 15:05:29 +0000, Morten Joergensen
<morten.joergen...@schneider-electric.com> wrote:
> Hi,
>
> I have two tables, the second with a FOREIGN KEY - ON DELETE
> CASCADE to the primary key on the first table. When I do a
> transaction inserting a lot of records into the second table
> at once, I am allowed to insert records that do not fulfill
> the constraint, i.e. having values in the foreign key column
> that are not present in the primary key column of the first
> table. When I insert or edit a single record using SQLCipher's
> DB Browser for SQLite, I am correctly being rejected. I can
> insert a record with NULL (so I need a NOT NULL on the foreign
> key column, actually), but not with a value that do not exist
> in the first table.
>
> I can find nothing in the documentation about foreign key
> constraints not being respected for bulk inserts like this,
> but perhaps there is such a rule anyway? I found an old bug
> report, 29ab7be99f2cfe0b04466dd95b80ace87e9fc1b2, "Hitting NOT
> NULL constraint does not roll back statement transaction",
> that resembles it a bit on the title at least, but it is not
> the same.
>
> Are constraints disabled for bulk inserts? It does execute
> very fast, so... - or have I found a bug? - or am doing
> something wrong?
Did you enable foreign key checking with
PRAGMA foreign_keys=on;
? It is a per-connection setting, not retained in the database.
It is off by default. Indeed bulk inserts are fast without it.
https://sqlite.org/pragma.html#pragma_foreign_keys
https://sqlite.org/pragma.html#pragma_foreign_key_check
--
Regards,
Kees Nuyt
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
