SQLite Foreign Key Constraint

Here we will learn SQLite Foreign Key Constraint with example and how to use SQLite Foreign Key Constraint to maintain the relationship between multiple tables with examples.

SQLite Foreign Key Constraint

In SQLite, FOREIGN KEY Constraint is used to maintain the relationship between multiple tables and it helps us to identify records uniquely  Generally, the Foreign Key column in one table becomes a Primary Key constraint in another table to maintain the relationship between tables.

 

We can create multiple FOREIGN KEY Constraints on columns in the table but all those columns must point to PRIMARY KEY Constraint in other tables. 

 

Generally, in SQLite FOREIGN KEY constraint in one table called a child table which points to the PRIMARY KEY constraint of another table is called a parent or reference table.

Syntax of SQLite Foreign Key Constraint

Following is the syntax of creating SQLite Foreign Key Constraint on the table.

 

CREATE TABLE table1

(col1 INTEGER PRIMARY KEY,

col2 TEXT NOT NULL);

 

CREATE TABLE table2

(col3 INTEGER PRIMARY KEY,

col4 TEXT NOT NULL,

col1 INTEGER NOT NULL,

FOREIGN KEY (col1) REFERENCES table1);

If you observe the above syntax we created table1 with col1 column as PRIMARY KEY and we created table2 with col1 column as FOREIGN KEY and this column reference PRIMARY KEY of table1

 

As we discussed FOREIGN KEY in one table becomes the PRIMARY KEY of another table.

Example of SQLite Foreign Key Constraint

We will see how to use FOREIGN KEY Constraint in SQLite with examples. Following is the example of creating Foreign Key Constraint on a column.

 

CREATE TABLE Publisher

(Pub_id INTEGER PRIMARY KEY,

Pub_name TEXT NOT NULL);

 

CREATE TABLE BOOK

(Book_id INTEGER PRIMARY KEY,

Book_name TEXT NOT NULL,

pub_id INTEGER NOT NULL,

FOREIGN KEY (pub_id) REFERENCES Publisher);

Here in the above example, the pub_id is the PRIMARY KEY in Publisher table, i.e. it will accept only unique values and pub_id in BOOK table is FOREIGN KEY, and its reference to the PRIMARY KEY of Publisher table, that means the values of pub_id column whatever exists in Publisher table, only those values are eligible to come in the pub_id column of BOOK table.

 

Now we will insert data into tables using the following statements and see the use of the FOREIGN KEY constraint.

 

sqlite> INSERT INTO Publisher Values(1,'JBL');

sqlite> INSERT INTO Publisher Values(2,'Cyberwit');

sqlite> INSERT INTO Publisher Values(3,'Indian Experss');

sqlite> INSERT INTO Publisher Values(4,'Economics Times');

 

sqlite> SELECT * FROM Publisher;

 

Pub_id      Pub_name

----------  ----------

1           JBL

2           Cyberwit

3           Indian Experss

4           Economics Times

Here in the above, we inserted some records in the Publisher table. Now we will insert some records in Book table using the following queries.

 

sqlite> INSERT INTO BOOK Values(1,'SQL',1);

sqlite> INSERT INTO BOOK Values(2,'SQL-Guide',2);

sqlite> INSERT INTO BOOK Values(3,'SQLite-Guide',1);

sqlite> INSERT INTO BOOK Values(4,'Half Girlfriend',3);

sqlite> INSERT INTO BOOK Values(5,'One Indian Girl',4);

 

sqlite> SELECT * FROM BOOK;

 

Book_id     Book_name   pub_id

----------  ----------  ----------

1           SQL         1

2           SQL-Guide   2

3           SQLite-Gui  1

4           Half Girlf  3

5           One Indian  4

Here we inserted 5 rows in the Book table. Now we will insert the 6th row in the Book table with pub_id value which does not exist in the Publisher table.

 

sqlite> INSERT INTO BOOK Values(6,'HTML',7);

When we insert the above record we won't get any problem even though "7" does not exist in the Publisher table because FOREIGN KEY support has not been enabled for the database session, so we need to enable Foreign Key support by using the following statement.

 

sqlite> PRAGMA foreign_keys = ON;

Once we execute the above statement Foreign Key support will be enabled for the current database session.

 

Now we will delete a recently inserted record from Book table where Pub_id value as 7 using the following statement.

 

sqlite> DELETE FROM BOOK where pub_id=7;

Now again we will try to insert pub_id value as a 7 and see what will happen using the following statement.

 

sqlite> INSERT INTO BOOK Values(6,'HTML',7);

 

Error: FOREIGN KEY constraint failed

Here at the time of inserting the 6th row in the Book table, we will get “FOREIGN KEY constraint failed error” because the value "7" in the reference column pub_id does not exist in the Publisher table. 

 

The following are the records of the Book table.

 

sqlite> SELECT * FROM BOOK;

 

Book_id    Book_name   pub_id

--------   ---------- ----------

1           SQL         1

2           SQL-Guide   2

3           SQLite-Gui  1

4           Half Girlf  3

5           One Indian  4

Same way if we try to delete the records from Publisher table whose Pub_id value exists in Book table then it will throw “Foreign Key Constraint Failed” error. 

 

This way our Foreign Key Constraint will help us to main the relationship between multiple tables based on our requirements.