Let's break down why your rating table creation is failing and fix it step by step, tailored for your Spring REST app's one-to-many relationship needs:

Core Issues Causing the Error #

There are two key conflicts in your current schema:

• The referenced column media.imdb_id lacks a unique constraint: MySQL requires foreign keys to reference either a primary key or a column with a UNIQUE constraint. Right now, media uses id as its primary key, and imdb_id only has a NOT NULL rule—nothing prevents duplicate IMDB IDs from being inserted, which breaks foreign key logic (since a child rating record can't reliably link to one parent media entry).
• ON DELETE SET NULL clashes with rating.imdb_id NOT NULL: You’ve configured the foreign key to set rating.imdb_id to NULL when its parent media record is deleted, but the rating table’s imdb_id is marked as non-nullable. These two settings can’t coexist.

Corrected SQL Schema #

Here’s the fixed version of your SQL, with clear explanations of the changes:

CREATE DATABASE IF NOT EXISTS `imdb`; 
USE `imdb`; 

DROP TABLE IF EXISTS `rating`; 
DROP TABLE IF EXISTS `media`; 

CREATE TABLE `media` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `title` varchar(70) DEFAULT NULL, 
    `year` varchar(70) DEFAULT NULL, 
    `rated` varchar(70) DEFAULT NULL, 
    `released` varchar(70) DEFAULT NULL, 
    `runtime` varchar(70) DEFAULT NULL, 
    `genre` varchar(70) DEFAULT NULL, 
    `director` varchar(70) DEFAULT NULL, 
    `writer` varchar(70) DEFAULT NULL, 
    `actors` varchar(70) DEFAULT NULL, 
    `plot` varchar(7000) DEFAULT NULL, 
    `language` varchar(70) DEFAULT NULL, 
    `country` varchar(70) DEFAULT NULL, 
    `awards` varchar(70) DEFAULT NULL, 
    `poster` varchar(270) DEFAULT NULL, 
    `metascore` varchar(70) DEFAULT NULL, 
    `imdb_rating` varchar(70) DEFAULT NULL, 
    `imdb_votes` varchar(70) DEFAULT NULL, 
    `imdb_id` varchar(70) NOT NULL UNIQUE, -- Added UNIQUE constraint to enforce unique IMDB IDs
    `type` varchar(70) DEFAULT NULL, 
    `dvd` varchar(70) DEFAULT NULL, 
    `box_office` varchar(70) DEFAULT NULL, 
    `production` varchar(70) DEFAULT NULL, 
    `website` varchar(70) DEFAULT NULL, 
    `response` varchar(70) DEFAULT NULL, 
    PRIMARY KEY (`id`) 
); 

CREATE TABLE `rating` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `source` varchar(70) DEFAULT NULL, 
    `value` varchar(70) DEFAULT NULL, 
    `imdb_id` varchar(70) NOT NULL, 
    PRIMARY KEY (`id`), 
    -- Switched to ON DELETE CASCADE to delete child ratings when parent media is removed
    FOREIGN KEY (`imdb_id`) REFERENCES `media`(`imdb_id`) ON DELETE CASCADE 
); 

SET FOREIGN_KEY_CHECKS = 1; 

Key Changes Explained #

1. Added UNIQUE to media.imdb_id: This ensures each IMDB ID is unique in the media table (which aligns with real-world IMDB ID behavior) and satisfies MySQL’s foreign key requirements.
2. Changed ON DELETE SET NULL to ON DELETE CASCADE: Since rating.imdb_id is non-nullable, we can’t set it to NULL when the parent is deleted. Instead, CASCADE automatically deletes all related rating records when their parent media entry is removed—this is a standard pattern for one-to-many relationships where child records shouldn’t exist without their parent.

Alternative Option (If You Prefer Retaining ON DELETE SET NULL) #

If you’d rather keep ratings even after their parent media is deleted, modify the rating table’s imdb_id to allow null values:

CREATE TABLE `rating` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `source` varchar(70) DEFAULT NULL, 
    `value` varchar(70) DEFAULT NULL, 
    `imdb_id` varchar(70) DEFAULT NULL, -- Removed NOT NULL constraint
    PRIMARY KEY (`id`), 
    FOREIGN KEY (`imdb_id`) REFERENCES `media`(`imdb_id`) ON DELETE SET NULL 
); 

This way, deleting a media record will set the imdb_id of its related rating records to NULL instead of deleting them. Pick whichever behavior fits your app’s business logic.

Run the corrected SQL, and your foreign key constraint will be created successfully—your one-to-many media → rating relationship will work as expected for your IMDB-like local database.

内容的提问来源于stack exchange，提问作者Anirudh Kanabar