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:

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

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:

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:

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