Kyle Niemiec Kyle Niemiec - 12 days ago 5
MySQL Question

MySQL: Why is Foreign Key failing?

Trying to insert a row into MySQL using phpMyAdmin, and it is failing my with error:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`staging`.`user_profiles`, CONSTRAINT `fk_user_profiles_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)


Can't figure out why. I created the database design in MySQL Workbench and it was working on MySQL 5.7 in development and now won't work with Percona server 5.5. Where am I NOT looking??

SQL Statement and Error

Foriegn key in phpMyAdmin

This is the table structure I created in MySQL Workbench (dashes for obscuring client info =P):

DROP TABLE IF EXISTS `staging_-----------`.`users` ;

CREATE TABLE IF NOT EXISTS `staging_-----------`.`users` (
`id` INT NOT NULL AUTO_INCREMENT,
`email` VARCHAR(255) NOT NULL,
`password` VARCHAR(128) NOT NULL,
`role` ENUM('admin', 'vet', 'client') NOT NULL,
PRIMARY KEY (`id`));


DROP TABLE IF EXISTS `staging_-----------`.`user_profiles` ;

CREATE TABLE IF NOT EXISTS `staging_-----------`.`user_profiles` (
`user_id` INT NOT NULL,
`address_one` VARCHAR(255) NULL,
`address_two` VARCHAR(255) NULL,
`age` INT NULL,
`sex` ENUM('m', 'f') NULL,
`first_name` VARCHAR(45) NULL,
`last_name` VARCHAR(45) NULL,
`city` VARCHAR(45) NULL,
`state` VARCHAR(45) NULL,
`zip` VARCHAR(6) NULL,
`phone` VARCHAR(45) NULL,
`photo` VARCHAR(255) NULL,
PRIMARY KEY (`user_id`),
INDEX `fk_user_profiles_users2_idx` (`user_id` ASC),
CONSTRAINT `fk_user_profiles_users`
FOREIGN KEY (`user_id`)
REFERENCES `staging_-----------`.`users` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;


I've been trying to get my working code working again for 2 days now, and no love. What could have gone wrong? Thank you so much for your time.

Answer

Check if the value you are inserting exists in user_id exists in staging_-----------.users

Maybe this is the problem ....

Comments