SQLite Foreign Keys

SQLite Foreign Key is used to specify that values in one table also appear in another table. It enforces referential integrity within SQLite database. The referenced table is known as parent table while the table with the foreign key is known as child table. The foreign key in the child table will generally reference a primary key in the parent table.

You can define a foreign key only in CREATE TABLE statement.

Create Foreign Key

Syntax:

snippet

CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);

Example:

Create a table "departments" having a foreign key.

snippet

CREATE TABLE departments
( department_id INTEGER PRIMARY KEY AUTOINCREMENT,
department_name VARCHAR
);

snippet

CREATE TABLE employees
(employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR NOT NULL,
first_name VARCHAR,
department_id INTEGER,
CONSTRAINT fk_departments
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);

Here a primary key department_id is created on the departments table. Then a foreign key called fk_departments is created on the employees table that references the departments table based on the department_id field.