外键约束

 

代码：

create table dept(
id int primary key auto_increment comment 'ID',
name varchar(50) not null comment '姓名'
) comment '部门表';
insert into dept(id,name) values (1,'研发部'),
(2,'市场部'),
(3,'财务部'),
(4,'销售部'),
(5,'总部');

create table emp(
id int primary key auto_increment comment 'ID',
name varchar(50) not null comment '姓名',
age int check ( age>0 && age<120 ) comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导ID',
dept_id int comment '部门id'
) comment '员工表';
insert into emp(name,age,job,salary,entrydate,managerid,dept_id) values ('111',25,'总裁',20000,'2000-01-01',null,5),
('222',22,'项目经理',12000,'2004-01-01',1,1),
('333',21,'开发',9000,'2005-01-01',2,1),
('444',20,'开发',8000,'2006-01-01',2,1),
('555',19,'开发',7000,'2007-01-01',3,1),
('666',17,'程序员新手',5000,'2009-01-01',2,1);
select * from emp;
alter table emp add constraint emp_dept_id foreign key (dept_id) references dept(id);/*添加外键 */
alter table emp drop foreign key emp_dept_id;/*删除外键 */