Учебники

5) первичный и внешний ключ

В этом уроке вы узнаете

Ограничение SQLite

Ограничения столбца применяют ограничения и правила к значениям, вставленным в столбец, для проверки вставленных данных. Ограничения столбцов определяются при создании таблицы в определении столбца.

Первичный ключ SQLite

Все значения в столбце первичного ключа должны быть уникальными и не равными NULL

Первичный ключ может быть применен только к одному столбцу или к комбинации столбцов, в последнем случае комбинация значений столбцов должна быть уникальной для всех строк таблиц.

Существует много разных способов определения первичного ключа в таблице, например:

  • В самом определении столбца:

    Синтаксис:

    ColumnName INTEGER NOT NULL PRIMARY KEY;				
  • Как отдельное определение:
    PRIMARY KEY(ColumnName);					
  • Чтобы создать комбинацию столбцов в качестве первичного ключа:
    PRIMARY KEY(ColumnName1, ColumnName2);

Не нулевое ограничение

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

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

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:

Учебник по первичному и внешнему ключу SQLite

Затем попробуйте вставить нового студента с идентификатором отдела, которого нет в таблице отделов:

INSERT INTO Students(StudentName,DepartmentId) VALUES('John', 5);

Строка не будет вставлена, и вы получите сообщение об ошибке: ограничение FOREIGN KEY не выполнено.