CODEWITHSUNDEEP
mysqlforeign-keys
Will
Will

Reputation: 3585

Attempting to add a foreign key to a table fails?

I have two tables - 

CREATE TABLE `FOO` (
    `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    /*Nothing to see here*/
    PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=380 DEFAULT CHARSET=latin1;

CREATE TABLE `BAR` (
    `ID` int(11) unsigned NOT NULL,
    `UserID` int(11) unsigned DEFAULT NULL,
    PRIMARY KEY (`ID`),
    KEY `VSK_UserID_Index` (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I am attempting create a foreign key constraint on BAR.UserID referencing users.user_id - 

ALTER TABLE `FOO`.`BAR` 
    ADD CONSTRAINT `BAR_UserID_FKey`
    FOREIGN KEY (`UserID`)
    REFERENCES `FOO`.`users` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

I keep getting this error -

Operation failed: There was an error while applying the SQL script to the database. Error 1452: Cannot add or update a child row: a foreign key constraint fails

Both of these tables have data in them - could this be the reason why this is happening, or is there something wrong with how the tables are being created?

Is there something I need to alter on one of these tables to make this work?

Upvotes: 0

Views: 58

Answers (1)

Tordek
Tordek

Reputation: 10882

The data already in one of the tables (in particular, `FOO`.`BAR`, since that is the one you're adding a constraint to) is not consistent with the data in `FOO`.`users` (`user_id`).

You must ensure that the values un the `FOO`.`BAR`.`UserID` column all exist in `FOO`.`users` (`user_id`). There may be null values or other values that do not exist in the other column.

Upvotes: 1

Related Questions