mercredi 7 octobre 2015

Can insert records without a matching key in the referencing table

This is how I create my tables:

CREATE TABLE IF NOT EXISTS countries ( id INTEGER PRIMARY KEY, name TEXT UNIQUE)
CREATE TABLE IF NOT EXISTS providences ( id INTEGER PRIMARY KEY, name TEXT UNIQUE, country_id INTEGER, FOREIGN KEY( country_id ) REFERENCES countries( id ) )

The tables are created without any errors.

Now I need to insert a country, it gets assigned ID 1

INSERT INTO countries( name ) VALUES('sweden')

Now I want to insert a couple of providences to my country as well:

INSERT INTO providences( name, country_id ) VALUES('Lappland', 1 )
INSERT INTO providences( name, country_id ) VALUES('Norrbotten', 2 ) <-- Oops! Non-existing country_id!

Both providences gets inserted but I was expecting that only the providence with country_id = 1 should be inserted as there is no country with id 2 in the database.

Am I missing something here?

This SQL that I am using in Corona SDK.

Aucun commentaire:

Enregistrer un commentaire