错误代码：1822。无法添加外键约束。引用的表"集合"中缺少约束"employee_ibfk_1"的索引

Question

代码：

CREATE DATABASE IF NOT EXISTS Company;
-- Creating Company Schema
USE Company;
CREATE TABLE IF NOT EXISTS Collection 
(
emp_id int not null,
emp_name varchar(60), 
numA int ,
tcol int , 
amt  int ,
acol int,
PRIMARY KEY (emp_id),
UNIQUE (amt),
UNIQUE(emp_name)
);
CREATE TABLE IF NOT EXISTS Employee 
(
employee_name varchar(60) , 
FOREIGN KEY (employee_name) REFERENCES Collection(emp_name),
amount int , 
FOREIGN KEY (amount) REFERENCES Collection(amt),
PRIMARY KEY(employee_name)
);
INSERT INTO Employee VALUES ('Bob','200');
-- Insert all records 
INSERT INTO Collection VALUES (1,'Bob',1000,10000, 500, tcol - amt );
-- Select Statements to validate all the tables were created properly
SELECT * FROM Collection;
SELECT * FROM Employee;

错误：

错误代码：1822。无法添加外键约束。引用的表"集合"中缺少约束"employee_ibfk_1"的索引

Answer 1

你说的有缺陷。外来约束意味着仅当数据存在于其他表中时，才能添加新行

例如

INSERT INTO Employee VALUES ('Bob','200');

意味着在插入名为 Bob 且 amt 为 200 的用户之前，必须在集合中存在，这不是真的，因为(切换位置后，只有一个 amt 机智为 500

因此，必须删除金额的引用，并可能替换为触发器，以检查某些东西或其他内容。

试着向某人解释你的想法，看看他是否能吸引你

CREATE TABLE IF NOT EXISTS Collection 
(
emp_id int not null,
emp_name varchar(60), 
numA int ,
tcol int , 
amt  int ,
acol int,
PRIMARY KEY (emp_id),
UNIQUE (amt),
UNIQUE(emp_name)
);
CREATE TABLE IF NOT EXISTS Employee 
(
employee_name varchar(60) , 
FOREIGN KEY (employee_name) REFERENCES Collection(emp_name),
amount int , 
FOREIGN KEY (amount) REFERENCES Collection(amt),
PRIMARY KEY(employee_name)
);
-- Insert all records 
INSERT INTO Collection VALUES (1,'Bob',1000,10000, 500, tcol - amt );


-- Select Statements to validate all the tables were created properly

INSERT INTO Employee VALUES ('Bob','500');

SELECT * FROM Collection;
SELECT * FROM Employee;

emp_id | emp_name | 数字 | 特科尔 |AMT |阿科尔 -----: |:------- |---: |----: |--: |---:  1 |鲍勃 |1000 |10000 |500 |9500 employee_name |量 :------------ |-----: 鲍勃 |   500

db<>在这里小提琴