Country as natural primary key

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Country as natural primary key

Post by matthijs »

So I've just discovered the big debate about using natural (character) primary keys versus (integer) surrogate keys in database design.

I'm used to using auto incrementing INTs as my primary keys mostly but after reading a bit more I realize that sometimes using natural and possibly combined keys is also possible and better. See for example
http://database-programmer.blogspot.com ... ch-to.html

Now I wonder if I should change some of my integer primaries to naturals. For example, I have

Code: Select all

 
CREATE TABLE `Countries` (
    `country_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `country_name` varchar(255) NOT NULL,
    PRIMARY KEY  (`country_id`)
) ENGINE=InnoDB;
CREATE TABLE `Areas` (
    `area_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `area_name` varchar(255) NOT NULL,
    `country_id` int(10) UNSIGNED NOT NULL,
    PRIMARY KEY  (`area_id`),
    UNIQUE KEY (`area_name`, `country_id`),
    FOREIGN KEY (`country_id`) REFERENCES `Countries` (`country_id`)
) ENGINE=InnoDB;
 
I could easily change that to:

Code: Select all

 
CREATE TABLE `Countries` (
    `country_name` varchar(255) NOT NULL,
    PRIMARY KEY  (`country_name`)
) ENGINE=InnoDB;
CREATE TABLE `Areas` (
    `area_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `area_name` varchar(255) NOT NULL,
    `country_name` varchar(255) NOT NULL,
    PRIMARY KEY  (`area_id`),
    UNIQUE KEY (`area_name`, `country_name`),
    FOREIGN KEY (`country_name`) REFERENCES `Countries` (`country_name`)
) ENGINE=InnoDB;
 
One advantage I see is:
There are more tables referencing to the tables above. When querying for data in them I always have to do joins to both Areas and Countries tables. Now when I would replace the integer primary in the country table with the natural primary it will save me a join in all those queries (since I can get the name of the country from the Areas table).

I understand it's a big and difficult debate. But looking at this example with the Countries table, what do you think about removing the surrogate INT key there?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: Country as natural primary key

Post by Luke »

In that particular case I can't see anything wrong with using a natural key. I am running into an issue with a legacy app an old employee wrote that uses the username in the "login" table as the key and it is hell to work with the damn thing... you just have to be careful and think about all the options... its case by case as far as Im concerned.

EDIT: by the way that's an awesome resource you found there.... I bookmarked it ;)
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Country as natural primary key

Post by matthijs »

Yeah I thought so as well. The only drawback I could think of is that the list of countries changes sometimes. Not often, but still. Having a separate INT as primary supposedly makes it easier to handle if a countries' name would change.

And indeed that's a great resource I linked to. I found out this morning and have been reading all day. An amazing amount of articles.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Country as natural primary key

Post by onion2k »

Interesting. I don't think I'd use them for countries, but I would for other things.

Reading the rest of the article... I am sooooo guilty of breaking that "magic values" rule. :lol:
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Country as natural primary key

Post by matthijs »

It's a good article because it discusses many points of view, many pros and cons of each situation. After reading some more it seems this is one of those issues in which there is no single right or wrong, but many "depends on".

In my case I'm still not sure what to do. One thing to consider is that I fill in the Countries table myself. So I'm sure what the list is, that it stays that way (at least for the next few years), so I can rely on it to be a useful natural key.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Country as natural primary key

Post by jayshields »

My rule of thumb is that if there is any chance that the primary key will ever need to change, then don't use that field as the primary key - there is some chance that you might misspell a coutry name or that a country will change it's name or that another country will appear with the same name (not to mention the fact that country names are different in different languages).
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Country as natural primary key

Post by matthijs »

That's true. But at the same time. Let's say a country name changes. So what? With a good referential foreign key setup and a simple "on update cascade" it should be perfectly manageable, wouldn't you say?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Country as natural primary key

Post by onion2k »

matthijs wrote:With a good referential foreign key setup and a simple "on update cascade" it should be perfectly manageable, wouldn't you say?
MySQL can't do that yet can it? Not with MYISAM tables anyway.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Country as natural primary key

Post by matthijs »

Post Reply