MySQL——数据库表的约束

MySQL——数据库表的约束

1、数据库约束概述

1.约束的作用

• 对数据库表进行约束，可以对表中的数据进行限制，保证数据的正确性、有效性、完整性。

2.约束的种类

约束名	约束关键字
主键约束	primary key
唯一约束	unique
非空约束	not null
外键约束	foreign key
检查约束	check(mysql不支持)

2、主键约束

1.主键的特点

• 主键非空且唯一，但是与非空约束+唯一约束还是有区别的。在总结会做比较；

• 主键是数据库中每条记录的唯一标识；

• 一个表中只有一个主键，主键可以是一个字段也可以由多个字段组成。

2.创建主键：

• 关键字： primary key

1. 在创建表的时候添加主键

   -- 语法: 字段名 字段类型 PRIMARY KEY
   
   -- 创建表学生表stu, 包含字段(id, name, age)将id做为主键 
   create table st5 (   
       id int primary key,
       name varchar(20),   
       age int
   ) 
   

2. 在已有表中添加主键

   -- 语法: ALTER TABLE 表名 ADD PRIMARY KEY(字段名); 
   
   -- 将stu表中的id定为主键
   alter table stu add primary key(id);
   

3.删除主键：

• 关键字：drop primary key;

• 删除表中的主键

  -- 语法: ALTER TABLE 表名 drop PRIMARY KEY; 
  
  -- 删除stu表中的主键
  alter table stu drop primary key;
  

• 注意：添加主键时需要指定主键字段名，而删除主键不需要。

4.自增主键

• 关键字：auto_increment

• 在每次插入数据时，主键会自动增长，但是，删除记录时，主键不会自动减小，默认起始值为1

1. 在创建表的时候添加自增主键

   -- 字段名 字段类型 PRIMARY KEY AUTO_INCREMENT
   
   -- 创建表学生表stu, 包含字段(id, name, age)将id做为自增主键 
   create table st5 (   
       id int primary key auto_increment,
       name varchar(20),   
       age int
   ) 
   

2. 在创建表的时候添加自增主键并设定自增起始值

   -- 创建表学生表stu, 包含字段(id, name, age)将id做为自增主键 ，自增起始值为4
   create table st5 (   
       id int primary key auto_increment,
       name varchar(20),   
       age int
   ) auto_increment=4;
   

3. 在创建好的表中修改自增的起始值

   -- ALTER TABLE 表名 AUTO_INCREMENT=起始值; 
   
   -- 在stu表中设置自增起始值为1000
   alter table stu auto_increment = 1000;
   

4. 在已有的表中添加自增主键

   --  ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
   -- 将stu表中的id定为自增主键
   alter table stu add primary key auto_increment(id);
   

• 注意：

  1. 将字段设置成主键后，如何进行插入数据呢？

     -- 插入数据 
     insert into stu (name,age) values ('张三',18); 
     insert into stu (name,age) values ('李四',20); 
     
     -- 另一种写法在主键插入null值
     insert into stu values(null,'王五',35);
     

  2. DELETE 和 TRUNCATE 对自增的影响是不同的

     • DELETE ：使用DELETE删除表中所有数据之后，会主键没有影响
     • TRUNCATE：使用TRUNCATE删除表中所有数据之后，会使自增主键重新在默认值开始计算。

3、唯一约束：

• 关键字：unique

1.唯一约束特点

• 顾名思义，唯一约束，也就是列值不能重复。
• 语法： 字段名 字段类型 UNIQUE
• null是没有数据，不存在重复问题，也就是说，唯一约束的字段可以重复null

2.唯一约束示例

-- 创建stu表, id这一列设置唯一约束,不能出现同id的学生 
create table stu (   
    id int unique ,   
    name varchar(20) 
)

4、非空约束：

• 关键字：NOT NULL

1.非空约束的特点

• 被设定为非空的那列，值不能为空。

2.非空主键示例

-- 创建表stu,id字段设置非空约束，其中id不能为NULL 
create table st8 (  
    id int not null,  
    name varchar(20) ,  
    gender char(1) ) 

5、外键约束

1.解决数据冗余

1. 我们看下面的表，公司有两个部们，研发部和销售部，研发部在广州，销售部在深圳，我们全部放在一个表中，每次插入数据都会有一部分重复，这样会显得数据冗余，怎么解除数据冗余呢？
   

2. 我们可以将表中的数据拆分为两个表，员工表（employee）和部门表（department），这样，我们插入和查找数据的时候就不用那么繁琐，也减少的存储空间。
   

3. 但是这样没有进行约束的话，会发现员工表和部门表相互独立，当我们在添加员工信息时，发现dep_id无论输入什么值都可以。这样会出现员工没有找到对应的部门，那么我么需要解决这个问题。那就需要进行外键约束。

2.外键约束理解

• 分为主表和从表

  • 以公共关键字作主键的表为主表（部门表），作用是约束从表（员工表），比如部门表就是主表，约束从表（员工表）中的dep_id值。
  • 以公共关键字作外键的表为从表表（员工表）

• 外键：在从表中与主表主键对应的那一列，比如员工表中dep_id，换句话说dep_id就是外键，外键在从表上。

3.外键约束语法和示例

• 新建表时增加外键约束

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名) 

• 示例：

-- 创建员工表时，给dep_id增加外键
create table employee(  
    id int primary key auto_increment,  
    name varchar(20),  
    age int,  dep_id int,  
    -- 创建外键约束
    constraint emp_depid_fk foreign key (dep_id) references department(id) 

• 给已经存在的表增加外键

ALTER TABLE 从表  
            ADD [CONSTRAINT] [外键约束名称] 
            FOREIGN KEY (外键字段名) 
            REFERENCES 主表(主 键字段名); 

• 示例：

ALTER TABLE empolyee 
            ADD  CONSTRAINT  emp_depid_fk  
            FOREIGN KEY (dep_id) 
            REFERENCES epartment(id);

4.删除外键

• 语法：

ALTER TABLE 从表 drop foreign key 外键名称; 

• 示例：

-- 删除employee表的emp_depid_fk外键
-- 由使用外键名称emp_depid_fk，所以删除的时候也是指定该名称
alter table employee drop foreign key emp_depid_fk;

5.外键级联

• 外键级联：在修改和删除主表的主键时，同时修改或删除从表的外键

• 分类：

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

• 示例：

-- 创建员工表(employee),ids

create table employee
(  
    -- id设置为主键.且自增长
    id int primary key auto_increment,  
    name varchar(20),  
    age int,  
     -- 外键对应主表的主键  
    dep_id int,  
    -- 创建外键约束  
    constraint emp_depid_fk 
    foreign key (dep_id) 
    references department(id) 
    -- 级联更新和级联删除
    on update cascade 
    on delete cascade ) 

6.总结

1.主键约束与（非空约束+唯一约束）的区别

• 主键约束：
  • 主键约束非空且唯一
  • 主键可以时一个字段或多个字段组成
  • 一个表中只有一个主键
• 非空约束+唯一约束：
  • 非空约束可以定义在多个字段上，无论是值是什么，只要非空就行
  • 唯一约束可以定义在多个字段上，null代表为空，多个null是没问题的

2.DELETE 和 TRUNCATE 对自增的影响

• DELETE：使用DELETE删除表中所有数据之后，对自增主键没有影响，下次插入数据时，主键会定位到之前的位置继续自增长
• TRUNCATE：使用TRUNCATE删除表中所有数据的方式是先把整个表删除，再创建一张一摸一样的表出来，因此主键自增长的位置被破坏，主键会从默认位置（1）开始重新增长。