Foreign Key in SQLite

• A Foreign Key is a column or set of columns in the database that refer to the primary key of another table.
• A Foreign Key is used to ensure referential integrity in the database. Referential Integrity ensures the relationship between the tables is valid.
• It means the value in the cell of one table must be the same as the value present in the cell of another table. The foreign key is the key through which we can interact with other tables. Through foreign keys, data retrieval is very fast.
• Foreign keys play a vital role in JOINS operations because in join operations there is the same column in two tables which we achieved through the FOREIGN KEY.

Syntax:

Let’s understand the syntax with the help of an example. We will create two tables Employees and Department Table.

Employees TABLE

CREATE TABLE Employees
(
emp_ID varchar(255),
emp_name varchar(255),
emp_city varchar(255),
emp_deptID varchar(255) PRIMARY KEY
)

Department TABLE

CREATE TABLE Department
(
emp_deptID varchar(255) ,
emp_dept_name varchar(255)
FOREIGN KEY (emp_deptID) REFERENCES Employees(emp_deptID) 
)

If you clearly saw, then there is a column called ‘emp_deptID‘ which is common in both tables. This column acts as a PRIMARY KEY for the Employees table and acts as a FOREIGN KEY for the Department table.

The column acts as a PRIMARY KEY for those Tables, then the table is called Parent Table and the the same column acts as a FOREIGN KEY for the another table then that table is called Child Table.

FOREIGN KEY (emp_deptID) REFERENCES Employees(emp_deptID)

This above line specifies that the emp_deptID in the Department acts as a FOREIGN KEY and we took the referance of emp_deptID from the Employees table.

SQLite is a serverless architecture, which does not require any server or administrator to run or process queries. This database system is used to develop embedded software due to its lightweight, and low size. It is used in Desktop applications, mobile applications televisions, and so on.