Implementing complex references without a trigger in PostgreSQL

Question

I am creating a PostgreSQL database: Country - Province - City. A city must belong to a country and can belong to a province. A province must belong to a country. A city can be capital of a country:

CREATE TABLE country (
    id serial NOT NULL PRIMARY KEY,
    name varchar(100) NOT NULL
);

CREATE TABLE province (
    id serial NOT NULL PRIMARY KEY,
    name varchar(100) NOT NULL,
    country_id integer NOT NULL,
    CONSTRAINT fk_province_country FOREIGN KEY (country_id) REFERENCES country(id)
);

CREATE TABLE city (
    id serial NOT NULL PRIMARY KEY,
    name varchar(100) NOT NULL,
    province_id integer,
    country_id integer,
    CONSTRAINT ck_city_provinceid_xor_countryid 
        CHECK ((province_id is null and country_id is not null) or 
                (province_id is not null and country_id is null)),
    CONSTRAINT fk_city_province FOREIGN KEY (province_id) REFERENCES province(id),
    CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country(id)
);

CREATE TABLE public.capital ( 
    country_id integer NOT NULL,
    city_id integer NOT NULL,
    CONSTRAINT pk_capital PRIMARY KEY (country_id, city_id),
    CONSTRAINT fk_capital_country FOREIGN KEY (country_id) REFERENCES country(id),
    CONSTRAINT fk_capital_city FOREIGN KEY (city_id) REFERENCES city(id)
);

For some (but not all) countries I will have province data, so a city will belong to a province, and the province to a country. For the rest, I shall just know that the city belongs to a country.

Issue #1: Concerning the countries that I do have province data, I was looking for a solution that will disallow a city to belong to a country and at the same time to a province of a different country.

I preferred to enforce through a check constraint that either province or country (but NOT both) are not null in city. Looks like a neat solution.

The alternative would be to keep both province and country info within the city and enforce consistency through a trigger.

Issue #2: I want to disallow that a city is a capital to a country to which it does not belong. That seems impossible without a trigger after my solution to issue #1 because there is no way to directly reference the country a city belongs to.

Maybe the alternative solution to issue #1 is better, it also simplifies future querying.

Answer 1

I would radically simplify your design:

CREATE TABLE country (
   country_id serial PRIMARY KEY  -- pk is not null automatically
  ,country text NOT NULL          -- just use text
  ,capital int REFERENCES city    -- simplified
);

CREATE TABLE province (           -- never use "id" as name
   province_id serial PRIMARY KEY
  ,province text NOT NULL         -- never use "name" as name
  ,country_id integer NOT NULL REFERENCES country  -- references pk per default
);

CREATE TABLE city (
   city_id serial PRIMARY KEY
  ,city text NOT NULL
  ,province_id integer NOT NULL REFERENCES province,
);

• Since a country can only have one capitol, no n:m table is needed.

• Never use "name" or "id" as column names. That's an anti-pattern of some ORMs. Once you join a couple of tables (which you do a lot in relational databases) you end up with multiple columns of the same non-descriptive name, causing all kinds of problems.

• Just use text. No point in varchar(n). Avoid problem like this.

• The PRIMARY KEY clause makes a column NOT NULL automatically. (NOT NULL sticks, even if you later remove the pk constraint.)

And most importantly:

• A city only references one province in all cases. No direct reference to country. Therefore mismatches are impossible, on-disk storage is smaller and your whole design is much simpler. Queries are simpler.

  For every country enter a single dummy-province with an empty string as name (''), representing the country "as a whole". (Possibly even with the same id, you could have provinces and countries draw from the same sequence ...). Do this automatically in a trigger. This trigger is optional, though.

  I chose an empty string instead of NULL, so the column can still be NOT NULL and a unique index over (country_id, province) does its job. You can easily identify this province representing the whole country and deal with it as appropriate in your application.

I am using a similar design successfully in multiple instances.

Answer 2

I think you can actually implement all these constraints without using triggers. It does require a bit of restructuring of the data.

Start by enforcing the relationship (using foreign keys) of:

city --> province --> country

For countries with no province information, invent a province -- perhaps with the country name, perhaps some weird default name ("CountryProvince"). This allows you to have only one set of relationship between the three entities. It automatically ensures that cities and provinces are in the right country, because you would get the country through the province.

The final question is about capitals. There is a way that you can implement and enforce uniqueness with no triggers. Keep a flag in the cities table and use a unique filtered index to guarantee uniqueness:

create unique index on cities_capitalflag on cities(capitalflag) where capitalflag = 'Y';

EDIT:

You are right about the the filtered index needing the country. But that requires storing the country in that table, which, in turn, requires keeping the provinces and cities aligned with respect to country. So, this solution gets close to not needing triggers but it isn't there.