Foreign Keys

Date: March 12th 2016
Last updated: March 12th 2016

Foreign keys are used to link tables are required to normalise the database and remove redundancy. Normalisation removes duplication. A foreign key references the items of another table and enforces referential integrity. A foreign key must be an item in an existing table.

The following example includes 2 tables; animals and patients. The animals table contains a unique list of animal types. The patients table contains individual animals that are going to visit the vet.

Create tables

# reference foriegn key
import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# create animal table
cur.execute('CREATE TABLE animals (animalid INTEGER PRIMARY KEY\
             AUTOINCREMENT, animaltype TEXT);')

# create second table and reference animal table
# note that a column is created before it is used in the next line
# using FOREIGN KEY() 
cur.execute('CREATE TABLE patient (patientid INTEGER PRIMARY KEY \
            AUTOINCREMENT, patientname TEXT, patientanimal INTEGER,\
            FOREIGN KEY(patientanimal) REFERENCES \
            animals(animalid));')
# commit
con.commit()

# close
con.close()

Add data to tables

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# data
animals = [('dog', ), ('cat', ), ('rat', ), ('bird', ), ('snake', ), ]
patients = [('rover', 1, ), ('tinkles', 2, ), ('peanut', 1, ), ]

# execute
cur.executemany('INSERT INTO animals (animaltype) VALUES (?);', animals)
cur.executemany('INSERT INTO patient (patientname, patientanimal) VALUES (?, ?);', patients)

# commit and close
con.commit()
con.close()

GUI
Animals table

patient table

Try to violate referential integrity - this fails

# reference foriegn key
import sqlite3

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()

# data
patients = [('pickles', 6, ), ]

# execute
cur.executemany('INSERT INTO patient (patientname, patientanimal) VALUES (?, ?);', patients)

# commit and close
con.commit()
con.close()

The reason this fails is because foreign keys are set off by default. The result here is the addition of pickles into the database that references an animal that doesn't exist yet. Once an animal is added to the animals table it is likely that this patient is going to reference the wrong animal. The trick is to add one line to cursor.

# connect
con = sqlite3.connect('gitbook.db')

# cursor
cur = con.cursor()
cur.execute("PRAGMA foreign_keys=ON") #<=== TURN ON FOREIGN KEYS!!!

# data
# patients = [('snoopy', 6, ), ] # fails
patients = [('snoopy', 1, ), ] # passes

# execute
cur.executemany('INSERT INTO patient (patientname, patientanimal) VALUES (?, ?);', patients)

# commit and close
con.commit()
con.close()