Page 1 of 1

Country as natural primary key

Posted: Thu Aug 28, 2008 1:23 pm
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?

Re: Country as natural primary key

Posted: Thu Aug 28, 2008 1:34 pm
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 ;)

Re: Country as natural primary key

Posted: Thu Aug 28, 2008 1:52 pm
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.

Re: Country as natural primary key

Posted: Thu Aug 28, 2008 2:19 pm
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:

Re: Country as natural primary key

Posted: Thu Aug 28, 2008 3:16 pm
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.

Re: Country as natural primary key

Posted: Thu Aug 28, 2008 6:13 pm
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).

Re: Country as natural primary key

Posted: Fri Aug 29, 2008 1:12 am
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?

Re: Country as natural primary key

Posted: Fri Aug 29, 2008 2:44 am
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.

Re: Country as natural primary key

Posted: Fri Aug 29, 2008 2:55 am
by matthijs