阿里Java学习路线：阶段 2：数据库开发－SQL进阶及查询练习：课时8：外键约束

外键约束

# 新建表格，设置主键：
CREATE TABLE dept1(
	deptno INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(50)
);

INSERT INTO dept1 VALUES(10,'研发部');
INSERT INTO dept1 VALUES(20,'人力部');
INSERT INTO dept1 VALUES(30,'财务部');

SELECT * FROM dept1;

DROP TABLE emp1;

# 新建表，设置主键和外键约束：
CREATE TABLE emp1(
	empno INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(50),
	dno INT,
	CONSTRAINT fk_emp1_dept1 FOREIGN KEY(dno) REFERENCES dept1(deptno)
);

SELECT * FROM emp1;
DESC emp1;

INSERT INTO emp1(empno,ename) VALUES(NULL,'zhanSan');
INSERT INTO emp1(empno,ename,dno) VALUES(NULL,'liSi',10);
INSERT INTO emp1(empno,ename,dno) VALUES(NULL,'wangWu',10);

INSERT INTO emp1(empno,ename,dno) VALUES(NULL,'zhaoLiu',80);
# 因为外键约束，dept1表没有80部门，所以报错
#[SQL]INSERT INTO emp1(empno,ename,dno) VALUES(NULL,'zhaoLiu',80);
#[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails
#(`mydb1`.`emp1`, CONSTRAINT `fk_emp1_dept1` FOREIGN KEY (`dno`) REFERENCES `dept1` (`deptno`))

# 如果新建的表没有外键约束，可使用以下语句进行设置
ALTER TABLE emp1 ADD CONSTRAINT fk_emp1_dept1 FOREIGN KEY(dno) REFERENCES dept1(deptno);