CODEWITHSUNDEEP
mysql
Jake Wilson
Jake Wilson

Reputation: 91193

Foreign Key Constraint is incorrectly formed

There are several other questions about this topic that I have gone through, but I can't seem to figure out how their solutions apply to my tables. Check out the sqlfiddle. You can see it builds the schema just fine.

Basically, one table is a table of contacts/people. The second table is a table of countries. I am attempting to create a foreign key reference between contacts.country_id and countries.id.

Now, add the following to the panel on the left side:

ALTER TABLE `ultra_contacts`
ADD INDEX `fk_test` (`country_id`),
ADD CONSTRAINT `fk_test` FOREIGN KEY (`country_id`) REFERENCES `ultra_countries` (`id`) ON UPDATE CASCADE ON DELETE CASCADE`

The alter table code is not working for some reason. Any help would be appreciated.

The error is: Schema Creation Failed: Can't create table 'db_e342e.#sql-7711_1a4d2' (errno: 150): Using a 3rd party program (HeidiSQL) the error is a bit more detailed:

Foreign key constraint is incorrectly formed

Upvotes: 1

Views: 7844

Answers (1)

Jake Feasel
Jake Feasel

Reputation: 16955

You're trying to use foreign keys on a MyISAM table, which is not allowed (they only work with InnoDB). Take a look here: http://sqlfiddle.com/#!2/64951 All I've changed from your original is the table type (from MyISAM to InnoDB) and then I added the constraint. Worked fine.

Full disclosure - I'm the author of SQL Fiddle :)

Upvotes: 4

Related Questions