EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQLite Tutorial SQLite foreign key
 

SQLite foreign key

Updated April 1, 2023

SQLite-foreign-key

 

 

Introduction to SQLite foreign key

SQLite provides referential integrity constraints that we call as foreign keys. The SQLite foreign key is a basic that checks the presence of a huge worth present in one table to another table that has an association with the essential table where the foreign key is described. When we work with more than one table, when two tables define some relation between them that means one column is common from both tables. If we have guaranteed that the inserted value id exists in the other table column, we can use foreign key constraint on the common columns. Main thing in SQLite databases is that foreign key constraints are not active by default, so for that purpose we need to run special commands.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax: 

create table specified table_name (colm name 1 data type, colm name 2 data type, ………colm name N data type ) [constraint name] foreign key [foreign key name] (colm name1 or list of columns ) reference parent_table_name (colm 1, colm 2,…….colm );

Explanation:

  • In the above syntax first we use create table statements with specified table names including different attributes. After that we use constraint name to specify the foreign key name, if we skip this constraint the SQLite will use the default generated name.
  • In the next part of syntax foreign key constraint followed by foreign name with column or list of column names and that are separated by using comma within parentheses. Finally we see how we can use foreign keys.

How to Use foreign key in SQLite?

Now let’s see how foreign keys work in SQLite databases as follows.

Basically foreign key constraints compile with SQLite library, so that purpose SQLite uses PRAGMA foreign_keys command to enable or disable the foreign key constraint at the run time.

So when we need to enable foreign key at that time we can use the following command as follows.

Code:

PRAGMA foreign_keys = ON;

If we need to disable foreign key at that time we can use the following command as follows.

Code:

PRAGMA foreign_keys = OFF;

When we use foreign key in table at that time we can control the operation by using different parameter as follows.

  • On delete: This is the default parameter in foreign keys. If any existing related key is deleted at that time all ongoing executions end.
  • On update: This is default parameter in foreign key. In the event that any current key is refreshed from the parent table, the exchange comes up short toward the finish of the inquiry.

Example of SQLite foreign key

Given below is the example mentioned:

Now first create two tables to implement foreign keys as follows.

First create a dist_unit with different attributes by using the following statement as follows.

Code:

create table dist_unit (common_id integer primary key, unit_name text not null);

Now create a distributor table with different attributes by using the following statement.

Code:

create table distributor (dist_id integer primary key, dist_name text not null, common_id integer not null, foreign key (common_id) references dist_units (common_id));
.table

Explanation:

  • In the above example we use a create table statement to create a new table name as distributor with attributes such as dist_id with integer data type and also have a primary key constraint, dist_name with text data type and common_id with integer data type as shown in above statement.
  • Here common_id id foreign key reference from dist_units.
  • We successfully created a booth table as shown in the below screenshot as follows.

Output:

SQLite foreign key 1

Now insert some records into the dist_unit table by using insert into statement as follows.

Code:

Insert into dist_unit (unit_name) values ("Local"), ("National"), ("Internal_National");

Now see inserted records by using select statement as follows.

Code:

select * from dist_unit;

Explanation:

  • End result of above statement as shown in below screenshot as follows.

Output:

SQLite foreign key 2

Now insert records into the distributor table by using insert into statement as follows.

Code:

Insert into distributor (dist_name, common_id) values ("Dell", 2);
select * from distributor;

Explanation:

  • Above records we successfully inserted into the distributor table and saw those records by using select statements.
  • End result of above statement as shown in below screenshot as follows.

Output:

insert records into the distributor table

Now try to insert another record as follows.

Code:

Insert into distributor (dist_name, common_id) values ("LG", 4);

Explanation:

  • When we try to insert the above records at that time it shows an error message because common_id 4 does not exist in the dist_unit table.
  • SQLite foreign key constraint or we can say action as follows.

Mainly there are two constraints as follows.

  • On Update Action
  • On Delete Action

SQLite supports the different actions as follows.

  • Set Null
  • Set Default
  • Restrict
  • No Action
  • Cascade

As per our requirement we can use any action.

Rules and Regulations for foreign key

Basically foreign keys are used as referential integrity constraints in SQLite and it is work between parent table and child table. We can make foreign keys at the time of table creation or we can utilize an adjust order to add foreign keys into the predetermined table.

At the point when we use reference_option in foreign key, the reference_option acknowledges the following five unique qualities as follows.

  • Cascade: When we make some changes that means update or delete records in parent table then corresponding rows from child table automatically update or delete.
  • Set Null: When we delete reference rows from parent table then related rows from child table are null.
  • Restrict: if reference rows is updated from parent table then related rows from child table is restricted.
  • No Action: It works same as limit work as referenced previously.
  • Set Default: In SQLite the set default value worked, in which that foreign key set default value in the table, if default value of foreign key is not available then SQLite shows error message.

Conclusion

From this article we saw the basic syntax of foreign key and we also seen different examples of foreign key for implementation. We also saw the rules of foreign key constraint. From this article we saw how and when we use SQLite foreign key.

Recommended Articles

We hope that this EDUCBA information on “SQLite foreign key” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL DDL Commands
  2. SQL Clustered Index
  3. SQL Super Key
  4. SQL LOCK TABLE

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW