correct usage of the FOREIGN KEY function

Question

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)

score 1 · Accepted Answer · answered Jun 14 '18 at 14:24

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.

correct usage of the FOREIGN KEY function

1 Answers1