I recently ran into a situation where I needed to add a foreign key to a table. This seems easy at first but was actually a 3 step process compared to the couple of lines it takes in a create table sytax.
Short version: when creating your FK column, it helps to have the column definition of the FK match the column definition. In retrospec, this seems like a no-brainer but was the heart of my problem.
So, you want create a FK to an existing table. Lets start by creating our table.
CREATE TABLE IF NOT EXISTS Role( Id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, Name VARCHAR(255) NOT NULL UNIQUE, Description TEXT DEFAULT NULL, PRIMARY KEY(Id) );
Pretend we pooped data into it and now it’s a big pain in the ass to do a drop/create with how we want things.
First we add our column that will be the Foreign Key.
ALTER TABLE Role ADD COLUMN Parent int(11) UNSIGNED DEFAULT NULL;
Next we add an index. Why? because mysql says so and FKs rely on indexes
ALTER TABLE Role ADD INDEX Parent (Parent);
Now we add our constraint.
ALTER TABLE `Role` ADD CONSTRAINT `Parent` FOREIGN KEY(`Parent`) REFERENCES `Role`(`Id`) ON DELETE SET NULL ON UPDATE SET NULL;
For fun, do
ALTER TABLE Role ADD COLUMN Parent int(11) DEFAULT NULL;
for the first step. Everything will look awesome until you get to the the last step. You’ll get a wonderful “Errno 150” error and spend a bunch of time googling various key words trying to figure out what went wrong. Well, at least I did.