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

Быстрый ответ

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

ALTER TABLE schema1.MyTable
ADD CONSTRAINT MyForeignKey
FOREIGN KEY (MyColumn) REFERENCES schema2.OtherTable (OtherColumn);

Здесь мы устанавливаем связь между MyTable из schema1 и OtherTable из schema2, где MyColumn ссылается на OtherColumn. Для обеспечения целостности данных необходимо правильно настроить доступ. Перед созданием связи удостоверьтесь, что обе таблицы доступны, и проиндексируйте столбец в родительской таблице для улучшения производительности запросов. Защита данных от нарушения целостности — ответственная задача, и для этого необходимо задействовать права доступа. Теперь всё готово к работе!

Эффективность индексации

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

CREATE INDEX idx_foreign_column ON schema2.OtherTable (OtherColumn);

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

Управление правами доступа, или как организовать правильную игру

Пользователь, добавляющий внешний ключ в таблицу, должен иметь доступ к обеим таблицам. Этому пользователю следует предоставить, например, право REFERENCES:

GRANT REFERENCES ON schema2.OtherTable TO YourUser;

Поддержка чистоты данных

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

Визуализация

Представьте две схемы как два района, таблицы в которых — это типа дома:

Схема А (🌆): [Дом 1 (🏠), Дом 2 (🏠)]
Схема В (🏙️): [Дом 3 (🏢)]

Если, к примеру, мы хотим привязать Дом 3 из Схемы В к Дому 1 из Схемы А с помощью внешнего ключа, это будет выглядеть так:

🏢🔗🌆🏠: Дом 3 (🏢 в Схеме В) связан с Домом 1 (🏠 в Схеме А)

Это можно сравнить с перепиской жителей разных районов через внешние ключи.

Надёжный план Б: управление ограничениями

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

ALTER TABLE schema1.MyTable
DROP CONSTRAINT MyForeignKey;

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

Тщательное удаление записей

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

DELETE FROM schema1.MyTable WHERE MyColumn = someValue;
DELETE FROM schema2.OtherTable WHERE OtherColumn = someValue;

Продуманное управление удалением данных помогает поддерживать порядок в базе данных.

Полезные материалы

1. PostgreSQL: Documentation: 16: 5.4. Constraints — Раздел об ограничениях и внешних ключах в PostgreSQL.
2. Create Foreign Key Relationships – SQL Server | Microsoft Learn — Руководство по созданию отношений между таблицами в SQL Server.
3. MySQL :: MySQL 8.0 Reference Manual :: 15.1.20.5 FOREIGN KEY Constraints — Справочник по созданию ограничений внешнего ключа в MySQL 8.0.
4. Foreign Keys – MariaDB Knowledge Base – Страница знаний по теме внешних ключей в MariaDB.
5. IBM Documentation — Инструкция IBM DB2 по работе с внешними ключами между схемами.