In my database I have two tables named artists and tracks. In the creation of the tracks table I set the FOREIGN KEY to artist. But when I run this shown code it is no problem to insert a track row with an non-existing artist.
That's definitely not the aim...
On http://homepages.ecs.vuw.ac.nz/~adam/scie201/lec_R_SQLite.html I found that I have to turn on this function with something like PRAGMA foreign_keys=ON
- but I have no clue how I should code this...
And this is my question: How do I implement the FOREIGN KEY function corretly?
Many thanks in advance for your help!
Now my code:
# needed packages for this script
# install.packages("sqldf") # install this package if necessary
library(sqldf)
# connection to the database TestDB.sqlite
db=dbConnect(SQLite(), dbname="TestDB.sqlite")
# create the first table of the database
dbSendQuery(conn = db,
"CREATE TABLE IF NOT EXISTS artists
(ID INTEGER,
name TEXT,
PRIMARY KEY (ID))")
# create the second table
dbSendQuery(conn = db,
"CREATE TABLE IF NOT EXISTS tracks
(track_ID INTEGER,
title TEXT,
artist INTEGER,
FOREIGN KEY(artist) REFERENCES artists(ID),
PRIMARY KEY (track_ID))")
# filling the artist table with two rows
dbSendQuery(conn = db,
paste0("INSERT INTO artists
VALUES (1,'Tom Chapin')"))
dbSendQuery(conn = db,
paste0("INSERT INTO artists
VALUES (2,'Harry Chapin')"))
# filling the tracks table
dbSendQuery(conn = db,
paste0("INSERT INTO tracks
VALUES (1,'Cats in the Cradle',1)"))
# with the following tracks filling order there must occur an error
### but how to switch on the 'FOREIGN KEY'
dbSendQuery(conn = db,
paste0("INSERT INTO tracks
VALUES (2,'Cats in the Cradle',3)"))
# list the tables of the database
print(dbListTables(db))
# list the columns of a specific table
print(dbListFields(db,"artists")) # of artists
print(dbListFields(db,"tracks")) # of tracks
# show the data ...
print(dbReadTable(db,"artists")) # of artists
print(dbReadTable(db,"tracks")) # of tracks
# close the connection
dbDisconnect(db)
You need to send the PRAGMA statement to the database after opening the connection:
dbExecute(conn = db, "PRAGMA foreign_keys=ON")
Note that the documentation states:
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.
So you have to this every time you connect to the database.