SAP

Must Learn!

Big Data

Web

In this tutorial, you will learn-

SQLite constraint

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.

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:

ColumnName INTEGER  NOT NULL;

DEFAULT Constraint

SQLite Default constraint if you don't insert any value in a column, the default value will be inserted instead.

For Example:

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

SQLite Unique constraint it will prevent duplicate values among all the values of the column.

For example:

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

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.

What is a SQLite Foreign KEY?

The SQLite foreign key is a constraint that verifies the existence of a 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 exits 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

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.

SQLite Primary & Foreign Key Tutorial

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

SQLite Primary & Foreign Key Tutorial

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.

 

YOU MIGHT LIKE: