Первичный ключ и внешний ключ SQLite с примерами

В разделах ниже приведено объяснение. SQLite Ограничения рассматриваются подробно, начиная с первичного ключа и внешнего ключа, которые определяют и связывают таблицы, и заканчивая правилами NOT NULL, DEFAULT, UNIQUE и CHECK, которые проверяют данные в каждом столбце.

SQLite ограничения

Ограничения столбцов устанавливают правила для значений, вставляемых в столбец, с целью проверки данных. Эти ограничения определяются при создании таблицы, в определении столбца. Они обеспечивают согласованность и точность хранимых данных, отклоняя значения, нарушающие установленные правила, такие как дубликаты, значения NULL или значения, отсутствующие в связанной таблице.

SQLite Основной ключ

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

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

Синтаксис:

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

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

ColumnName INTEGER NOT NULL PRIMARY KEY;

В качестве отдельного определения:

PRIMARY KEY(ColumnName);

Чтобы создать комбинацию столбцов в качестве первичного ключа:

PRIMARY KEY(ColumnName1, ColumnName2);

SQLite Ограничения NOT NULL, DEFAULT, UNIQUE и CHECK

Помимо основного ключа, SQLite Предоставляет несколько ограничений для столбцов, которые проверяют значения, введенные в таблицу. Ограничения NOT NULL, DEFAULT, UNIQUE и CHECK определены в определении столбца, и каждое из них применяет определенное правило к столбцу. тип данных и ценности.

Ограничение NOT NULL

SQLite Ограничение NOT NULL предотвращает появление нулевых значений в столбце:

ColumnName INTEGER  NOT NULL;

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

Достаточно воспользоваться ИИ-ассистентом SQLite Ограничение по умолчанию: если в столбец не вставляется никакое значение, вместо него вставляется значение по умолчанию.

Например:

ColumnName INTEGER DEFAULT 0;

Если вы напишете оператор INSERT и не укажете значение для этого столбца, то столбец будет иметь значение 0.

Уникальное ограничение

SQLite Ограничение UNIQUE предотвращает появление повторяющихся значений среди всех значений столбца.

Например:

EmployeeId INTEGER NOT NULL UNIQUE;

Это обеспечивает уникальность значения «EmployeeId»; повторяющиеся значения не допускаются. Обратите внимание, что это относится только к значениям столбца «EmployeeId».

ПРОВЕРИТЬ ограничение

SQLite Ограничение CHECK задает условие для проверки вставляемого значения. Если значение не соответствует условию, оно не будет вставлено.

Quantity INTEGER NOT NULL CHECK(Quantity > 10);

В столбце «Количество» нельзя ввести значение меньше 10.

SQLite Внешний ключ

SQLite Внешний ключ — это ограничение, которое проверяет наличие значения, присутствующего в одной таблице, в другой таблице, которая связана с первой таблицей, где определен внешний ключ.

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

В этом случае, при попытке вставить значение в этот столбец, внешний ключ гарантирует, что вставляемое значение существует в столбце ссылочной таблицы.

Обратите внимание, что ограничения внешнего ключа по умолчанию отключены. SQLiteСначала их необходимо включить, выполнив следующую команду:

PRAGMA foreign_keys = ON;

Ограничения внешнего ключа были введены в SQLite начиная с версии 3.6.19.

Пример SQLite Внешний ключ

Предположим, у нас есть две таблицы: «Студенты» и «Отделения».

В таблице Students содержится список студентов, а в таблице Departments — список факультетов. Каждый студент принадлежит к определенному факультету; то есть у каждого студента есть столбец departmentId.

Теперь мы рассмотрим, как ограничение внешнего ключа может помочь гарантировать, что значение идентификатора отдела в таблице Students должно существовать в таблице Departments.

Таким образом, если мы создадим ограничение внешнего ключа по полю DepartmentId в таблице Students, каждый вставленный departmentId должен присутствовать в таблице Departments.

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)
);

Чтобы проверить, как ограничения внешнего ключа могут предотвратить вставку неопределенного элемента или значения в таблицу, связанную с другой таблицей, рассмотрим следующий пример.

