Mysql – Another Foreign Key Issue – #1451 – Cannot delete or update a parent row: a foreign key constraint fails

When doing this in MySQL:

UPDATE `client_therapist` SET `therapist_id` = 3 LIMIT 1 ;

I get the following error:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test_structure`.`client_group`, CONSTRAINT `client_group_therapist_id` FOREIGN KEY (`therapist_id`) REFERENCES `client_therapist` (`therapist_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

I've searched all over and have read several of the foreign key questions asked here on StackOverflow… to no avail. I don't think I'm completely understanding foreign keys at this point.

Here is my DB structure:
Database structure diagram showing foreign keys

The foreign key that is causing issues is on the client_group table. How I understand it is it's saying "whenever a row is added, the therapist_id must match a therapist_id in the client_therapist table."

Just typing this out made me realize part of the issue is that when a client_group is added, it might be getting bound to a certain row in the client_therapist table. So then if I try to update that client_therapist row, it freaks out. So I guess my question is – how do I get around this? I need to update the therapist_id on a certain row in the client_therapist table, but it seems I can't as long as there is a row in the client_group table.

Here are my tables with some dummy info:

CREATE SCHEMA IF NOT EXISTS `test_structure` DEFAULT CHARACTER SET utf8 ;
USE `test_structure` ;

