I have two tables, created as below,
sqlite> CREATE TABLE Artists(
...> ArtistId INTEGER PRIMARY KEY,
...> ArtistName TEXT NOT NULL
...> );
sqlite> CREATE TABLE Albums(
...> AlbumId INTEGER PRIMARY KEY,
...> AlbumName TEXT NOT NULL,
...> Year TEXT NOT NULL,
...> ArtistId INTEGER NOT NULL,
...> FOREIGN KEY(ArtistId) REFERENCES Artists(ArtistId)
...> );
.table
returns the following,
sqlite> .table
Albums Artists
foreign key pragma is set to ON
sqlite> PRAGMA foreign_keys;
1
However, When I try to insert data as below results in FOREIGN KEY constraint failed.
sqlite> INSERT INTO Albums VALUES (NULL, 'Killers', '1981', 7);
Error: FOREIGN KEY constraint failed
sqlite>
I am a database learner. Could someone point out what is that I am doing wrong?
Best Answer
You try to insert
Albums.ArtistId
value (7
) which is absent inArtisis.ArtistId
values list now. So insertion fails.You cannot insert album of
<unknown>
, FK claims this is impossible, and server prevents this.Insert artist with
ArtistId=7
first, then insert his albums.