SQLite: foreign key constraint

Creating a table with a foreign key

A foreign key is created as part of the create table statement as follows:

create table T (
   ref_1,
   ref_2,
   val_1,
   val_2,
   foreign key (ref_1, ref_2) references parent
);

If the foreign key consists of one column only, it can alternatively be created like so:

create table T (
   ref_id  foreign key references parent,
   val_1,
   val_2
);

create table parent (
   id   integer primary key,
   name text
);

create table child (
   parent_id integer not null references parent,
   val       text
-- foreign key (parent_id) references parent
);

pragma foreign_keys=on;

insert into parent values (1, 'one');
insert into parent values (2, 'two');

insert into child  values (1, 'foo');
insert into child  values (2, 'bar');
insert into child  values (3, 'baz'); -- Uh, oh.

Github repository about-sqlite, path: /tables/constraints/foreign-key/simple.sql

Enforcing foreign keys

SQLite supports foreign key constraints since 3.6.19 (Oct 14, 2009) - but they're not enforced by default (but SQLite could handle a create table statement with foreign keys even before that).

Even after 3.6.19, SQLite must be compiled without SQLITE_OMIT_FOREIGN_KEY and without SQLITE_OMIT_TRIGGER.

The compile options used to compile can be queried with pragma compile_options.

In order to enforce a foreign key constraint, the following pragma must be used:

pragma foreign_keys = on;

If this command returns nothing (rather than either a 0 or 1), foreign keys are not supported with the current SQLIte executable.

Python example

The following simple Python script enforces foreign key relations by setting pragma foreign_keys = on.

Trying to insert a record into the child table for which there is no parent record causes the script to throw the exception sqlite3.IntegrityError: FOREIGN KEY constraint failed.

import sqlite3

db  = sqlite3.connect(':memory:')
cur = db.cursor()

print('')
print('Compile options:')
for opt in cur.execute('pragma compile_options'):
    print('  ' + opt[0])

print('')

db.execute('create table parent (id     integer primary key      , val text)')
db.execute('create table child  (parent integer references parent, val text)')

#ins_parent = db.cursor() # 'insert into parent values (?, ?)')
#ins_child  = db.cursor() # 'insert into child  values (?, ?)')

cur.executemany(
  'insert into parent values (?, ?)',
  [(1, 'foo'),
   (2, 'bar'),
   (3, 'baz')])

#
#   Uncomment following pragma
#   in order to be able to insert
#
#      (4, 'baz')
#
#   Otherwise, script throws
#      sqlite3.IntegrityError: FOREIGN KEY constraint failed
#
cur.execute('pragma foreign_keys = on')

cur.executemany(
  'insert into child values (?, ?)',
  [(2, 'two'),
   (3, 'bar'),
   (4, 'baz')])

for rec in cur.execute('select * from child'):
    print('  ' + str(rec[0]) + ': ' + rec[1])

Github repository about-sqlite, path: /tables/constraints/foreign-key/python/go.py

SQLite: foreign key constraint

Creating a table with a foreign key

Enforcing foreign keys

Python example

Adding foreign keys to an existing table

See also