SQL Error 1822：外键约束添加失败问题求助

错误原因 #

Error 1822的核心问题是：你在users表中创建的外键引用了city表中没有索引的列。MySQL要求外键关联的被引用列必须是主键，或者至少存在对应的索引（唯一索引或普通索引），否则无法高效验证外键约束的合法性。

除此之外，你的外键设计存在逻辑缺陷：单独将city_name、state、zip、country作为外键关联是不合理的——同一个城市名可能存在于不同国家/州，单独引用这些字段无法保证数据的一致性，还会造成数据冗余。

正确解决方案（推荐） #

遵循数据库设计范式，通过city表的主键city_id来关联users表，这样既满足外键约束的索引要求，又能保证数据一致性。

修正后的建表语句 #

CREATE TABLE city(
    city_id INT NOT NULL,
    city_name VARCHAR(50) NOT NULL,
    state VARCHAR(20),
    zip CHAR(10) NOT NULL,
    country VARCHAR(60) NOT NULL,
    PRIMARY KEY (city_id),
    -- 可选：添加联合唯一索引，避免重复插入相同城市信息
    UNIQUE KEY unique_city_info (city_name, state, country, zip)
);

CREATE TABLE users(
    user_id INT NOT NULL,
    first_name VARCHAR(25) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    city_id INT, -- 用city_id关联城市信息，替代原有的多个字段
    phone VARCHAR(12),
    email VARCHAR(30) NOT NULL,
    user_password VARCHAR(25) NOT NULL,
    PRIMARY KEY (user_id),
    FOREIGN KEY(city_id) REFERENCES city(city_id)
);

方案说明 #

1. 利用city_id（city表的主键，默认自带索引）作为外键，直接满足MySQL的外键约束要求，不会再触发Error 1822。
2. 避免数据冗余：users表无需重复存储城市名称、州、邮编等信息，查询时通过city_id关联city表即可获取完整城市数据。
3. 可选的联合唯一索引：防止city表中插入完全重复的城市信息，进一步保证数据准确性。

临时解决方案（不推荐） #

如果因作业要求必须保留users表中的city、state等字段，需为city表中被引用的列分别创建索引：

CREATE TABLE city(
    city_id INT NOT NULL,
    city_name VARCHAR(50) NOT NULL,
    state VARCHAR(20),
    zip CHAR(10) NOT NULL,
    country VARCHAR(60) NOT NULL,
    PRIMARY KEY (city_id),
    INDEX idx_city_name (city_name),
    INDEX idx_state (state),
    INDEX idx_zip (zip),
    INDEX idx_country (country)
);

-- users表结构保持不变
CREATE TABLE users(
    user_id INT NOT NULL,
    first_name VARCHAR(25) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    city VARCHAR(50),
    state VARCHAR(20),
    zip_code CHAR(10),
    country VARCHAR(60),
    phone VARCHAR(12),
    email VARCHAR(30) NOT NULL,
    user_password VARCHAR(25) NOT NULL,
    PRIMARY KEY (user_id),
    FOREIGN KEY(city) REFERENCES city(city_name),
    FOREIGN KEY(state) REFERENCES city(state),
    FOREIGN KEY(zip_code) REFERENCES city(zip),
    FOREIGN KEY(country) REFERENCES city(country)
);

注意事项 #

这种方式虽然能解决Error 1822，但存在严重的逻辑漏洞：比如users表中city为"New York"、state为"California"时，外键约束无法验证这种组合是否存在于city表中，会导致数据不一致，因此强烈推荐使用第一种方案。

内容的提问来源于stack exchange，提问作者Anand Sundaram