В этом примере таблица Departments имеет связь внешнего ключа с таблицей Students, поэтому любое значение departmentId, вставленное в таблицу Students, должно существовать в таблице Departments. Если вы попытаетесь вставить значение departmentId, которого нет в таблице Departments, ограничение внешнего ключа не позволит этого сделать.

Давайте добавим два отдела, «ИТ» и «Искусство», в таблицу «Отделы» следующим образом. Запросы на вставку:

INSERT INTO Departments VALUES(1, 'IT');
INSERT INTO Departments VALUES(2, 'Arts');

Эти два запроса должны вставить два отдела в таблицу Departments. Вы можете убедиться, что оба значения были вставлены, выполнив после этого запрос «SELECT * FROM Departments»:

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

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

Строка не будет вставлена, и вы получите ошибку: "Нарушение ограничения внешнего ключа".

Разница между первичным ключом и внешним ключом в SQLite

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

В примере со студентами и факультетами DepartmentId является первичным ключом таблицы «Факультеты» и внешним ключом в таблице «Студенты», который связывает каждого студента с соответствующим факультетом.

SQLite Составной первичный ключ

Составной первичный ключ — это первичный ключ, состоящий из двух или более столбцов. Он используется, когда ни один отдельный столбец сам по себе не является уникальным, но комбинация столбцов уникальна для каждой строки. SQLite рассматривает объединенные значения как один ключевой параметр.

Например, в таблице зачисления один и тот же студент может быть записан на множество курсов, а один и тот же курс — на множество других студентов, однако каждая пара «студент — курс» должна встречаться только один раз:

CREATE TABLE Enrollments (
	StudentId INTEGER NOT NULL,
	CourseId INTEGER NOT NULL,
	Grade TEXT,
	PRIMARY KEY (StudentId, CourseId)
);

В данном случае ни StudentId, ни CourseId сами по себе не являются уникальными, но пара (StudentId, CourseId) уникальна, поэтому один и тот же студент не может быть зачислен на один и тот же курс дважды. При использовании составного ключа обратите внимание на следующие моменты:

• Используйте составной ключ, если один столбец не может однозначно идентифицировать строку.
• Каждый столбец в составном ключе подчиняется правилам первичного ключа, поэтому объединенное значение должно быть уникальным и не содержать значений NULL.
• Составной ключ записывается как отдельный параметр PRIMARY KEY на уровне таблицы, а не внутри определения отдельного столбца.

SQLite Действия внешних ключей: при удалении и при обновлении

Внешний ключ также может управлять тем, что происходит с дочерними строками при удалении или обновлении родительской строки, на которую он ссылается. Эти ссылочные действия добавляются с помощью предложений ON DELETE и ON UPDATE при определении внешнего ключа. SQLite поддерживает пять действий:

• БЕЗДЕЙСТВИЕ — действие по умолчанию, которое вызывает ошибку, если дочерние строки по-прежнему ссылаются на родительскую.
• ОГРАНИЧЕНИЕ — предотвращает немедленное удаление или обновление до внесения каких-либо других изменений.
• УСТАНОВИТЬ НУЛЬ — устанавливает значение NULL для столбца внешнего ключа дочернего элемента.
• УСТАНОВИТЬ ПО УМОЛЧАНИЮ — устанавливает для столбца внешнего ключа дочернего элемента значение по умолчанию, указанное в его заявленном виде.
• CASCADE — Применяет те же изменения к дочерним строкам, поэтому удаление родительского элемента приводит к удалению его дочерних элементов.

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

CREATE TABLE Students (
	StudentId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	StudentName NVARCHAR(50) NULL,
	DepartmentId INTEGER NOT NULL,
	FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

Помните, что ссылочные операции выполняются только при включенной поддержке внешних ключей, поэтому выполняйте команду PRAGMA foreign_keys = ON в начале каждого соединения. Без неё... SQLite Анализирует пункты ON DELETE и ON UPDATE, но не применяет их на практике.

Часто задаваемые вопросы (FAQ)