MySQL 出现错误代码 1452，但值和表确实存在

Question

我一生都无法弄清楚我哪里搞砸了。我有使用此 SQL 代码制作的下表:

CREATE TABLE IF NOT EXISTS Users(
    username VARCHAR(50) NOT NULL PRIMARY KEY,
    pwd TEXT NOT NULL,
    first_name VARCHAR(25) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    user_type VARCHAR(25) NOT NULL,
    user_status VARCHAR(10) NOT NULL,
    email VARCHAR(255) NOT NULL
);

CREATE UNIQUE INDEX Users_Index
ON Users (username);

CREATE TABLE IF NOT EXISTS Clients(
    client_id VARCHAR(10) PRIMARY KEY,
    client_name VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(25) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    middle_name VARCHAR(25),
    related_to VARCHAR(10),
    relation_type VARCHAR(25),
    ssn_ein VARCHAR(11) NOT NULL UNIQUE,
    date_of_birth DATE NOT NULL,
    manager VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    client_status VARCHAR(15) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zip INT NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(255) NOT NULL,
    notes TEXT,
    FOREIGN KEY(manager) REFERENCES Users(username)
);

CREATE UNIQUE INDEX username_index
ON Clients(manager);

我尝试将数据插入到 Clients 表中，但出现错误 1452，这是约束检查失败时出现的错误。事情是，我已经在用户表中有数据，我三次检查了拼写错误，我向两个表添加了索引但无济于事，我检查了引用是否指向正确的表，我检查了引擎都是InnoDB 以及排序规则是准确的，用户名和经理列都是相同的数据类型。我已经检查了 MySQL 文档，并据我所知遵循了所有必要条件。有谁知道可能是什么问题吗？

这里是示例数据:

INSERT INTO Clients VALUES (
    client_id = "123456",
    client_name = "John Doe",
    first_name = "John",
    last_name = "Doe",
    middle_name = "Michael",
    related_to = NULL,
    relation_type = NULL,
    ssn_ein = "123-456-7890",
    date_of_birth = "1990-01-01",
    manager = "OzzyTheGiant",
    entity_type = "Individual",
    client_status = "Active",
    address = "123 Main St.",
    city = "Anytown",
    state = "XX",
    zip = 78550,
    email = "john.doe@example.com",
    phone = "123-555-7890",
    notes = "self-employed"
);

Answer 1

这是因为您尝试插入 Clients 表中的行/记录在 Users 表中不存在，因此出现错误。既然父表本身不存在该记录，怎么能期望插入到子表中呢？这是不可能的。

此外，您的 INSERT 查询语法错误。您的架构创建脚本应如下所示。在这里查看演示 fiddle : Demo Here

CREATE TABLE IF NOT EXISTS Users(
    username VARCHAR(50) NOT NULL PRIMARY KEY,
    pwd TEXT NOT NULL,
    first_name VARCHAR(25) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    user_type VARCHAR(25) NOT NULL,
    user_status VARCHAR(10) NOT NULL,
    email VARCHAR(255) NOT NULL
);

CREATE UNIQUE INDEX Users_Index
ON Users (username);

CREATE TABLE IF NOT EXISTS Clients(
    client_id VARCHAR(10) PRIMARY KEY,
    client_name VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(25) NOT NULL,
    last_name VARCHAR(25) NOT NULL,
    middle_name VARCHAR(25),
    related_to VARCHAR(10),
    relation_type VARCHAR(25),
    ssn_ein VARCHAR(11) NOT NULL UNIQUE,
    date_of_birth DATE NOT NULL,
    manager VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    client_status VARCHAR(15) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(255) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zip INT NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(255) NOT NULL,
    notes TEXT,
    FOREIGN KEY(manager) REFERENCES Users(username)
);

CREATE UNIQUE INDEX username_index
ON Clients(manager);

INSERT INTO `Users` VALUES('OzzyTheGiant', 'scxcxx','test1','test2','test','test','test');

INSERT INTO Clients VALUES (
'123456',
'John Doe',
'John',
'Doe',
'Michael',
NULL,
NULL,
'123-456-78',
'1990-01-01',
'OzzyTheGiant',
'Individual',
'Active',
'123 Main St.',
'Anytown',
'XX',
78550,
'john.doe@example.com',
'123-555-7890',
'self-employed'
);