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?