Проверка целостности данных

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

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

Целостность данных означает:

• Никаких "пустых" ссылок: если мы ссылаемся на что-то в другой таблице, то это "что-то" всегда существует.
• Устойчивость к ошибкам модификации: если удаляем из таблицы значение, на которое ссылаются другие записи, база данных должна нас предупредить или корректно обработать эту ситуацию.

Именно для этого в PostgreSQL используются внешние ключи.

Как внешние ключи обеспечивают целостность данных?

Когда в таблице создается внешний ключ, PostgreSQL автоматически проверяет:

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

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

Пример: целостность данных в таблицах студентов и курсов

Допустим, у нас есть две таблицы — students и courses. Каждый студент может записаться на несколько курсов. Для отражения этой связи мы используем таблицу enrollments. Рассмотрим ситуацию, когда кто-то пытается записать студента на несуществующий курс.

Шаг 1. Создадим три связанные таблицы:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id)
);

Здесь:

• В таблице enrollments мы явно указали внешние ключи student_id и course_id, которые ссылаются на первичные ключи таблиц students и courses.

Типичные проверки целостности данных

1. Проверка при вставке данных

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

Пример:

INSERT INTO enrollments (student_id, course_id)
VALUES (999, 1); -- Ошибка! Студент с ID 999 не существует.

Сообщение об ошибке:

ERROR:  insert or update on table "enrollments" violates foreign key constraint "enrollments_student_id_fkey"
DETAIL:  Key (student_id)=(999) is not present in table "students".

2. Проверка при удалении данных

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

Пример:

INSERT INTO students (name) VALUES ('Alice');
INSERT INTO courses (title) VALUES ('Mathematics');

INSERT INTO enrollments (student_id, course_id)
VALUES (1, 1); -- Успешная вставка

DELETE FROM students WHERE student_id = 1; -- Ошибка, потому что студент всё еще записан на курс!

Сообщение об ошибке:

ERROR:  update or delete on table "students" violates foreign key constraint "enrollments_student_id_fkey" on table "enrollments"
DETAIL:  Key (student_id)=(1) is still referenced from table "enrollments".

Для корректного удаления записей в таких случаях мы пользуемся стратегиями CASCADE, SET NULL или RESTRICT, которые обсуждались ранее.

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

Пример 1: Автоматическая защита от некорректных данных

С помощью внешних ключей PostgreSQL автоматически предотвращает вставку "несуществующих" данных:

-- Попробуем добавить несуществующих студентов в курс:
INSERT INTO enrollments (student_id, course_id)
VALUES (42, 1); -- Ошибка! Студент с ID 42 не существует.

Это гарантирует, что студент не сможет записаться на курс, если он не существует в таблице students.

Пример 2: Удаление данных с помощью ON DELETE CASCADE

Если внешний ключ настроен на каскадное удаление ON DELETE CASCADE, то при удалении записи в родительской таблице связанные данные в дочерней таблице также будут удалены.

ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey; -- Удаляем старый внешний ключ

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON DELETE CASCADE;

DELETE FROM students WHERE student_id = 1; -- Теперь удалятся и записи из таблицы enrollments

Пример 3: Обработка изменений с помощью ON UPDATE

Если внешний ключ настроен с помощью ON UPDATE CASCADE, то при изменении значения в родительской таблице PostgreSQL автоматически обновит данные в дочерней таблице.

-- Настроим внешний ключ так, чтобы изменения родительского ключа автоматически применились к дочерней таблице:
ALTER TABLE enrollments DROP CONSTRAINT enrollments_student_id_fkey;

ALTER TABLE enrollments
ADD CONSTRAINT enrollments_student_id_fkey FOREIGN KEY (student_id)
REFERENCES students(student_id) ON UPDATE CASCADE;

-- Изменим идентификатор студента:
UPDATE students SET student_id = 10 WHERE student_id = 1;

-- Теперь в таблице enrollments student_id тоже обновится на 10.

Тестирование целостности данных

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

1. Попробуйте вставить данные с неверным student_id или course_id.
2. Удалите данные из students и проверьте, как ведет себя таблица enrollments.
3. Измените данные в таблице students и убедитесь, что связанные записи обновлены.

Особенности при работе с внешними ключами

Иногда возникают ситуации, которые могут сбить с толку:

• Отсутствие индекса. Если родительская таблица (students, например) не имеет индексированного столбца, на который идет ссылка, PostgreSQL может "пытаться" работать медленнее. Поэтому важно, чтобы первичный ключ в родительской таблице всегда был индексом.
• Циклические ссылки. Если две таблицы ссылаются друг на друга, это может вызывать сложности при вставке данных. В таких случаях требуется подходить к проектированию более тщательно.
• Удаление всех записей. Если требуется удалить все записи с каскадным удалением, нужно учитывать характер данных в дочерней таблице, чтобы избежать неожиданного поведения.

Чтобы избежать этих проблем, важно грамотно проектировать таблицы и тестировать правила связей до их применения в реальной базе данных.