【超详细】Mysql数据库中的约束

小鱼儿 2024-03-30 14:01 55阅读 0赞

**目录**

一、约束的概念和分类

1.约束的概念

2.约束的分类

二、案例分析

1. 非空约束（not null）

2. 主键约束（primary key）

3. 默认约束 (defaut)

4. 自动增长 （auto\_increment）

5.外键约束

--------------------

## 一、约束的概念和分类 ##

### 1.约束的概念 ###

*  约束是作用于表中列上的规则，用于限制加入表的数据
 *  约束的存在保证了数据库中数据的正确性、有效性和完整性

### 2.约束的分类 ###

![f34bbcda59de4046ace17fde181786ca.png][]

## 二、案例分析 ##

![429891008b9b4d46a93ec88ce945ae55.png][]

DROP TABLE IF EXISTS emp;
    
    -- 员工表
    CREATE TABLE emp (
      id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长 -- 【auto_increment】
      ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
      joindate DATE NOT NULL , -- 入职日期，非空
      salary DOUBLE(7,2) NOT NULL , -- 工资，非空
      bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
    );

#### 1. 非空约束（not null） ####

-- 演示非空约束
    insert into emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000)-- 报错 null
    insert into emp(id,ename,joindate,salary,bonus) values(3,"李四",'1999-11-11',8800,5000)-- 报错 唯一

**（1）添加约束：**

> **-- 创建表时添加非空约束**
> 
> create table 表名（
> 
> 列名 数据类型 not null,
> 
> ....
> 
> ） ；
> 
> **-- 建完表后添加约束**
> 
> alter table 表名 modify 字段名 数据类型 not null;

**（2） 删除约束:**

> alter table 表名 modify 字段名 数据类型;

#### 2. 主键约束（primary key） ####

-- 演示主键约束：非空且唯一
    insert into emp(id,ename,joindate,salary,bonus) values(1,"张三",'1999-11-11',8800,5000);
    
    insert into emp(id,ename,joindate,salary,bonus) values(1,"张三",'1999-11-11',8800,5000);-- 报错，id唯一
    insert into emp(id,ename,joindate,salary,bonus) values(null,"张三",'1999-11-11',8800,5000);-- 报错 null
    
    insert into emp(id,ename,joindate,salary,bonus) values(2,"李四",'1999-11-11',8800,5000);

**（1）添加约束：**

> **-- 创建表时添加主键约束**
> 
> create table 表名（
> 
> 列名 数据类型 primary key \[auto\_increment\],
> 
> ....
> 
> ） ；
> 
> create table 表名（
> 
> 列名 数据类型 ,
> 
> \[constraint\] \[约束名称\]primary key (列名)
> 
> ....
> 
> ） ；
> 
> **-- 建完表后添加主键约束**
> 
> alter table 表名 add primary key（字段名）;

**（2） 删除约束:**

> alter table 表名 drop primary key;

#### 3. 默认约束 (defaut) ####

-- 演示默认约束
    insert into emp(id,ename,joindate,salary) values(3,"王五",'1999-11-11',8800);-- defaut 约束 不给任何值
    
    insert into emp(id,ename,joindate,salary,bonus) values(4,"赵六",'1999-11-11',8800,null);-- null也算值

**（1）添加约束：**

> **-- 创建表时添加默认约束**
> 
> create table 表名（
> 
> 列名 数据类型 default 默认值,
> 
> ....
> 
> ） ；
> 
> **-- 建完表后添加默认约束**
> 
> alter table 表名 alter 列名 set defaut 默认值;

**（2） 删除约束:**

> alter table 表名 arter 列名 drop default;

#### 4. 自动增长 （auto\_increment） ####

**当列是数字类型且唯一时可用"auto\_increment"**

-- 演示自动增长：auto_increment:当列是数字类型且为唯一约束时
    insert into emp(ename,joindate,salary,bonus) values("赵六",'1999-11-11',8800,null);-- null也算值
    insert into emp(id,ename,joindate,salary,bonus) values(null,"赵六2",'1999-11-11',8800,null);
    insert into emp(id,ename,joindate,salary,bonus) values(null,"赵六3",'1999-11-11',8800,null);

