SQLite - FOREIGN KEY

The SQLite FOREIGN KEY constraint is used to link two tables together. A FOREIGN KEY consist of single or multiple columns in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is known as the child table. The table which is referenced is called the referenced or parent table.

The FOREIGN KEY constraint helps to preserve links between two tables. It prevents invalid data from being inserted in the foreign key column. The value which can be inserted into the foreign key column must be the values contained in the referenced table.

Consider a database containing tables called Employee and Contact_Info with the following records:

Table 1: Employee table

Table 2: Contact_Info table

The EmpID column in the Employee table is the PRIMARY KEY in the Employee table. The EmpID column in the Contact_Info table is a FOREIGN KEY in the Contact_Info table.

SQLite FOREIGN KEY constraint with CREATE TABLE

The below mentioned statement creates the Contact_Info table with FOREIGN KEY constraint applied on column EmpID.

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

To provide a name to FOREIGN KEY constraint, and to define a FOREIGN KEY constraint on multiple columns, the statement is given below:

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  CONSTRAINT FK_Contact_Info FOREIGN KEY (EmpID) 
  REFERENCES Employee(EmpID)
);

The statement given below demonstrates how to apply FOREIGN KEY constraint on multiple columns (EmpID and PersonName). Please note that, the Employee table must have PRIMARY KEY constraint on these columns - EmpID and Name.

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  CONSTRAINT FK_Contact_Info FOREIGN KEY (EmpID, PersonName) 
  REFERENCES Employee(EmpID, Name)
);

SQLite FOREIGN KEY constraint with ALTER TABLE

SQLite does not support ALTER TABLE statement to add a FOREIGN KEY to the existing table. However this can be achieved using following steps:

• The foreign key constraint should be checked off
• Rename the table to some other name
• Create a new table with same structure but FOREIGN KEY constraint added
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to enforce FOREIGN KEY constraint on EmpID column of the existing table Contact_Info, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Contact_Info RENAME TO Contact_Info_old;

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255),
  FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

INSERT INTO Contact_Info SELECT * FROM Contact_Info_old;

COMMIT;

PRAGMA foreign_keys=on;

DROP FOREIGN KEY constraint

SQLite does not support ALTER TABLE or DROP statement to drop an existing FOREIGN KEY constraint from a table. To achieve this, the following steps can be used:

• The foreign key constraint should be checked off
• Rename the table to some other name
• Create a new table with same structure but FOREIGN KEY constraint dropped
• Copy the data from renamed table to new table
• In the end, turn on the foreign key constraints

For example, to drop FOREIGN KEY constraint from EmpID column of the existing table Contact_Info, the following statement can be used:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE Contact_Info RENAME TO Contact_Info_old;

CREATE TABLE Contact_Info (
  Phone_Number VARCHAR(100),
  EmpID INT NOT NULL,
  PersonName VARCHAR(255),
  Address VARCHAR(255)
);

INSERT INTO Contact_Info SELECT * FROM Contact_Info_old;

COMMIT;

PRAGMA foreign_keys=on;