FOREIGN KEY constraint not respected during bulk insert using TRANSACTION

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

FOREIGN KEY constraint not respected during bulk insert using TRANSACTION

Morten Joergensen
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?

Morten Due Jørgensen  |  Schneider Electric  |  Automation  |  Sr. Software Development Engineer
Phone: +45 88302000 |  Direct Phone: +45 88302653
Email: [hidden email]  |  Site: www.schneider-electric.com  |  Address: Lautrupvang 1, DK-2750 Ballerup, Denmark

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: FOREIGN KEY constraint not respected during bulk insert using TRANSACTION

Kees Nuyt

On Fri, 14 Dec 2018 15:05:29 +0000, Morten Joergensen
<[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users