Как наложить нетривиальное ограничение уникальности на поле?

Question

[Иван Мельников]

Имеется кусок схемы БД:

Каким образом можно в таблице tp реализовать ограничение уникальности поля addr в рамках города (а не только района, как показано на схеме)?
Дальнейшей разумной нормализации данная схема не подлежит, но при этом ограничение уникальности поля addr не надуманно, а вытекает из модели данных.
Используемая СУБД: MySQL 8.0.

Comments

[Евгений Самсонов]

Как вариант, денормализировать и добавить city_id в TP

[#]

Евгений Самсонов, думаю это единственный вариант. но из любопытсва подпишусь

Answer 1

[orbit070]

Ограничение уникальности накладывается на поля в рамках одного отношения, то есть то что вам хочется провернуть, а именно заглянуть как-то в другую табу чтобы проверить уникальность, из коробки такой возможности нет. Два наиболее очевидных способа решения задачи:
1) Прокинуть city_id в нужную таблицу. Да, не по феньшую, но реальность такая что нормализованные базы данных существуют только в книгах по базам данных или во всяких простячковых проектах, добро пожаловать.
2) Оставить все по феньшую и просто повесить триггер, в котором и будет происходить проверка уникальности по городу.

Answer 2

[Vitsliputsli]

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

Answer 3

[beerchaser]

На мой взгляд в данном случае будет разумно сделать денормализацию - первый вариант, предложенный orbit070. Это позволит, во-первых, снизить вероятность указания неверного города (значения город/район для вставки устанавливаются вместе), во-вторых, в дальнейшем упростит запросы по таблице ТП.