06.【MySQL约束】

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类

- 主键约束：primary key
- 非空约束：not null
- 唯一约束：unique
- 外键约束：foreign key

1. 非空约束： not null

not null，值不能为null

添加方式

• 创建表时添加约束

 -- 创建表添加非空约束
CREATE TABLE stu(
	id INT ,
	NAME VARCHAR(20) NOT NULL -- name为非空
);

• 创建表完后，添加非空约束

 -- 创建表完成后，添加非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL ;

删除方式

 -- 删除name的非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20);

2. 唯一约束： unique

unique，值不能重复

添加方式

• 创建表时唯一约束

 -- 创建表添加唯一约束
CREATE TABLE stu1(
	id INT ,
	phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);

• 创建表完后，添加唯一约束

 -- 创建表完成后，添加唯一约束
ALTER TABLE stu1 MODIFY phone_number VARCHAR(20) UNIQUE;

删除方式

 -- 删除唯一约束
-- alter table stu1 MODIFY phone_number VARCHAR(20); -- 错误，这种方法不行！
ALTER TABLE stu1 DROP INDEX phone_number; -- 唯一索引

注意

• MySQL中，唯一约束限定的列的值可以有多个null
• 意思就是：null值不算重复，代表不确定的意思。

3. 主键约束： primary key

含义：非空且唯一

注意

• 一张表只能有一个字段为主键
• 主键就是表中记录的唯一标识

添加方式

• 在创建表时，添加主键约束

 -- 创建表添加主键约束
CREATE TABLE stu2(
	id INT PRIMARY KEY, -- 给id添加了主键约束
	NAME VARCHAR(20)
);

• 创建完表后，添加主键

	ALTER TABLE stu2 MODIFY id INT PRIMARY KEY;

删除主键

 -- 删除主键
-- alter table stu modify id int ; -- 错误 
-- 不用指定主键字段名，因为每个表的主键只有一个
ALTER TABLE stu2 DROP PRIMARY KEY; 

自动增长 auto_increment

如果某一列是数值类型的，使用 auto_increment可以来完成值得自动增长。

有了自动增长后，主键可以为null，它会根据前一个值自动+ 1。

• 在创建表时，添加主键约束，并且完成主键自增长

create table stu2(
	id int primary key auto_increment,-- 给id添加主键约束
	name varchar(20)
);

测试添加效果：

INSERT INTO stu2 VCALUES(NULL，’ccc‘);

添加自动增长

ALTER TABLE stu2 MODIFY id INT AUTO_INCREMENT;

删除自动增长

ALTER TABLE stu2 MODIFY id INT;

4. 外键约束： foreign key

foreign key：让表于表产生关系，从而保证数据的正确性。

注意

外键的值可以为null，但是不可以不写。

添加方式

• 在创建表时，可以添加外键

不起外键名称时，constraint 外键名称可省略。

 -- 外键列
dep_id INT, -- 外键对应主表的主键(这两句话可以不挨着写)
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)

代码演示：

dep_id INT, -- 外键对应主表的主键
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)

• 创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

代码演示：

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

代码演示：

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

代码演示

 -- 创建表添加主键约束
CREATE TABLE emp( -- 创建emp表
	id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加了主键约束
	NAME VARCHAR(30),
	age INT,
	dep_name VARCHAR(20), -- 部门名称
	dep_location VARCHAR(30) -- 部门地址
);
 -- 修改列名称、类型
ALTER TABLE emp MODIFY dep_name VARCHAR(20);

 -- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

SELECT * FROM emp;

 -- 解决方案：分成2张表
 -- 创建部门表(id, dep_name. dep_location)
 -- 一方，主表
CREATE TABLE department(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加了主键约束
	dep_name VARCHAR(20), -- 部门名称
	dep_location VARCHAR(30) -- 部门地址
);

  -- 创建员工表(id, name, age, dep_id)
  -- 多方，从表
CREATE TABLE employee(
	id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加了主键约束
	NAME VARCHAR(30),
	age INT,
	dep_id INT, -- 外键对应主表的主键
	CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);

 -- 添加两个部门
 INSERT INTO department VALUES (NULL, '研发部', '广州'),(NULL, '销售部', '深圳');
 SELECT * FROM department;

 -- 添加员工，dept_id 表示员工所在的部门
 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
 INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
 INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
 INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
 INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
 INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
 
 
 -- 删除外键
 ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
 
  -- 创建表之后，添加外键 
  ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);
 
 
 SELECT * FROM department;
 
 SELECT * FROM employee;
 
 DROP TABLE employee;
 DROP TABLE department;

5. 级联操作

添加级联操作

语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
	FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) 
	ON UPDATE CASCADE 
	ON DELETE CASCADE;

分类

• 级联更新：ON UPDATE CASCADE
• 级联删除：ON DELETE CASCADE

注意

添加级联操作可能会出现危险（改变多个表格）、影响效率和性能等问题。在使用时要多考虑。

代码演示：

   -- 添加外键,设置及联更新 ，设置级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk
	FOREIGN KEY (dep_id) REFERENCES department(id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;