Saturday, March 20, 2021

[SOLUTION] Cannot Create Foreign Key

Been a long while since I last needed to have Foreign Key in my Database.

Much of my applications are not so complex that I don't bother employ a Foreign Key.

Now that I have to, it took me about three whole hours figuring out the problem.

I cannot create Foreign Key with HeidiSQL!

I've been using HeidiSQL for over ten years now.  It's only now that I faced problem with this MySQL GUI.

My latest Wamp Server uses MariaDB.

Thinking it could be MariaDB having a problem and not HeidiSQL, I tried using MySQL instead.

Still, with MySQL, I could not successfully create Foreign Key!  And I've been pulling my hairs off!

Somehow, this problem has been discussed over at HeidiSQL forums a year ago.  Even the author of HeidiSQL couldn't figure out why because the problem isn't happening on his side.

Luckily, a user named, Patsy, figured out why.

For some reason, my Wamp Server used MyISAM as default Table Engine instead of the more common, InnoDB.

So, in the end, it wasn't a bug in HeidiSQL.  Neither a bug in MariaDB nor MySQL.

It was a compatibility problem with MyISAM not being able to support Foreign Key

Hope this blog post will save you hours of hair pulling.

Happy hacking!

~creek23

No comments: