1

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

Jake Wilson
  • 88,616
  • 93
  • 252
  • 370
  • See also: http://stackoverflow.com/questions/8434518/mysql-foreign-key-constraint-is-incorrectly-formed-error – JohnFx Jun 19 '12 at 02:50
  • and http://stackoverflow.com/questions/10078111/foreign-key-constraint-is-incorrectly-formed – JohnFx Jun 19 '12 at 02:53
  • http://stackoverflow.com/questions/10076921/cant-create-foreign-key-constraint (not saying these are dupes, just related) – JohnFx Jun 19 '12 at 02:53

1 Answers1

4

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 :)

Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
  • Ah yeah I knew it had to be InnoDB... not sure how I missed that. Thanks! And great site as well! I use it a lot for quick testing. Keep it up! – Jake Wilson Jun 19 '12 at 14:25