Identifying one-to-many relationship

Question

I have some questions about meanings of identifying one to many relationship. I read some other related questions on stack overflow, but I need a bit more information :)

Lets suppose we have table "Country" and table "Cities". It seems to me that this is example of one-to-many identifying relationship. But when I use MySql Workbench to create one-to-many identifying relationship between these two tables I get the following:

Countries
---------
country_id (PK)
...

Cities
--------
city_id    (PK)
country_id (PK)
...

We have composite primary key in Cities table, and it will allow following rows in that table (lets assume country_id and city_id are strings for better readability):

1) France, Paris
2) England, London
3) England, Manchester
4) France, London

In order to have it correct way, we need to put UNIQUE constraint on city_id, so that it can belong to only one country. But isn't it more clear then to just make country_id as NOT_NULL (FK) in Cities table and get the same effect:

Cities
---------
city_id (PK)
country_id (FK) (NOT_NULL) 

So, is this identifying or non identifying relationship? Seems to me that it is "logically identifying", but by definition it is non-identifying, since parent PK is not part of child PK. It is a bit confusing :)

Answer 1

If the key of cities is (country_id, city_id) then the relationship is "identifying" - meaning that the primary key is partly or wholly a foreign key reference to another table. If country_id is not part of the primary key then it is non-identifying.

Those two different keys would make the table represent very different things in each case but only you can say which better fits your requirements.

Don't worry too much about the concept of identifying vs non-identifying relationships. It is a concept that originates in ER modelling but in relational database design it is usually of very little practical importance.

Answer 2

You have in your own example data the counter-argument for your suggestion. London appears as a city in both England and France, but they are not the same city; The city is identified not by it's city_id, which is the name of the city, but by the pair country_id, city_id. There's no other natural key for this kind of data. If you wanted a single column primary key, you would be forced to invent a surrogate key to act as the primary key (say, an autoincremented Integer)