Primary Key And Foreign Key In Sqlite With Examples

SQLite Constraints SQLite Primary Key Not null constraint DEFAULT Constraint UNIQUE constraint CHECK constraint SQLite Foreign Key

SQLite Constraints

Column constraints enforce constraints and rules to the values inserted on a column in order to validate the inserted data. Columns constraints are defined when creating a table, in the column definition.

SQLite Primary Key

All the values on a primary key column should be unique and not null The primary key can be applied to only one column or on a combination of columns, in the latter case, the combination of the columns’ values should be unique for all the tables’ rows. Syntax: There are a lot of different ways to define a primary key on a table like:

In the column definition itself: ColumnName INTEGER NOT NULL PRIMARY KEY;

As a separate definition: PRIMARY KEY(ColumnName);

To create a combination of columns as a primary key: PRIMARY KEY(ColumnName1, ColumnName2);

Not null constraint

SQLite Not null constraint prevents a column from having a null value:

DEFAULT Constraint

SQLite Default constraint if you don’t insert any value in a column, the default value will be inserted instead. For Example: If you write an insert statement, and you didn’t specify any value for that column, the column will have the value 0.

SQLite UNIQUE constraint

SQLite Unique constraint it will prevent duplicate values among all the values of the column. For example: This will enforce the “EmployeeId” value to be unique, no duplicated values will be allowed. Note that, this applies on the values of the column “EmployeeId” only.

SQLite CHECK constraint

SQLite check constraint a condition to check an inserted value, if the value doesn’t match the condition, it won’t be inserted. You can’t insert a value less than 10 in the “Quantity” column.

SQLite Foreign Key

The SQLite foreign key is a constraint that verifies the existence of value present in one table to another table that has a relation with the first table where the foreign key is defined. While working with multiple tables, when there are two tables that relate to each other with one column in common. And if you want to ensure that the value inserted in one of them must exist in the other table’s column, then you should use a “Foreign key Constraint” on the column in common. In this case, when you try to insert a value on that column, then the foreign key will ensure that the inserted value exists in the table’s column. Note that Foreign keys constraints are not enabled by default in SQLite, you have to enable them first by the running the following command: Foreign key constraints were introduced in SQLite starting from version 3.6.19.

Example of SQLite Foreign Key

Suppose if we have two tables; Students and Departments. The Students table have a list of students, and the departments table has a list of the departments. Each student belongs to a department; i.e., each student has a departmentId column. Now, we will see how does the foreign key constraint can be helpful to ensure that the value of the department id in the students table must exist in the departments table.

So, if we created a foreign key constraint on the DepartmentId on the Students table, each inserted departmentId have to present in the Departments table. To check how foreign key constraints can prevent undefined element or value to be inserted in a table that has a relation to another table, we will look into the following example. In this example, the Departments table has a Foreign key constraint to the Students table, so any departmentId value inserted in the students table must exist in the departments table. If you are tried to insert a departmentId value that doesn’t exist in the departments table, the foreign key constraint would prevent you to do that. Let’s insert two departments “IT” and “Arts” into the departments table as following: The two statements should insert two departments into departments table, you can ensure that the two values were inserted by running the query “SELECT * FROM Departments” after that:

Then try to insert a new student with a departmentId that doesn’t exist in the departments’ table: The row won’t be inserted, and you will get an error saying that: FOREIGN KEY constraint failed.