MySQL-约束语法_mysql外键约束怎么写

约束：

作用：对表中的数据进行限定，保证数据的正确性、有效性、完整性！

主键、唯一、非空约束：

特点：

• 主键约束包含：非空和唯一两个功能
• 一张表只能有一个列作为主键
• 主键一般用于表中数据的唯一标识
• 如果删除某一行数据再添加一行，那id就会再删除的id的基础上加1

演示：

CREATE TABLE Student(
  id INT PRIMARY KEY, -- 主键约束
  NAME VARCHAR(20) UNIQUE, -- 添加了唯一约束后，姓名不能重复
  age TINYINT NOT NULL
);

-- 添加数据
INSERT INTO Student VALUES(NULL,'韩信',99); -- 报错：非自增字段，不能为NULL值

CREATE TABLE Student2(
  id     INT PRIMARY KEY AUTO_Increment COMMENT '主键',
  NAME VARCHAR(20) UNIQUE, -- 添加了唯一约束后，姓名不能重复
  age TINYINT NOT NULL
);

-- 添加数据
INSERT INTO student2 VALUES(NULL,'韩信',99); -- 会自动增长，可以为NULL值

-- 修改自增起始值
alter table Student2 AUTO_INCREMENT = 100; -- 修改后后面的id再赋值，会在原来的id基础上 + 100

-- 建表以后添加主键
ALTER TABLE Student MODIFY id INT PRIMARY KEY;

-- 删除主键
ALTER TABLE Student DROP PRIMARY KEY;

-- 删除唯一约束
ALTER TABLE Student DROP INDEX NAME;

-- 建表后添加唯一约束，如果要约束的列已经有重复数据是添加不了的
ALTER TABLE Student MODIFY NAME INT UNIQUE;

-- 删除非空约束
ALTER TABLE Student MODIFY age TINYINT;

-- 创建表后单独添加非空约束,如果有已经存在的null值会提醒，但不报错，原来的null值会变空
ALTER  TABLE Student MODIFY age TINYINT NOT NULL;

-- 默认约束
create table Students(
    name varchar(20) DEFAULT '默认值'
);
-- 可以直接为空
insert into Students values ();
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
43
44
45

外键约束：

• 一个表中的某个字段引用其他表的主键，这个字段称为外键
• 主表：主键所在的表，约束别人的表，将数据给别人用
• 副表/从表：外键所在的表，被约束的表，使用别人的数据

作用：

作用就是让表和表之间产生关系，保证数据的准确性

为什么有外键约束：

表和表之间的数据有关联的时候，没有相关的数据约束无法保证数据的准确性！

什么时候用？

表和表之间有关联的时候呗，比如是用户和订单两个表之间

• 当我们在employee的dep_id里面输入不存在的部门时数据依然可以添加，但是并没有对应的部门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yfAt7NoX-1680064951362)(/Users/itzhuzhu/Library/Application Support/typora-user-images/image-20230328125749524.png)]

语法：

演示：

-- 创建部门表
CREATE TABLE department(
   id      INT PRIMARY KEY AUTO_INCREMENT,
   NAME    VARCHAR(20) UNIQUE,
   address VARCHAR(100) NOT NULL
);

-- 创建员工表
CREATE TABLE employee(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) UNIQUE,
    age  TINYINT NOT NULL,
    dep_id INT NOT NULL, -- 要和外键列的数据类型一致，否则报错
    CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department (id)
);

INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
INSERT INTO employee VALUES(NULL,'猴子',99,111); -- 在employee表中不能添加一个department表不存在的id
INSERT INTO employee VALUES(NULL,'韩信',99,1),(NULL,'李白',99,1),(NULL,'露娜',99,2);

-- 删除外键    
ALTER TABLE employee DROP FOREIGN KEY emp_dep_id;

-- 创建表后单独添加外键约束
ALTER TABLE employee ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(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

外键级联更新、删除：

什么是级联更新和级联删除

把user用户表中的某个用户删，该用户所有的订单也随之被删除
把user用户表中的某个用户id修改，订单表中该用户所属的订单用户编号也随之修改

演示：

-- 使用CASCADE 添加级联更新和级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

-- 将department表的id修改，employee表中的dep_id也会随之修改
UPDATE department SET id = 10 where id =1;

-- 将department表的id删除，employee表中的dep_id也会随之删除
DELETE FROM department  WHERE id = 1;


-- 使用 SET NULL添加级联更新和删除
ALTER TABLE employee ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE SET NULL ON DELETE SET NULL;
1
2
3
4
5
6
7
8
9
10
11
12

报错：说明表字段结构设置了不能为NULL

Column 'dep_id' cannot be NOT NULL: needed in a foreign key constraint 'emp_dep_id' SET NULL
1