SQLite Foreign Key

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.

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.

Example of FOREIGN KEY

Let's insert some entries into our tables.

Insert into Employees Table

INSERT INTO Employees VALUES(01, 'Vipul', 'Dehradun', 01);
INSERT INTO Employees VALUES(02, 'Nakul', 'Amritsar', 02);

Insert into Department Table

INSERT INTO Department VALUES(01,'Content');
INSERT INTO Department VALUES(02,'Finance');

Explanation:

In this data insertion example, we ensure that the emp_deptID values in the Department table must match valid emp_deptID values from the Employees table,for establishing a valid foreign key relationship.

Now, if you try to insert an order with a non-existent emp_deptID , SQLite will reject the operation, enforcing referential integrity.

FOREIGN KEY with JOINS

As we have discussed above, Foreign key is play a vital role in join operations. Because for joins operations we should have a common column in both table, so here Foreign Key came into picture and make our query very easy. Let's look with example from above tables.

SELECT Employees.emp_id, Employees.emp_name, Employees.emp_city,
Employees.emp_deptid, Department.emp_dept_name
FROM 
Employees JOIN Department
ON (Employees.emp_deptid = Department.emp_deptid)

Output:

Explanation:

In the above query we perform the join operation on both table Employees and Department tables. Under the ON block, we have inserted the column(emp_deptid) with the same name for joining the tables i.e FOREIGN KEY.

Conclusion

The use of Foreign Keys in SQLite ensures referential integrity, allowing for valid relationships between tables. This feature is particularly crucial for JOIN operations, simplifying queries by establishing connections between tables through common columns. With the ability to enforce data consistency and facilitate efficient data retrieval, SQLite, along with Foreign Keys, provides a robust solution for managing relational databases.