MySQL 1215错误求助：执行SQL脚本时无法添加外键约束

Let's break down why you're hitting this foreign key error and fix it step by step—there are two key issues in your current script causing the problem:

1. Table Database Misalignment #

You explicitly target test_db.users in your DROP TABLE statement, but your CREATE TABLE users doesn't specify the test_db database. This means the users table gets created in whatever database you're currently connected to, not test_db. Later, when your comments table tries to reference test_db.users(id), that table either doesn't exist in test_db or isn't the one you just created.

2. Mismatched Data Types #

In MySQL, SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. But your comments table defines userId as BIGINT(20) (which defaults to a signed integer). Foreign key constraints require the referencing column and referenced column to have exact matching data types—including whether they're signed or unsigned. This mismatch blocks the constraint from being created.

Fixed SQL Script #

Here's the corrected version that addresses both problems:

-- Switch to the target database first to ensure all tables live here
USE test_db;

-- Drop dependent table first (comments relies on users)
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
 id SERIAL,
 username VARCHAR(20) NOT NULL,
 password VARCHAR(20) NOT NULL,
 PRIMARY KEY (id)
);

CREATE TABLE comments (
 id SERIAL,
 content VARCHAR(255) NOT NULL,
 userId BIGINT UNSIGNED NOT NULL, -- Match the UNSIGNED type from users.id
 CONSTRAINT fk_comments_has_user FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE,
 PRIMARY KEY (id)
);

Key Changes Explained: #

• Added USE test_db; to guarantee all table operations happen in the correct database.
• Reordered DROP TABLE statements: We drop comments first because it has a dependency on users—dropping users first would throw an error about dependent tables.
• Updated userId to BIGINT UNSIGNED to match the data type of users.id (since SERIAL is unsigned).

Run this script, and the foreign key constraint should be created without issues.

内容的提问来源于stack exchange，提问作者Jonas Grønbek