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.

Databases

Mayur Pande
PLUS
Mayur Pande
Courses Plus Student 11,711 Points
Posted by Mayur Pande
Mayur Pande
Courses 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

1 Answer

Kevin D
Kevin D
8,646 Points
Kevin D
Kevin D
8,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

Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points
Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points

Kevin Dang thanks for answer. However you have said I am not referencing the primary key, but my first table has two primary keys.

Kevin D
Kevin D
8,646 Points
Kevin D
Kevin D
8,646 Points

You're right! My mistake.

What happens if you add a primary key in the regions table?

Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points
Mayur Pande
Mayur Pande
Courses Plus Student 11,711 Points

no unfortunately it doesn't like that! Normally when I do this it works fine. Not sure what I am doing wrong