How to impose a non-trivial uniqueness constraint on a field?

Question

MySQL

There is a piece of the database schema:

How can I implement the uniqueness constraint of the addr field in the tp table within the city (and not just the district, as shown in the diagram)? This scheme is not subject to further reasonable normalization, but at the same time, the restriction on the uniqueness of the addr field is not far-fetched, but follows from the data model. Used DBMS: MySQL 8.0.

Answer 1

The uniqueness constraint is imposed on the fields within the same relationship, that is, what you want to do, namely, to look somehow into another taboo to check the uniqueness, there is no such possibility out of the box. The two most obvious ways to solve the problem:
1) Throw city_id into the desired table. Yes, it's crazy, but the reality is that normalized databases exist only in books on databases or in any simple projects, welcome.
2) Leave everything as it is and just hang up a trigger, in which the uniqueness of the city will be checked.

Answer 2

In my opinion, in this case it would be reasonable to do denormalization - the first option proposed by orbit070. This will, firstly, reduce the likelihood of specifying an incorrect city (the city/region values ​​for insertion are set together), and secondly, it will further simplify queries on the TP table.