SQLite Forum

Error not given when referencing something that doesn't exist
Login

Error not given when referencing something that doesn't exist

(1) By anonymous on 2021-07-28 04:54:56 [link] [source]

SQLite version 3.36.0 2021-06-18 18:36:39
------------------------------------------------------------
PRAGMA foreign_keys = ON;

create table Owner(
    name text primary key,
    address text
);
create table Pet(
    pet_name text primary key,
    pet_species text,
    pet_DOB text,
    owner_name,
    foreign key (owner_name) references Owner(owner_name) 
);
------------------------------------------------------------

If I am not missing something...
This should give an error saying that the table Owner does not contain owner_name.

What actually happens:
No error is given.

When inserting values:
------------------------------------------------------------
insert into Pet(pet_name, pet_species, pet_DOB, owner_name)
   ...> values ("Rufus", "dog", "12/10/2017", "Jim");
Error: foreign key mismatch - "Pet" referencing "Owner"
------------------------------------------------------------

When inserting in SQLite version 3.34.0 2020-12-01 16:14:00:
No error is given.

Is this a bug that has been missed or maybe an intended feature that had a bug that was fixed?

(2) By Simon Slavin (slavin) on 2021-07-28 12:32:40 in reply to 1 [source]

The fact that FOREIGN KEY definitions are not checked at CREATE TABLE time may be connected with database dumping and reloading. SQLite allows a definition like this because at the time you CREATE TABLE Pet, TABLE Owner may not yet exist.

Various utilities dump tables in the order they are held in the database file, or in alphabetical order. Checking the parent tables as you define a child table would require a relation-chasing procedure to figure out which order to dump the tables in.

One might argue that, if the parent table does exist, the definition should be checked anyway. But this would introduce a 'sometimes works' behaviour, which might fool programmers into thinking it always works.

In terms of the INSERT command referring to a non-existent parent, if I understand your post correctly, 3.34.0 returned no error, but version 3.36.0 returns an error. This suggests a bug has been fixed.

Hope that helps.

(4) By anonymous on 2021-07-29 21:17:28 in reply to 2 [link] [source]

Even though documentation says otherwise, this would make sense. Thank you.

(3) By David Empson (dempson) on 2021-07-28 22:45:14 in reply to 1 [link] [source]

I tried repeating your example. The insert produced the same "foreign key mismatch" error in the SQLite command shell for version 3.34.0 (exact same date and time, running on Windows in my case), which you claim didn't produce an error. It also produced the same error in an older version I happened to have handy (3.15.2).

Did you perhaps miss the PRAGMA foriegn_keys = ON when running your test with 3.34.0?