Manolis Manolis - 2 months ago 10
SQL Question

Implementing complex references without a trigger in PostgreSQL

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 (
name varchar(100) NOT NULL

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

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)

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.


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

   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.