EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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
SPSS Course Bundle - 14 Courses in 1 | 5 Mock Tests
34+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Course Bundle - 32 Courses in 1 | 4 Mock Tests
125+ Hour of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Course Bundle - 7 Courses in 1
47+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQLite Course Bundle - 4 Courses in 1
 10+ Hours of HD Videos
4 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more