-- -----------------------------------------------------
-- Table `test_structure`.`user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_structure`.`user` (
  `user_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `email` VARCHAR(80) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  `password` VARCHAR(40) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL,
  `display_name` VARCHAR(60) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `first_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  `last_name` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  `street_address` VARCHAR(120) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `city` VARCHAR(45) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `state` CHAR(2) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `zip` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `work_phone` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `cell_phone` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `home_phone` VARCHAR(20) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `date_created` TIMESTAMP NULL DEFAULT NULL ,
  `expired` TINYINT(1) NOT NULL DEFAULT '0' ,
  `date_last_login` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  `login_count` INT(10) UNSIGNED NULL DEFAULT '0' ,
  PRIMARY KEY (`user_id`) ,
  UNIQUE INDEX `email` (`email` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci, 
COMMENT = 'Users table.  All Therapists, Clients, Admins and others wil' ;


-- -----------------------------------------------------
-- Table `test_structure`.`client_therapist`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_structure`.`client_therapist` (
  `client_therapist_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `client_id` INT(10) UNSIGNED NOT NULL ,
  `therapist_id` INT(10) UNSIGNED NOT NULL ,
  `start_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `end_date` TIMESTAMP NULL DEFAULT NULL ,
  `therapist_approval` TINYINT(1) NOT NULL DEFAULT '0' ,
  `date_client_agreed_to_terms` TIMESTAMP NULL DEFAULT NULL ,
  PRIMARY KEY (`client_therapist_id`) ,
  INDEX `client_therapist_client_id` (`client_id` ASC) ,
  INDEX `client_therapist_therapist_id` (`therapist_id` ASC) ,
  CONSTRAINT `client_therapist_client_id`
    FOREIGN KEY (`client_id` )
    REFERENCES `test_structure`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `client_therapist_therapist_id`
    FOREIGN KEY (`therapist_id` )
    REFERENCES `test_structure`.`user` (`user_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci, 
COMMENT = 'This defines the relationship between a client and therapist' ;



-- -----------------------------------------------------
-- Table `test_structure`.`client_group`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_structure`.`client_group` (
  `client_group_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `therapist_id` INT(10) UNSIGNED NOT NULL ,
  `title` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  `description` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NULL DEFAULT NULL ,
  `date_added` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`client_group_id`) ,
  INDEX `client_group_therapist_id` (`therapist_id` ASC) ,
  CONSTRAINT `client_group_therapist_id`
    FOREIGN KEY (`therapist_id` )
    REFERENCES `test_structure`.`client_therapist` (`therapist_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `test_structure`.`client_group_member`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_structure`.`client_group_member` (
  `client_group_member_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `client_group_id` INT(10) UNSIGNED NOT NULL ,
  `client_id` INT(10) UNSIGNED NOT NULL ,
  PRIMARY KEY (`client_group_member_id`) ,
  INDEX `client_group_id` (`client_group_id` ASC) ,
  INDEX `client_group_member_id` (`client_id` ASC) ,
  CONSTRAINT `client_group_id`
    FOREIGN KEY (`client_group_id` )
    REFERENCES `test_structure`.`client_group` (`client_group_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `client_group_member_id`
    FOREIGN KEY (`client_id` )
    REFERENCES `test_structure`.`client_therapist` (`client_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- INSERT TEST DATA
-- -----------------------------------------------------
INSERT INTO `test_structure`.`user` (`user_id`, `email`, `password`, `display_name`, `first_name`, `last_name`, `street_address`, `city`, `state`, `zip`, `work_phone`, `cell_phone`, `home_phone`, `date_created`, `expired`, `date_last_login`, `login_count`) VALUES (NULL, 'dr@dr.com', SHA1('12345'), 'Dr. Test', 'Dr.', 'Test', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), '0', CURRENT_TIMESTAMP, '0');

SET @therapist_id = LAST_INSERT_ID();

INSERT INTO `test_structure`.`user` (`user_id`, `email`, `password`, `display_name`, `first_name`, `last_name`, `street_address`, `city`, `state`, `zip`, `work_phone`, `cell_phone`, `home_phone`, `date_created`, `expired`, `date_last_login`, `login_count`) VALUES (NULL, 'client@client.com', '12345', 'Client', 'Client', 'Client', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), '0', CURRENT_TIMESTAMP, '0');

SET @client_id = LAST_INSERT_ID();

INSERT INTO `test_structure`.`client_therapist` (`client_therapist_id`, `client_id`, `therapist_id`, `start_date`, `end_date`, `therapist_approval`, `date_client_agreed_to_terms`) VALUES (NULL, @client_id, @therapist_id, CURRENT_TIMESTAMP, NOW(), '0', NOW());

INSERT INTO `test_structure`.`client_group` (`client_group_id`, `therapist_id`, `title`, `description`, `date_added`) VALUES (NULL, @therapist_id, 'Test', NULL, CURRENT_TIMESTAMP);

SET @group_id = LAST_INSERT_ID();

INSERT INTO `test_structure`.`client_group_member` (`client_group_member_id`, `client_group_id`, `client_id`) VALUES (NULL, @group_id, @client_id);

And then run this code to see the foreign_key problem:

INSERT INTO `test_structure`.`user` (`user_id`, `email`, `password`, `display_name`, `first_name`, `last_name`, `street_address`, `city`, `state`, `zip`, `work_phone`, `cell_phone`, `home_phone`, `date_created`, `expired`, `date_last_login`, `login_count`) VALUES (NULL, 'newdr@newdr.com', SHA1('12345'), 'New Dr', 'Dr.', 'New', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NOW(), '0', CURRENT_TIMESTAMP, '0');

SET @new_therapist_id = LAST_INSERT_ID();

UPDATE `test_structure`.`client_therapist` SET `therapist_id` = @new_therapist_id LIMIT 1;

All help/instruction/guidance is much appreciated!

UPDATE

The issue I'm having is because the therapist_id in the client_group table is bound to the therapist_id in the client_therapist table and not the user table. That therapist_id can exist multiple times in the client_therapist and the client_group tables, but only once in the user table, so I can never update that column in the client_therapist table as long as there is a single row in the client_group table still referencing that therapist_id.

What I should have done is bound the therapist_id to the user table and not the client_therapist table since it is better to keep foreign keys referencing the primary IDs on each table (is that true?).

Now I've gotta learn how to remove/edit/update foreign keys on a table… 🙂

Best Answer

You need a foreign key declared with the ON UPDATE CASCADE option.

Here's an example of how to remove and add a foreign key on a table:

ALTER TABLE client_group drop FOREIGN KEY `client_group_therapist_id`, 
  ADD FOREIGN KEY `client_group_therapist_id` (`therapist_id`) 
  REFERENCES `client_therapist` (`therapist_id`) ON DELETE NO ACTION ON UPDATE CASCADE;

Then if you change the value in client_therapist.therapist_id, it will automatically update the value in client_group.therapist_id.

This change will be simultaneous and atomic -- that is, no other query running concurrently will be able to see the data changed in one table and not changed in the other table.

Best Answer

Related Solutions

Mysql – Add Foreign Key to existing table

MySQL Cannot Add Foreign Key Constraint

Related Topic