SQL foreign key mutex

Question

I do not know know how to exactly name this question because I'm still learning SQL. I use sqlite3 for simple tests and my problem is the following:

I have two tables with a primary key. The first table auto increments the key on insert and the second references this key. Let's say I inserted into the first table and after that I insert into the second table with the previous inserted key. Now I have a foreign key constraint. That works but let's say before I start to insert into the second table another thread deletes the key from the first table and inserts another key. This would have the same primary key integer value (I've read this in the sqlite documentation). Now the data could be inconsistent.

What is the solution for this case? Does wrapping the two inserts in a transaction help here?

Answer 1

If you've created a foreign key, then the insert would fail with an error if the key didn't exist in the first table. That's the point of creating FK relationships: it ensures that the data remains consistent.

Similarly, if you insert rows into both tables and then try to delete the row from the 'parent' table, that will throw an error unless you've set up cascading deletes, which will delete the related rows in the second, 'child', table. Either way, consistency is preserved.

Answer 2

Wrapping the two inserts in a transaction will help.

Isolating two threads (transactions) from each other is one of the things that transactions are designed for.