【MySQL】约束

2023-12-20 23:52:16

1、约束概述

为什么需要约束

数据完整性,是指数据的精确性和可靠性,从以下四个方面考虑:

  • 实体完整性,同一个表中不能有两条无法区分的记录
  • 域完整性,记录的属性具有范围
  • 引用完整性,例如,员工所在部门,在部门表中一定能找到这个部门
  • 用户自定义完整性,例如,用户名唯一、密码不能为空。

2、约束的分类

添加约束

CREATE TABLE 时添加约束(尽量刚开始就设好约束)
ALTER TABLE 时增加约束或者删除约束 
  • not null(非空约束)

    只有列级约束

    • # 1.在CREATE TABLE时添加约束
      CREATE TABLE test1(
      	id INT NOT NULL,
          last_name VARCHAR(15) NOT NULL,
          email VARCHAR(25),
          salary DECIMAL(10, 2)
      );
      
    • # 2.在ALTER TABLE时添加约束
      ALTER TABLE test1
      MODIFY email VARCHAR(25) NOT NULL; 
      
    • # 3.在ALTER TABLE时删除约束
      ALTER TABLE test1
      MODIFY email VARCHAR(25) NULL;
      
  • unique(唯一性约束)

    在这个约束下,通个字段中不能有相同的值,但是null值可以有多个,也可以多次添加null值。

    有列级约束,也有表级约束

    • # 1.在CREATE TABLE时添加约束
      CREATE TABLE test2(
      	id INT UNIQUE,  #列级约束
          last_name  VARCHAR(15),
          email VARCHAR(25) UNIQUE,
          salary DECIMAL(10, 2)# 表级约束
          CONSTRAINT uk_test2_email UNIQUE(email)
      );
      
    • # 2.在ALTER TABLE时添加约束
      ALTER TABLE test2
      # 方式一:添加表级约束
      ADD CONSTRAINT uk_test2_sal VARCHAR(15) UNIQUE(salary); 
      # 方式二:
      ALTER TABLE test2
      MODIFY last_name VARCHAR(15) UNIQUE;
      ```
      
      
    • # 3.复合的唯一性约束
      CREATE TABLE USER(
      	id INT,
          `name` VARCHAR(15),
          `password` VARCHAR(25),
          
          CONSTRAINT uk_user_name_pwd UNIQUE(`name`, `password`)
      )
      
    • # 4.删除唯一性约束
      # 删除唯一性约束只能通过删除唯一索引的方式删除
      ALTER TABLE test2
      DROP INDEX last_name;
      
      # 可以通过以下命令查看表的索引
      SHOW index from 表名称;
      
  • primary key(主键约束)

    作用:用来唯一标识表中的一行记录

    主键对于不同行不能重复,主键自动排序。

    • # 1.在CREATE TABLE时添加约束
      CREATE TABLE test3(
      	id INT,
          last_name VARCHAR(15) PRIMARY KEY, # 列级约束
          salary DECIMAL(10, 2),
          email VARCHAR(25)
        	# 表级约束  
          # 使用表级约束不用特地起名字,起别名不会生效 	
          CONSTRAINT pk_test5_id PRIMARY KEY(id)
      );
      # 这里对于联合的情况
      # 对于多列组合的复合主键约束,只要不重复且不为空值即可。 
      CREATE TABLE user1(
      	id INT,
          NAME VARCHAR(15),
          PASSWORD VARCHAR(25),
          
          PRIMARY KEY (NAME, PASSWORD)
      )
      
    • # 2.在ALTER TABLE时添加约束
      ALTER TABLE test6
      ADD PRIMARY KEY (id);
      
    • # 3.删除主键元素(实际上不会去删除主键约束)
      ALTER TABLE test6
      DROP PRIMARY KEY;
      
补充:自增列AUTO_INCREMENT

一个表最多有一个自增列, 且自增列约束的列必须是键列(主键列,唯一键列),数据类型必须为整形 。如果在这一列上插入0或者null,则自动增加。

# 1.在CREATE TABLE时添加
CREATE TABLE test7(
	id INT PRIMARY KEY AUTO_INCREMENT,
    last_name VARCHAR(15)
)

# 在这种情况下,插入数据一次,其对应约束属性每次加一,不需要手动指定,且从1开始
INSERT INTO test7(last_name)
VALUE('Tom');

# 2.在ALTER TABLE 时添加
CREATE TABLE test7(
	id INT PRIMARY KEY,
    last_name VARCHAR(15)
)
ALTER TABLE test8
MODIFY id INT AUTO_INCREMENT;

# 3.在ALTER TABLE 时删除
ALTER TABLE test8
MODIFY id INT;
在MySQL8.0中实现了自增变量的持久化,这就导致在MySQL服务重启后,对带有AUTO_INCREMENT的列插入数据仍然可以完成基于关闭服务前的值进行自增。而在MySQL5.7当中并没有实现自增变量的持久化。
  • foreign key(外键约束)

    作用:限定某个表的某个字段的引用完整性。

    外键约束中有主表和从表的概念, 例如:员工表的员工所在部门这个字段的值要参考部门表:部门表是主表,员工表是从表。

    • # 1.在CREATE TABLE时添加
      # 先创建主表
      CREATE TABLE dept1(
      	dept_id INT PRIMARY KEY,
          dept_name VARCHAR(15)
      );
      # 创建从表
      CREATE TABLE emp1(
      	emp_id INT PRIMARY KEY AUTO_INCREMENT,
          emp_name VARCHAR(15),
          department_id INT,
          
          CONSTRAINT fk_emp1_dept_id FOREIGN KEY (department_id) REFERENCES dept1(dept_id)
      )
      
      
    • # 2.在ALTER TABLE时添加外键约束
      CREATE TABLE dept2(
      	dept_id INT PRIMARY KEY,
          dept_name VARCHAR(15)
      );
      CREATE TABLE emp2(
      	emp_id INT PRIMARY KEY AUTO_INCREMENT,
          emp_name VARCHAR(15),
          department_id INT
      );
      ALTER TABLE emp2
      ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY(department_id) REFERENCES ON depts(dept_id);
      
    • # 3.约束等级
      --Cascade方式:子表同步父表的update/delete记录
      --Set null方式:父表进行update/delete时,子表对应 记录设为 null,并且子表的外键列不能为not null。
      --No action方式:子表中有对应记录时,不允许父表对对应记录进行update/delete记录。
      --Restrict方式:同no action,立即检查外键约束
      --Set default方式:父表变更时,子表将外键列设置为一个默认值。
      
      结论:对于外键列,最好采用ON UPDATE CASCADE ON DELETE RESTRICT 方式
      
    • # 4.删除外键约束
      ALTER TABLE emp1
      DROP FOREIGN KEY fk_emp1_dept_id;
      # 删除外键约束对应的普通索引(用外键约束名来删除)
      ALTER TABLE emp1
      DROP INDEX fk_emp1_dept_id;
      
    • 外键概念需要在应用层解决,在数据库中最好不要使用外键和级联。即,在应用层面即体现数据完整性,在数据库中使用外键会使得一张表的更新影响其他表,从而在高并发的情况下带来更新风暴。

  • check(检查约束)

    • CREATE TABLE test10(
      	id INT, 
          last_name VARCHAR(15),
          salary DECIMAL(10, 2) CHECK(salary > 2000)
      );
      # 在插入的记录的salary小于2000时会报错
      # 8.0版本会报错,5.7版本没有用
      
  • default(默认值约束)

    • # 1.在VREATE TABLE添加约束
      CREATE TABLE test11(
      	id INT, 
          last_name VARCHAR(15),
          salary DECIMAL(10, 2) DEFAULT 2000
      );
      # 在插入命令中没有明确值的时候填充默认值
      INSERT INTO test11(id, last_name)
      VALUES(1, 'Tom');
      
    • # 2.在ALTER TABLE添加约束
      CREATE TABLE test12(
      	id INT,
          last_name VARCHAR(15),
          salary DECIMAL(10, 2)
      );
      
      ALTER TABLE test12
      MODIFY salary DECIMAL(8, 2) DEFAULT 2500;
      

3、面试题

请添加图片描述

文章来源:https://blog.csdn.net/lrzHHl/article/details/135119727
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。