How to add Foreign Key (MySQL)

问题:

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

回答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);