PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    It’s just the trivial structure of:

    town    (country_id*, region_id*, town_id*, town_name)
region  (country_id*, region_id*, region_name)
country (country_id*, country_name)

    Where the primary key columns are marked with *. Foreign keys are the obvious ones.

    I don’t know why people shy away from multi-column keys so much. Well, OK, I suspect it’s because most ORM examples stick to a single autogenerated id.

    Login or Signup to reply.
  2. 0

    You’d have to have redundant columns and foreign keys like this:

    CREATE TABLE countries (
   id bigint PRIMARY KEY,
   name text UNIQUE NOT NULL
);

CREATE TABLE regions (
   id bigint PRIMARY KEY,
   name text NOT NULL,
   country_id bigint REFERENCES countries NOT NULL,
   -- redundant; only used to reference from "towns"
   UNIQUE (country_id, id)
);

CREATE TABLE towns (
   id bigint PRIMARY KEY,
   name text NOT NULL,
   region_id bigint NOT NULL,
   country_id bigint NOT NULL,
   FOREIGN KEY (country_id, region_id) REFERENCES regions (country_id, id),
   -- this enforces your desired constraint
   UNIQUE (name, country_id)
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search