SQLite: Foreign Keys

This SQLite tutorial explains how to use Foreign Keys in SQLite with syntax and examples.

What is a Foreign Key in SQLite?

A foreign key is a way to enforce referential integrity within your SQLite database. A foreign key means that values in one table must also appear in another table.

The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

A foreign key can only be defined in a CREATE TABLE statement.

TIP: You can not add a foreign key to a table using ALTER TABLE because SQLite does not support ADD CONSTRAINT in the ALTER TABLE statement. However, we will show you a workaround later in this tutorial that will allow you to add a foreign key to an existing table.

How to Create a Foreign Key using the CREATE TABLE Statement

Syntax

The syntax to create a foreign key using a CREATE TABLE statement in SQLite is:

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

Let's look at an example of how to create a foreign key using the CREATE TABLE statement in SQLite.

For example:

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

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)
);

In this example, we've created a primary key on the departments table that consists of only one field - the department_id field. Then we've created a foreign key called fk_departments on the employees table that references the departments table based on the department_id field.

How to Add a Foreign Key to an Existing Table

You can not use the ALTER TABLE statement to add a foreign key in SQLite. Instead you will need to rename the table, create a new table with the foreign key, and then copy the data into the new table.

Syntax

The syntax to add a foreign key to an existing table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1
(
  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)
);

INSERT INTO table1 SELECT * FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Example

First, let's start by creating our 2 tables (departments and employees):

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

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  department_id INTEGER
);

Next, let's add some data to these tables:

INSERT INTO departments VALUES (30, 'HR');
INSERT INTO departments VALUES (999, 'Sales');

INSERT INTO employees VALUES (10000, 'Smith', 'John', 30);
INSERT INTO employees VALUES (10001, 'Anderson', 'Dave', 999);

Now, let's add a foreign key to the employees table:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

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)
);

INSERT INTO employees SELECT * FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

This example will rename our existing employees table to _employees_old. Then it will create the new employees table with a foreign key called fk_departments that references the departments table based on the department_id field. Then it will insert all of the data from the _employees_old table into the employees table.

This workaround allows you to add a foreign key to the employees table without losing the data in the table.

 

SQLite: Primary Keys

This SQLite tutorial explains how to create, add, and drop a primary key in SQLite with syntax and examples.

What is a primary key in SQLite?

In SQLite, a primary key is a single field or combination of fields that uniquely defines a record. A table can have only one primary key.

TIP: While the SQL-89 and SQL-92 standards do not allow a NULL value in a primary key, SQLite does allow a NULL under certain circumstances. We strongly recommend against using a NULL value in a primary key.

Create Primary Key (CREATE TABLE statement)

A primary key can be created when you execute a CREATE TABLE statement in SQLite.

Syntax

The syntax to create a primary key using the CREATE TABLE statement in SQLite is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)
);

OR

CREATE TABLE table_name
(
  column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
  column2 datatype [ NULL | NOT NULL ],
  ...
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the primary key.

pk_col1, pk_col2, ... pk_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to create a primary key using the CREATE TABLE statement in SQLite. We will start with a very simple one where our primary key consists of just one column.

For example:

CREATE TABLE employees
( employee_id INTEGER,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

In this example, we've created a primary key on the employees table called employees_pk. It consists of only one column - the employee_id column.

We could have used the alternate syntax and created this same primary key as follows:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

Both of these syntaxes are valid when creating a primary key with only one field.

If you create a primary key that is made up of 2 or more columns, you are limited to using only the first syntax where the primary key is defined at the end of the CREATE TABLE statement.

For example:

CREATE TABLE customers
( last_name VARCHAR NOT NULL,
  first_name VARCHAR NOT NULL,
  address VARCHAR,
  CONSTRAINT customers_pk PRIMARY KEY (last_name, first_name)
);

This example creates a primary key on the customers table called customers_pk that is made up of a combination of the last_name and first_name columns. So each combination of last_name and first_name must be unique in the customers table.

Add Primary Key

If your table already exists and you wish to add a primary key later, you can not use the ALTER TABLE statement to create a primary key. Instead, you must create a new table with the primary key and copy the data into this new table.

Syntax

The syntax to add a primary key to a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)
);

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;

table_name

The name of the table to modify. This is the table that you wish to add a primary key to.

old_table

The name of the original table that will be left behind after you have created the new table with the primary key added.

constraint_name

The name of the primary key.

pk_col1, pk_col2, ... pk_col_n

The columns that make up the primary key.

Example

Let's look at an example of how to add a primary key to an existing table in SQLite. So say, we already have an employees table with the following definition:

CREATE TABLE employees
( employee_id INTEGER,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

And we wanted to add a primary key to the employees table that consists of the employee_id. We could run the following commands:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO old_employees;

CREATE TABLE employees
(
  employee_id INTEGER,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE,
  CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

INSERT INTO employees SELECT * FROM old_employees;

COMMIT;

PRAGMA foreign_keys=on;

In this example, we've created a primary key on the employees table called employees_pk which consists of the employee_id column. The original table will still exist in the database called old_employees. You can drop the old_employees table once you have verified that your employees table and data are as expected.

DROP TABLE old_employees;

Drop Primary Key

In SQLite, you can not use the ALTER TABLE statement to drop a primary key. Instead, you must create a new table with the primary key removed and copy the data into this new table.

Syntax

The syntax to drop a primary key from a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;

table_name

The name of the table to modify. This is the table that you wish to remove the primary key from.

old_table

The name of the original table that will be left behind after you have created the new table with the primary key removed.

Example

Let's look at an example of how to remove a primary key from an existing table in SQLite. So say, we already have a suppliers table with the following definition:

CREATE TABLE suppliers
( supplier_id INTEGER,
  supplier_name VARCHAR NOT NULL,
  address VARCHAR,
  city VARCHAR,
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

And we wanted to drop a primary key from the suppliers table. We could run the following commands:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE suppliers RENAME TO old_suppliers;

CREATE TABLE suppliers
(
  supplier_id INTEGER,
  supplier_name VARCHAR NOT NULL,
  address VARCHAR,
  city VARCHAR
);

INSERT INTO suppliers SELECT * FROM old_suppliers;

COMMIT;

PRAGMA foreign_keys=on;

In this example, we've dropped the primary key on the existing suppliers table. The original table will still exist in the database called old_suppliers. You can drop the old_suppliers table once you have verified that your suppliers table and data are as expected.

DROP TABLE old_suppliers;

 

SQLite: Functions - Listed Alphabetically

For easy reference, we have provided a list of all SQLite functions. The list of SQLite functions is sorted alphabetically based on the function name.

These functions can be used in SQL statements or queries in SQLite.

Below is the list of SQLite functions, sorted by alphabetically by function name.

absavgcoalesceconcatenate with ||countdatedatetimeifnullinstrjulianday

last_insert_rowidlengthlowerltrimmaxminnownullifrandomreplace

roundrtrimsqlite_versionstrftimesubstrsumtimetrimupper