CODEWITHSUNDEEP
mysqlforeign-keyscascade
Jon C.
Jon C.

Reputation: 374

MySQL FK Cascade - not seeing my mistake

I have read through countless threads on this and am still missing something. When I delete a row from either table no constraint or cascade occurs.

My goal is to cascade delete any child rows.

CREATE SCHEMA IF NOT EXISTS `my_schema`
  DEFAULT CHARACTER SET latin1;
USE `my_schema`;

SET foreign_key_checks = 0;

DROP TABLE IF EXISTS `test_types`;
DROP TABLE IF EXISTS `test_core_types`;

SET foreign_key_checks = 1;

-- -----------------------------------------------------
-- Table `test_core_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_core_types` (
  `test_core_type_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `data`                 VARCHAR(100)     NOT NULL,
  PRIMARY KEY (`test_core_type_id`))
  ENGINE = InnoDB
  AUTO_INCREMENT = 1;

CREATE UNIQUE INDEX `test_core_types__data_UNIQUE` ON `test_core_types` (`data` ASC);

-- -----------------------------------------------------
-- Table `test_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_types` (
  `test_type_id`      SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `test_core_type_id` INT(10) UNSIGNED     NOT NULL,
  `name`                 VARCHAR(45)          NOT NULL,
  `is_viewable`          TINYINT(1) UNSIGNED  NOT NULL DEFAULT '1',
  PRIMARY KEY (`test_type_id`))
  ENGINE = InnoDB
  AUTO_INCREMENT = 1;

CREATE UNIQUE INDEX `test_types__name_UNIQUE` ON `test_types` (`name` ASC);
CREATE INDEX `idx_test_core_type_00` ON `test_types` (`test_core_type_id` ASC);

ALTER TABLE `test_types`
ADD CONSTRAINT `fk_test_core_type_00`
FOREIGN KEY (`test_core_type_id`)
REFERENCES `test_core_types` (`test_core_type_id`)
  ON DELETE CASCADE
  ON UPDATE NO ACTION;

Insert:

INSERT INTO `test_core_types` (`test_core_type_id`, `data`) VALUES ( 1, 'General');
INSERT INTO `test_types` (`test_type_id`, `test_core_type_id`, `name`, `is_viewable`) VALUES ( 1, 1, 'My General Item', 1);

Delete:

DELETE FROM `test_core_types` WHERE `test_core_type_id` = 1;

Result:

SELECT * FROM `test_core_types`;
/* --> Row Deleted */

SELECT * FROM `test_types`;
/* --> Row still exists */

When I perform a delete the row is deleted without errors. Does not matter which table.

Any help is greatly appreciated.

Upvotes: 0

Views: 38

Answers (2)

spencer7593
spencer7593

Reputation: 108400

You've specified a DELETE rule of CASCADE.

We'd expect a delete from the parent table would succeed, related rows in the table with the foreign key reference will also be deleted. We wouldn't expect any error.

If you want the DELETE operation in your code to throw an error, then define the foreign key reference with a DELETE rule of RESTRICT.

I recommend you verify that FOREIGN_KEY_CHECKS is enabled, and that the foreign key constraint is actually defined (i.e. that the ALTER TABLE statement that added the constraint succeeded. I've never tried specifying NO ACTION as an update rule; I know that's the default when no rule is specified, but I always specify either RESTRICT, CASCADE or SET NULL.) Also verify that the table is actually using the InnoDB storage engine.

SHOW VARIABLES LIKE '%foreign_key_checks%' ;
SHOW CREATE TABLE `test_types` ;

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Theory:

For your child table (test_types) you have mentioned ON DELETE CASCADE; which means when you delete a row from parent table (test_core_types); same delete will get cascaded to child table (S) as well and corresponding related row from child table will be deleted.

After your delete query; if you try a select from both table you will find 0 rows present. That's the actual behavior of ON DELETE CASCADE.

For Real example check the below mentioned fiddle link

http://sqlfiddle.com/#!2/33014e/1

Upvotes: 1

Related Questions