In this tutorial, you will learn-
- SQLite Constraints
- SQLite Primary Key
- Not null constraint
- DEFAULT Constraint
- UNIQUE constraint
- CHECK constraint
- SQLite Foreign Key
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.
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.
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:
- To create a combination of columns as a primary key:
PRIMARY KEY(ColumnName1, ColumnName2);
SQLite Not null constraint prevents a column from having a null value:
ColumnName INTEGER NOT NULL;
SQLite Default constraint if you don’t insert any value in a column, the default value will be inserted instead.
ColumnName INTEGER DEFAULT 0;
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 it will prevent duplicate values among all the values of the column.
EmployeeId INTEGER NOT NULL UNIQUE;
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 a condition to check an inserted value, if the value doesn’t match the condition, it won’t be inserted.
Quantity INTEGER NOT NULL CHECK(Quantity > 10);
You can’t insert a value less than 10 in the “Quantity” column.
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:
PRAGMA foreign_keys = ON;
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.
CREATE TABLE [Departments] ( [DepartmentId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [DepartmentName] NVARCHAR(50) NULL ); CREATE TABLE [Students] ( [StudentId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, [StudentName] NVARCHAR(50) NULL, [DepartmentId] INTEGER NOT NULL, [DateOfBirth] DATE NULL, FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId) );
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:
INSERT INTO Departments VALUES(1, 'IT'); INSERT INTO Departments VALUES(2, 'Arts');
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:
INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);
The row won’t be inserted, and you will get an error saying that: FOREIGN KEY constraint failed.