Welcome to the Treehouse Community
The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.
Mayur PandeCourses Plus Student 11,711 Points
mysql cannot create foreign key
I have tried to create a foreign key in mysql workbench, I have two tables, but I get the error;
Error Code: 1215. Cannot add foreign key constraint
Here are my two tables;
CREATE TABLE `countries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `country_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, `country_id` int(11) NOT NULL, PRIMARY KEY (`id`,`country_id`) ) ENGINE=InnoDB AUTO_INCREMENT=207 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
here is my table with the foreign key constraint
CREATE TABLE `regions` ( `region_id` int(11) NOT NULL, `region_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL, foreign key(`region_id`) references countries(`country_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Kevin D8,646 Points
Oh, it could be that what you're referencing is not the primary key in the 'countries' table so that's why you're getting an error.
So you're doing: FOREIGN KEY (region_id) REFERENCES countries(country_id) <-- which is not the primary key for the 'countries' table
Maybe try changing it to : REFERENCES countries(id)
and see what happens!
There was a question on StackOverflow which seems to offer a solution for this: http://stackoverflow.com/questions/18435065/foreign-key-to-non-primary-key