Country as natural primary key
Posted: Thu Aug 28, 2008 1:23 pm
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
I could easily change that to:
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?
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;
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;
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?