EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQLite Tutorial SQLite foreign key
Secondary Sidebar
Examples of Data Visualizations

MySQL AB

MDF File in SQL Server

Salesforce Admin Interview Questions

SQL Table Variables

Oracle ASM

SQLite foreign key

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.

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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;

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,171 ratings)

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

This is a guide to SQLite foreign key. Here we discuss the introduction, how to use foreign key in SQLite? example, rules and regulations. You may also have a look at the following articles to learn more –

  1. SQL DDL Commands
  2. SQL Clustered Index
  3. SQL Super Key
  4. SQL LOCK TABLE
Popular Course in this category
SQLite Tutorial (3 Courses, 1 Project)
  3 Online Courses |  1 Hands-on Projects |  11+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

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

Let’s Get Started

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