17 SQL——外键约束操作_sql数据库添加外键约束代码

1
create table  depart(
    id          int                        auto_increment    primary key      comment 'ID',
    name         varchar(50)               not null                           comment '部门名'
)comment '部门';



	insert into depart(id, name)values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经部');




2
create  table  personal(
 id             int                        auto_increment      primary key                comment 'ID',
 name           varchar (50)                not null                                      comment '姓名',
 age            int                                                                       comment '年龄',
job             varchar(20)                                                               comment '职位',
salary            int                                                                     comment '薪资',
woketime           date                                                                    comment'入职时间',
lender             int                                                                     comment '直属领导ID',
 depart_id          int                                                                    comment '部门ID'

)comment '员工表';



	insert  into personal(id, name, age, job, salary, woketime, lender, depart_id) values (1,'天盗',66,'总裁',20000,'2000-1-1',null,5),
                                                                                       (2,'天盗时',20,'项目经理',12000,'2002-12-1',1,1),
                                                                                       (3,'天盗第',33,'开发',15000,'2021-3-23',2,1),
                                                                                       (4,'天盗岁',48,'开发',16000,'2004-11-15',2,1),
                                                                                       (5,'天盗大',43,'开发',2600,'2002-10-12',3,1),
                                                                                       (6,'天盗就',19,'程序员鼓励师',13000,'2008-3-1',2,1);


3 -- 添加外键
alter  table  personal add constraint fk_personal_depart_id foreign key (depart_id)references depart(id);


4 -- 删除外键
alter table  personal drop foreign key fk_personal_depart_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

添加外键后depart_id字段状态（外键是蓝色的）

删除外键后depart_id字段状态