Donson Donson - 2 months ago 7
MySQL Question

A foreign key constraint fails (MySQL)

I'm getting an error that states:

Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails


Not sure why I'm getting this error. Any clue?

Code below:

-- DROP EXISTING TABLES --

DROP TABLE IF EXISTS `category`;
DROP TABLE IF EXISTS `format`;
DROP TABLE IF EXISTS `customer`;
DROP TABLE IF EXISTS `rentals`;
DROP TABLE IF EXISTS `videos`;

-- CREATE CATEGORY TABLE --
CREATE TABLE `category` (
`category_id` int(10) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- CREATE FORMAT TABLE --
CREATE TABLE `format` (
`format_id` int(10) NOT NULL,
PRIMARY KEY (`format_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- CRTEATE VIDEOS TABLE --
CREATE TABLE `videos` (
`video_id` int(10) NOT NULL,
`title` varchar(50) NOT NULL,
`format_id` int(10) NOT NULL,
`cost` double NOT NULL,
`category_id` int(10) NOT NULL,
PRIMARY KEY (`video_id`),
CONSTRAINT `fk_video_format` FOREIGN KEY (`format_id`) REFERENCES `format` (`format_id`),
CONSTRAINT `fk_video_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- CREATE CUSTOMER TABLE --
CREATE TABLE `customer` (
`customer_id` int(10) NOT NULL,
`last_name` varchar(50) NOT NULL,
`first_name` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`city` varchar(50) NOT NULL,
`state` varchar(10) NOT NULL,
`zip` int(10) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

-- CREATE RENTALS TABLE --
CREATE TABLE `rentals` (
`rental_id` int(10) NOT NULL,
`date_out` datetime NOT NULL,
`date_due` datetime NOT NULL,
`date_in` datetime NOT NULL,
`delivery_status` varchar(50),
`cost` double NOT NULL,
`late_fee` double NOT NULL,
`customer_id` int(10) NOT NULL,
`video_id` int(10) NOT NULL,
PRIMARY KEY (`rental_id`),
CONSTRAINT `fk_video_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`),
CONSTRAINT `fk_video_cost` FOREIGN KEY (`cost`) REFERENCES `videos` (`cost`),
CONSTRAINT `fk_video_id` FOREIGN KEY (`video_id`) REFERENCES `videos` (`video_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;


I believe everything matches up key and foreign key-wise. Formatting looks correct, too. Unless I am doing something totally wrong here.

Answer

Ignoring your cost issue described in comments above, the 1217 error occurs very commonly because when you drop, you have to do it in the reserve order of the creates to do it safely.

Supposing you create tables A,B,C,D successfully in succession with FK's in place, you need to drop them in order of D,C,B,A.

This is simply because a parent cannot be dropped if a child has an FK in-force into it. And by dropping the chilren first, the error won't surface.