#### 5.外键约束 ####

*  概念：外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性

![fa5a432d6eb3482fb97ffc51d6a360ac.png][]

/*
    	外键约束:
    		* 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性
    
    	
    	-- 创建表时添加外键约束
    	CREATE TABLE 表名(
    		 列名 数据类型,
    		 …
    		 [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
    	); 
    
    
    	-- 建完表后添加外键约束
    	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
    
    
    	-- 删除约束
    	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
    	
    	
    */
    -- 删除表
    DROP TABLE IF EXISTS emp;
    DROP TABLE IF EXISTS dept;
    
    
    -- 部门表
    CREATE TABLE dept(
    	id int primary key auto_increment,
    	dep_name varchar(20),
    	addr varchar(20)
    );
    -- 员工表 
    CREATE TABLE emp(
    	id int primary key auto_increment,
    	name varchar(20),
    	age int,
    	dep_id int,
    
    	-- 添加外键 dep_id,关联 dept 表的id主键
    	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
    		
    );
    -- 添加 2 个部门
    insert into dept(dep_name,addr) values
    ('研发部','广州'),('销售部', '深圳');
    
    -- 添加员工,dep_id 表示员工所在的部门
    INSERT INTO emp (NAME, age, dep_id) VALUES 
    ('张三', 20, 1),
    ('李四', 20, 1),
    ('王五', 20, 1),
    ('赵六', 20, 2),
    ('孙七', 22, 2),
    ('周八', 18, 2);
    
    -- ------------------
    select * from emp;
    select * from dept;
    
    
    -- 删除外键
    alter table emp drop FOREIGN key fk_emp_dept;
    
    
    -- 建完表后，添加外键
    alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

**5.1建员工表和部门表**

-- 删除表
    DROP TABLE IF EXISTS emp;
    DROP TABLE IF EXISTS dept;
    
    
    -- 部门表
    CREATE TABLE dept(
    	id int primary key auto_increment,
    	dep_name varchar(20),
    	addr varchar(20)
    );
    -- 员工表 
    CREATE TABLE emp(
    	id int primary key auto_increment,
    	name varchar(20),
    	age int,
    	dep_id int,
    
    	-- 添加外键 dep_id,关联 dept 表的id主键
    	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
    		
    );

**5.2 语法**

**（1）添加约束：**

> **-- 创建表时添加外键约束**
> 
> create table 表名（
> 
> 列名 数据类型 ,
> 
> ....
> 
> \[constraint\] \[外键名称\] foreign key（外键列名） references 主表（主表列名）
> 
>     -- 添加外键 dep_id,关联 dept 表的id主键，外键名称随便写，一般以fk_开头
>     	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
> 
> ） ；
> 
> **-- 建完表后添加外键约束**
> 
> alter table 表名 add constraint 外键名称 foreign key（外键字段名称）references 主表名称（主列表名称）;
> 
>     -- 建完表后，添加外键
>     alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

![b68c60b48763431aa9ecd031a55c3aea.png][]

**（2） 删除约束:**

> alter table 表名 drop foreign key 外键名称;

-- 删除外键
    alter table emp drop FOREIGN key fk_emp_dept;

**注意：**

*  **先创建主表和添加主表里的数据，在对从表进行操作！！！**
 *  **删除时，先删去****从表****数据才能删对应的****主表****数据****！！！**

[f34bbcda59de4046ace17fde181786ca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/d3b5731e724a450693b86dd2cf3662ef.png
[429891008b9b4d46a93ec88ce945ae55.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b6072dd4fac248378f82cdc3001f8376.png
[fa5a432d6eb3482fb97ffc51d6a360ac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/df292928493e427db9d086fbb20bb675.png
[b68c60b48763431aa9ecd031a55c3aea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/4f75fa0337264d0f8591a73f9f543733.png