Управление ограничениями SQLite (SQLite Constraint Management)

Введение

В этой лабораторной работе вы изучите управление ограничениями SQLite для обеспечения целостности данных. Вы начнете с определения ограничений внешнего ключа (foreign key constraints) для установления связей между таблицами, внедрения ограничений CHECK, создания составных ключей (composite keys) и, наконец, тестирования нарушений ограничений, чтобы понять, как SQLite обеспечивает соблюдение этих правил. Этот практический опыт даст вам практическое понимание того, как поддерживать согласованность данных в ваших базах данных SQLite.

Skills Graph

Создание таблиц с ограничением внешнего ключа (Foreign Key Constraint)

В этом шаге вы создадите две таблицы: customers и orders, и установите между ними ограничение внешнего ключа (foreign key constraint). Это ограничение гарантирует, что каждый заказ связан с действительным клиентом.

Сначала откройте оболочку SQLite, выполнив следующую команду в терминале:

sqlite3 /home/labex/project/database.db

Эта команда открывает соединение с файлом базы данных SQLite с именем database.db. Если файл не существует, SQLite создаст его. Теперь вы должны увидеть приглашение sqlite>.

Теперь создайте таблицу customers с помощью следующей SQL-команды:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

Эта команда создает таблицу с именем customers с четырьмя столбцами: customer_id, first_name, last_name и email. Столбец customer_id является первичным ключом (primary key) и будет автоматически увеличиваться для каждого нового клиента.

Далее создайте таблицу orders с ограничением внешнего ключа, ссылающимся на таблицу customers:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_id INTEGER,
    order_date TEXT,
    total REAL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Эта команда создает таблицу с именем orders с четырьмя столбцами: order_id, customer_id, order_date и total. Столбец order_id является первичным ключом. Предложение FOREIGN KEY (customer_id) REFERENCES customers(customer_id) устанавливает ограничение внешнего ключа, гарантируя, что customer_id в таблице orders ссылается на действительный customer_id в таблице customers.

Чтобы убедиться, что таблицы были созданы, вы можете использовать следующую команду:

.tables

Эта команда выведет список всех таблиц в базе данных. Вы должны увидеть customers и orders в выводе.

Вставка данных и проверка ограничения внешнего ключа (Foreign Key Constraint)

В этом шаге вы вставите данные в таблицы customers и orders и проверите ограничение внешнего ключа (foreign key constraint).

Сначала вам нужно включить ограничения внешнего ключа в SQLite (они отключены по умолчанию):

PRAGMA foreign_keys = ON;

Теперь вставьте некоторые данные в таблицу customers:

INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', '[email protected]'),
('Jane', 'Smith', '[email protected]');

Эта команда вставляет двух новых клиентов в таблицу customers. customer_id будет назначен автоматически.

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

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2023-01-01', 100.00);

Эта команда вставляет новый заказ в таблицу orders, связывая его с клиентом, чей customer_id равен 1.

Далее попробуйте вставить заказ с customer_id, которого не существует в таблице customers:

INSERT INTO orders (customer_id, order_date, total) VALUES
(99, '2023-01-02', 50.00);

Вы увидите сообщение об ошибке, похожее на следующее: Error: FOREIGN KEY constraint failed. Это подтверждает, что ограничение внешнего ключа работает правильно, не позволяя вам создать заказ для несуществующего клиента.

Чтобы просмотреть данные в таблицах, вы можете использовать следующие команды:

SELECT * FROM customers;
SELECT * FROM orders;

Эти команды отобразят содержимое таблиц customers и orders соответственно.

Реализация ограничения CHECK (CHECK Constraint)

В этом шаге вы создадите новую таблицу customers_with_check, которая включает ограничение CHECK (CHECK constraint), чтобы гарантировать, что все адреса электронной почты содержат символ @.

Сначала давайте создадим новую таблицу с ограничением CHECK напрямую:

CREATE TABLE customers_with_check (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT,
    last_name TEXT,
    email TEXT CHECK (email LIKE '%@%')
);

Эта команда создает новую таблицу с именем customers_with_check с ограничением CHECK в столбце email, которое гарантирует, что он содержит символ @. Оператор LIKE используется для сопоставления с шаблоном, а % является подстановочным знаком (wildcard character), который соответствует любой последовательности символов.

Теперь давайте скопируем существующие данные о клиентах в новую таблицу:

INSERT INTO customers_with_check (customer_id, first_name, last_name, email)
SELECT customer_id, first_name, last_name, email FROM customers;

Теперь попробуйте вставить нового клиента с недействительным адресом электронной почты:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Charlie', 'Davis', 'invalid-email');

Вы должны увидеть сообщение об ошибке, похожее на следующее: Error: CHECK constraint failed: email. Это указывает на то, что ограничение CHECK предотвратило вставку недействительного адреса электронной почты.

Чтобы убедиться, что ограничение CHECK работает, вставьте клиента с действительным адресом электронной почты:

INSERT INTO customers_with_check (first_name, last_name, email) VALUES
('Alice', 'Brown', '[email protected]');

Эта команда должна выполниться успешно, так как адрес электронной почты содержит символ @.

Создание таблицы с составным ключом (Composite Key)

В этом шаге вы создадите таблицу с именем enrollments с составным ключом (composite key), состоящим из столбцов student_id и course_id.

Выполните следующую SQL-инструкцию в оболочке SQLite:

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date TEXT,
    PRIMARY KEY (student_id, course_id)
);

Эта команда создает таблицу с именем enrollments с тремя столбцами: student_id, course_id и enrollment_date. Предложение PRIMARY KEY (student_id, course_id) указывает, что первичный ключ (primary key) для таблицы состоит из столбцов student_id и course_id. Это означает, что комбинация student_id и course_id должна быть уникальной для каждой строки в таблице.

Теперь вставьте некоторые данные в таблицу enrollments:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-01'),
(2, 101, '2023-01-02'),
(1, 102, '2023-01-03');

Это вставит три строки в таблицу enrollments.

Далее попробуйте вставить строку с теми же student_id и course_id, что и существующая строка:

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
(1, 101, '2023-01-04');

Вы должны увидеть сообщение об ошибке, похожее на следующее: Error: UNIQUE constraint failed: enrollments.student_id, enrollments.course_id. Это указывает на то, что ограничение составного ключа (composite key constraint) предотвратило вставку дублирующейся строки.

Чтобы убедиться, что таблица была создана правильно, вы можете использовать команду .tables в оболочке SQLite:

.tables

Вы должны увидеть enrollments в выводе.

Итог (Summary)

В этой лабораторной работе вы узнали, как определять и реализовывать ограничения (constraints) в SQLite для обеспечения целостности данных (data integrity). Вы создали таблицы с ограничениями внешнего ключа (foreign key constraints), реализовали ограничения CHECK для проверки данных и создали таблицы с составными ключами (composite keys) для уникальной идентификации строк на основе нескольких столбцов. Понимая и используя эти ограничения, вы можете создавать надежные и устойчивые базы данных, которые поддерживают согласованность данных (data consistency).