SQLite FOREIGN KEY

A SQLite FOREIGN KEY is a constraint used to link two tables. A foreign key is a primary key from another table. A foreign key uses to enforce the relationships between two or more tables in SQLite database.

In the SQLite database, foreign key constraints are disabled by default, so foreign key constraints must be enabled for eachdatabase connection.

Enable foreign key constraints.

PRAGMA foreign_keys = ON;

Disable foreign key constraints.

PRAGMA foreign_keys = OFF;

Syntax

Here is the syntax of SQLite FOREIGN KEY constraint:

First, the parent table is created.

CREATE TABLE parent_table(
	column_name1 datatype PRIMARY KEY AUTOINCREMENT,
	column_name2 datatype NOT NULL,
	column_name3 datatype,
	...
);

Second, the child table is created, it will contain the foreign key to the parent table.

CREATE TABLE child_name  
(  
column_name1 datatype [ NULL | NOT NULL ],  
column_name2 datatype [ NULL | NOT NULL ],  
column_name3 datatype [ NULL | NOT NULL ],  
...  
CONSTRAINT fk_name  
FOREIGN KEY (column_name1, column_name2, ...)  
REFERENCES parent_table (column_name1, column_name2, ...)  
ON UPDATE action 
ON DELETE action
);   

ON DELETE and ON UPDATE clauses can be added to a foreign key constraint.

The ON DELETE clause is used when deleting one or more rows from the parent table.

The ON UPDATE clause is used when modifying the values of the parent key for one or more rows.

For ON DELETE and ON UPDATE clauses, SQLite supports the following actions: NO ACTION, RESTRICT, SET NULL, SET DEFAULT or CASCADE. The default action or if no action is specified, then it is NO ACTION.

NO ACTION – no special action is taken.

RESTRICTION – this action means that you cannot delete rows or modify the values of a parent key when there are one or more child keys associated with it.

SET NULL – when a parent key is deleted or the values of a parent key are changed, then the child key columns in all rows in the child table mapped to the parent key are set to NULL values.

SET DEFAULT – it is similar to SET NULL, instead of NULL values, the child key columns will be set to the columns DEFAULT value.

CASCADE – extends the delete or update operation of the parent key to each dependent child key.

Example

In the following example, two tables customers and customer_offers will be created, the cusotmers_offers table will contain the foreign key constraint fk_customer_id to enforce the relationships between rows of tables.

CREATE TABLE customers
(
id INTEGER PRIMARY KEY, 
name VARCHAR(200), 
city VARCHAR(250), 
has_orders INTEGER
);

INSERT INTO customers (id, name, city, has_orders) VALUES (1, 'Tom', 'New York', NULL);
INSERT INTO customers (id, name, city, has_orders) VALUES (2, 'Olivia', 'New York', NULL);
INSERT INTO customers (id, name, city, has_orders) VALUES (3, 'Emma', 'Los Angeles', NULL);
INSERT INTO customers (id, name, city, has_orders) VALUES (4, 'James', 'Chicago', NULL);
INSERT INTO customers (id, name, city, has_orders) VALUES (5, 'Sophia', 'San Diego', NULL);
INSERT INTO customers (id, name, city, has_orders) VALUES (6, 'Evelyn', 'Los Angeles', NULL);
INSERT INTO customers (id, name, city, has_orders) VALUES (7, 'Charlotte', 'San Antonio', NULL);
INSERT INTO customers (id, name, city, has_orders) VALUES (8, 'Elijah', 'San Antonio', NULL);	

CREATE TABLE customer_offers
(
offer_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
offer_date DATE,
amount INT,
CONSTRAINT fk_customer_id 
FOREIGN KEY (customer_id) 
REFERENCES customers (id) 
);

INSERT INTO customer_offers (offer_id, customer_id, offer_date, amount)
VALUES (1, 1, date(), 100);
INSERT INTO customer_offers (offer_id, customer_id, offer_date, amount)
VALUES (2, 1, date(), 150);
INSERT INTO customer_offers (offer_id, customer_id, offer_date, amount)
VALUES (3, 2, date(), 120);
INSERT INTO customer_offers (offer_id, customer_id, offer_date, amount)
VALUES (4, 5, date(), 180);
INSERT INTO customer_offers (offer_id, customer_id, offer_date, amount)
VALUES (5, 8, date(), 140);

SELECT * FROM customer_offers;

Table of customer_offers

offer_id	customer_id	offer_date	amount
1	1	2022-12-28	100
2	1	2022-12-28	150
3	2	2022-12-28	120
4	5	2022-12-28	180
5	8	2022-12-28	140

Let’s try to insert a new row in the customer_offers table, with a customer_id that does not exist in the customers table.

INSERT INTO customer_offers (offer_id, customer_id, offer_date, amount)
VALUES (6, 10, date(), 100);

The SQLite database will return the following error:

FOREIGN KEY constraint failed