1822 – Failed to add the foreign key constraint. Missing index for constraint ‘tb_emp_ibfk_1’ in the

mysql, error reported: 1822-failed to add the foreign key constraint. Missing index for constraint ‘tb_emp_ibfk_1’ in the referenced table ‘tb_dept’

reason: if table A has no primary key set, the fields in table A cannot be used as foreign keys for table B.

solution, on the premise of not deleting table A, add the following SQL text:

ALTER TABLE tb_dept ADD PRIMARY KEY(deptno);

SQL:

DROP DATABASE IF EXISTS tb_test;
CREATE DATABASE tb_test DEFAULT charset utf8;

USE tb_test;
# 部门表
CREATE TABLE tb_dept ( 
	deptno INT NOT NULL COMMENT '编号', 
	deptname VARCHAR ( 10 ) NOT NULL COMMENT '名称', 
	deptaddr VARCHAR ( 20 ) NOT NULL COMMENT '地址' 
);
INSERT INTO tb_dept VALUES
	( 101, '研发部门', '武汉' ),
	( 127, '会计部门', '上海' );
# 员工表
DROP TABLE IF EXISTS tb_emp;
CREATE TABLE tb_emp (
	empno INT NOT NULL COMMENT '编号',
	empname VARCHAR ( 10 ) NOT NULL COMMENT '姓名',
	empjob VARCHAR ( 10 ) NOT NULL COMMENT '职位',
	empsal INT NOT NULL DEFAULT 3000 COMMENT '薪酬',
	deptno INT NOT NULL COMMENT '所属部门',
	PRIMARY KEY ( empno ),
	FOREIGN KEY ( deptno ) REFERENCES tb_dept ( deptno )
);
INSERT INTO tb_emp VALUES
	(10001,'杨过','研发主管',2000000,101),
	(40001,'张三丰','总裁',90000000,127);

operation result:

DROP DATABASE IF EXISTS tb_test
> OK
> 时间: 0.32s


CREATE DATABASE tb_test DEFAULT charset utf8
> OK
> 时间: 0.991s


USE tb_test
> OK
> 时间: 0.008s


# 部门表
CREATE TABLE tb_dept ( 
	deptno INT NOT NULL COMMENT '编号', 
	deptname VARCHAR ( 10 ) NOT NULL COMMENT '名称', 
	deptaddr VARCHAR ( 20 ) NOT NULL COMMENT '地址' 
)
> OK
> 时间: 1.74s


INSERT INTO tb_dept VALUES
	( 101, '研发部门', '武汉' ),
	( 127, '会计部门', '上海' )
> Affected rows: 2
> 时间: 0.532s


# 员工表
DROP TABLE IF EXISTS tb_emp
> OK
> 时间: 0.393s


CREATE TABLE tb_emp (
	empno INT NOT NULL COMMENT '编号',
	empname VARCHAR ( 10 ) NOT NULL COMMENT '姓名',
	empjob VARCHAR ( 10 ) NOT NULL COMMENT '职位',
	empsal INT NOT NULL DEFAULT 3000 COMMENT '薪酬',
	deptno INT NOT NULL COMMENT '所属部门',
	PRIMARY KEY ( empno ),
	FOREIGN KEY ( deptno ) REFERENCES tb_dept ( deptno )
)
> 1822 - Failed to add the foreign key constraint. Missing index for constraint 'tb_emp_ibfk_1' in the referenced table 'tb_dept'
> 时间: 0.034s

Read More: