[SQL] Workarounds for unique index based on parent table

I have a table structure analogous to this

towns (id, name, region_id)
regions (id, name, country_id)
countries (id, name)


I need to enforce the uniqueness of town name within the country. In a given country, if we put together all towns that belong to its regions, town name should be unique.

I assume that it's not possible to achieve this directly, right? Any idea for a workaround?

The only workaround that comes into my mind is to have a redundant field towns.country_id, but I really don't like this solution because I want to make sure that towns.country_id coincides with towns.region.country_id.

Is there any way to make this waterproof? Maybe a trigger to autopopulate towns.country_id based on the parent region (by the way, it must be present, there is a foreign key towns.region_id)? Is it possible to write this trigger so that it prevents users from manually changing towns.country_id? It should exclusively be written by the trigger, automatically.

Continue reading...