Дана база данных: чек-лист проверки связей и структуры таблиц перед стартом

Ошибка в типе столбца проявится при миграции либо интеграции. А наличие резервной копии само по себе ничего не говорит о возможности восстановления.

Перед стартом нужно зафиксировать структуру таблиц, проверить первичные и внешние ключи, сопоставить типы связанных столбцов и определить поведение при удалении и обновлении записей. Это не универсальный SQL-чек-лист: ограничения и метаданные отличаются между СУБД. Ниже — порядок аудита, который помогает выявить архитектурные риски до деплоя.

1. Зафиксируйте исходную схему и границы проверки

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

Отдельно укажите среду: СУБД и её версию, схему или базу, пользователя, под которым выполняется проверка. Это имеет практический смысл. В PostgreSQL представления information_schema описывают объекты текущей базы и рассчитаны на переносимость между системами, но не включают все возможности, специфичные для PostgreSQL. Кроме того, видимость объектов зависит от прав пользователя.

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

До разбора деталей полезно зафиксировать четыре вопроса:

• Какая именно СУБД и версия обслуживает среду запуска?
• Каким пользователем будет работать приложение, а каким выполняется аудит?
• Какая версия схемы должна оказаться после деплоя?
• Какие таблицы и связи критичны для записи, удаления и чтения данных?

Такой срез отделяет дефект структуры от дефекта доступа и от расхождения между средами. Без него результаты аудита могут быть корректны только на тестовой базе.

2. Проверьте первичные ключи и индексы

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

В PostgreSQL первичный ключ автоматически создаёт уникальный B-tree-индекс и устанавливает NOT NULL для всех входящих в него столбцов. Это поведение относится именно к PostgreSQL. Его нельзя автоматически переносить на MySQL или другую СУБД без проверки документации и фактической схемы.

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

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

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

Для каждой таблицы зафиксируйте:

• первичный ключ и набор его столбцов;
• ограничения NOT NULL и UNIQUE;
• индексы, необходимые внешним ключам и рабочим запросам;
• соответствие ключа способу, которым приложение обновляет и удаляет строки.

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

3. Проверьте внешние ключи и ссылочную целостность

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

Для каждой связи проверьте направление: какая таблица хранит ссылку, а какая содержит целевую запись. Затем установите, соответствует ли связь фактической модели данных. Колонка customer_id в таблице заказов обычно ссылается на клиента, но одного названия недостаточно. Нужны ограничение в базе и совпадение с правилами приложения.

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

Для каждой связи сопоставьте:

1. Столбец или набор столбцов в дочерней таблице.

2. Целевую таблицу и столбцы, на которые указывает связь.

3. Уникальность целевых столбцов, необходимую для смысла связи.

4. Наличие ограничения в фактической схеме.

5. Существующие строки, не удовлетворяющие этому ограничению.

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

Не предполагайте, что любая СУБД сама создаст все нужные индексы на ссылающихся столбцах. Требование о наличии индексов и поведение при их создании зависят от реализации. Для PostgreSQL факт автоматического создания индекса относится к первичному ключу; он не означает, что индекс на столбце внешнего ключа появится автоматически.

4. Сопоставьте типы связанных столбцов

Несовпадение типов часто выглядит мелочью в миграции и становится проблемой на границе сервисов. Один столбец объявлен как целое число со знаком, второй — как беззнаковое. Идентификатор хранится строкой в одной таблице и числом в другой. В тестовой среде связь ещё работает из-за особенностей конкретной конфигурации, а в целевой СУБД ограничение не создаётся или запрос требует неявного преобразования.

Составьте матрицу связей и сравните типы попарно. Для каждого внешнего ключа должны быть понятны тип, длина или точность, знак числового значения и допустимость NULL. Для строковых идентификаторов учитывайте также правила сравнения и сопоставления, которые поддерживает выбранная СУБД. Необходимые свойства определяются конкретной технологией и схемой.

Проверьте также происхождение данных. Если идентификаторы приходят из внешнего API, их формат может отличаться от внутреннего. Анализ входных параметров здесь должен завершаться явным контрактом: что принимает приложение, как валидирует значение и в какой тип записывает его база. Скрытая конвертация при каждом запросе — слабая замена согласованной спецификации.

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

5. Определите правила удаления и обновления

Внешний ключ отвечает за допустимость связи. Правило ON DELETE или ON UPDATE определяет, что произойдёт с зависимыми строками при изменении целевой записи. В PostgreSQL доступны пять вариантов: CASCADE, SET NULL, SET DEFAULT, RESTRICT и NO ACTION.

Выбор влияет на поведение приложения и на сохранность данных. CASCADE может удалить зависимые строки автоматически. Это удобно для строго принадлежащих друг другу записей, но опасно, если дочерняя таблица содержит историю или данные, которые должны жить независимо. SET NULL допустим лишь там, где отсутствие ссылки разрешено схемой и логикой приложения. SET DEFAULT требует подходящего значения по умолчанию, которое само удовлетворяет ограничениям. RESTRICT и NO ACTION запрещают операцию в определённых условиях; детали поведения нужно сверить с выбранной СУБД.

Не выбирайте каскад по принципу «так миграция проходит». Для каждого правила ответьте на вопросы:

• Кто инициирует удаление или изменение ключа: пользователь, фоновая задача, интеграция?
• Должны ли зависимые строки удаляться вместе с родительской записью?
• Разрешено ли сохранять дочернюю запись без ссылки?
• Есть ли аудит, история или юридически значимые данные, которые нельзя удалять каскадно?
• Совпадает ли поведение базы с тем, что ожидает код сервиса?

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

6. Проверьте системные представления и права аудита

Системные каталоги и информационные представления полезны для автоматизации проверки, но их результат зависит от СУБД и пользователя. В PostgreSQL information_schema обеспечивает переносимый способ просмотра части метаданных. При этом представления не охватывают все специфичные функции PostgreSQL, а сведения об ограничениях могут быть видны не полностью.

В частности, information_schema.referential_constraints показывает ограничения, для которых текущий пользователь имеет право записи в ссылающуюся таблицу. Поэтому сравнивайте результаты под учётной записью с достаточными правами для аудита. Не повышайте права без необходимости на постоянной основе: используйте отдельную роль для проверок и ограничивайте её срок и область действия.

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

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

7. Ограничьте доступ и проверьте резервное копирование

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

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

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

Перед запуском

Зафиксируйте результат аудита в спецификации и привяжите его к версии схемы. Минимальный набор вопросов перед деплоем выглядит так:

• Первичные ключи объявлены и соответствуют модели адресации строк.
• Внешние ключи отражают фактические связи, а существующие данные им соответствуют.
• Типы связанных столбцов совместимы с требованиями конкретной СУБД.
• Индексы проверены отдельно для ключей и рабочих запросов.
• Правила удаления и обновления согласованы с поведением приложения.
• Аудит выполнялся пользователем, который видит нужные метаданные.
• Роли базы разделены по назначению и ограничены необходимыми правами.
• Резервные копии защищены, а процедура восстановления проверяется отдельно.

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