SQLite Foreign Key Constraint

  Total Views : 14
  Comments : 0
 
  Keywords : SQLite foreign key constraint on table column with example, Use sqlite foreign key constraint to maintain relation between multiple tables with example, SQLite create 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 help us to identify records uniquely  Generally, the Foreign Key column in one table become Primary Key constraint in another table to maintain the relationship between tables.

 

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

 

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

Syntax of SQLite Foreign Key Constraint

Following is the syntax of creating SQLite Foreign Key Constraint on 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 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 become 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 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 accepts 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 following statements and see the use of 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 above we inserted some records in Publisher table. Now we will insert some records in Book table using 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 Book table. Now we will insert 6th row in Book table with pub_id value which is not exist in Publisher table.

 

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

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

 

sqlite> PRAGMA foreign_keys = ON;

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

 

Now we will delete recently inserted record from Book table where Pub_id value as 7 using 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 following statement.

 

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

 

Error: FOREIGN KEY constraint failed

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

 

Following are the records of 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.

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User