How to add Foreign Key (MySQL)

Question

I'm extremely new to SQL and I'm trying to perform a simple ALTER TABLE to create a new column and use it as a foreign key to reference another simple table in my database. I've altered both tables to be InnoDB

However, when executing the ALTER TABLE code, I get the following error:

Error   1452    Cannot add or update a child row:  a foreign key constraint fails (`toys`.<result 2 when  explaining filename '#sql-6d4_6'>, CONSTRAINT  `#sql-6d4_6_ibfk_1` FOREIGN KEY (`toy_id`) REFERENCES `toys` (`toy_id`))     

Below are the DESC of both tables:

Table 1:

FIELD       TYPE     NULL   KEY     EXTRA toy_id      int(11)  NO     PRI     auto_increment toy varchar(50) YES          

Table 2:

FIELD       TYPE        NULL   KEY     EXTRA boy_id      int(11)     NO     PRI      auto_increment boy         varchar(50) YES  

And this is the ALTER query I was trying to perform:

    ALTER TABLE boys     ADD COLUMN toy_id INT NOT NULL,     ADD CONSTRAINT toys_toy_id_fk     FOREIGN KEY(toy_id)     REFERENCES toys(toy_id); 

I've looked all over trying to figure it out, but with no luck. Thanks in advance, and please be kind to this newbie :)

EDIT:

Here are the SHOW CREATE TABLE for both tables:

TABLE 1:

    CREATE TABLE `toys` (       `toy_id` int(11) NOT NULL AUTO_INCREMENT,       `toy` varchar(50) DEFAULT NULL,       PRIMARY KEY (`toy_id`)     ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 

TABLE 2:

    CREATE TABLE `boys` (       `boy_id` int(11) NOT NULL AUTO_INCREMENT,       `boy` varchar(50) DEFAULT NULL,       PRIMARY KEY (`boy_id`)     ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 

Answer 1

You can't add a NOT NULL column to a table that has more than zero rows, when the column is constrained to values that match those in the parent table, and yet has only NULL values because it's a new, unpopulated column with no DEFAULT.

The workaround is to do it in stages: add the column, but don't declare it NOT NULL, and don't declare the foreign key yet.

ALTER TABLE boys  ADD COLUMN toy_id INT; 

Then populate it with valid data that matches some value(s) in your toys table.

UPDATE boys SET toy_id = ...; 

Then alter the column to be NOT NULL, and create the constraint:

ALTER TABLE boys MODIFY COLUMN toy_id INT NOT NULL,  ADD CONSTRAINT toys_toy_id_fk  FOREIGN KEY(toy_id)  REFERENCES toys(toy_id);