Indicating a "canonical" record in a one-to-many table

Question

Imagine we have a table of countries, and a table of cities. A country can of course have many cities, but a city can only be in one country, thus a one-to-many relationship makes intuitive sense:

countries
| id | name    |
|  1 | Lorwick |
|  2 | Belmead |

cities
| id | country | name        |
|  1 |       1 | Marblecrest |
|  2 |       1 | Westacre    |
|  3 |       2 | Belcoast    |
|  4 |       1 | Rosemarsh   |
|  5 |       2 | Vertston    |

But in addition to our one-to-many relationship, we want to describe the one-to-one relationship of national capitals. If it matters, assume that capitals may change fairly regularly, and for that matter cities appear and vanish at will, and that cities may switch countries. Point is, this data is unstable.

I see a couple of options:

1. Add an int column capital to countries which cannot be null. Pro: always exactly one city; Con: not associated with the city, nothing enforcing the city is in the country, or that it even exists.

2. Add a boolean column capital to cities, which if true indicates the city is the capital of the associated country. Pro: directly associated with the city in question, no duplicate columns indicating hierarchy; Con: pretty sure this is poor normalization as there's nothing stopping there being zero, or more than one, "capital" for a given country.

3. Create an additional table capitals with columns country and city and a unique constraint on both columns (or at least on city). Pro: feels cleaner, easy joining on either countries or cities; Con: still doesn't ensure city is in country, or that either exist.

What is the most normalized and/or best way to represent this relationship? Is there any way to ensure each country has exactly one capital which does in fact exist and resides inside that country? I imagine it's not possible, in which case, how can I best minimize issues for my client code?

I'm currently using SQLite, but I'm interested in generalized answers, regardless of the underlying database.

I did a little digging and found Indicating primary/default record in database but I don't think this really answers my question.

---

PS: It's not that bad if there's no capital (there may be no cities!), but it would be bad if there were multiple.

Answer 1

I think the requirement "each country has exactly one capital" conflicts with the requirement "cities appear and vanish at will". If a city can vanish, it follows that a capital city can vanish, too.

You can enforce the constraint "each country has [zero or] one capital which does in fact exist and resides inside that country" with a foreign key constraint on a table of capitals.

create table capitals (
  country_id integer primary key,
  city_id integer not null,
  foreign key (country_id, city_id) references cities (country_id, city_id)
);

In that table, the primary key constraint guarantees that there can be no more than one capital per country. The foreign key constraint guarantees that that the capital you choose exists in the country you choose. In the referenced table (the "cities" table), you also need a unique constraint on {city_id, country_id}; since {city_id} is unique in the "cities" table, {city_id, country_id} will necessarily be unique in that table, too, so that's not a problem.

The declarative "way" to guarantee a one-to-one relationship between countries and capitals (not a one-to-zero-or-one relationship) is to use an assertion. But I don't know of any current SQL dbms that supports CREATE ASSERTION. That forces us rely on one or more of these:

• triggers and possibly deferred constraints,
• application code, or
• administrative procedures.

(Initially, you'd have to enter a row in the three tables "countries", "cities", and "captials" in a single transaction in order to satisfy all the constraints. I think you'll need deferred constraints for that, but I haven't had coffee yet today.)

Answer 2

For clarity and simplicity, I'd add the boolean IsCapital column to the cities table. Then add a trigger that sets all other cities (that share the updated record's country) IsCapital = false when IsCapital is set to true on a record. This will handle most of your concerns. The one case to ensure there is exactly one capital per country isn't really possible, you can ensure there is 0 or 1, but since the cities table has a FK constraint to countries, there is always going to be a point in time where inserted countries will have no cities that can be set as the capitol.

FWIW, I think logic should be left to the app, referential integrity to the database.

Answer 3

To make sure there is exactly one capital per country and the capital is not a city from a different country, do this:

Note how we use the identifying relationship to migrate the COUNTRY_ID to CITY's PK, so it can be migrated back to the CONTRY - this is what guarantees a capital must actually belong to the country it is the capital of.

The circular reference here prevents the insertion of new data, which is resolved using deferred foreign keys if the DBMS supports them. Otherwise, you can just leave COUNTRY.CAPITAL_NO NULL-able (and enforce its eventual non-NULL-ness at the application level).¹

---

¹ This assumes the DBMS has MATCH SIMPLE foreign keys (i.e. FK is ignored if any of its components are NULL). If the DBMS supports only MATCH PARTIAL or FULL (such as MS Access), you are out of luck, and would have to emulate the FK through non-declarative means (triggers or application code).