MSQL常见约束

川长思鸟来 2022-12-27 06:26 135阅读 0赞

*  NOT NULL：非空约束，保证字段不能为空，例如：`name varchar(128) NOT NULL`
 *  DEFAULT：默认值约束，保证字段必须有一个默认值，例如：`age INT DEFAULT 18`
 *  PRIMARY KEY：主键约束，唯一性且不能为空，例如：`id INT PRIMARY KEY`
 *  UNIQUE：唯一性约束，唯一行，可以为空，例如：`cid char(18) UNIQUE`
 *  CHECK：用于检查约束，例如性别只能时男或者女，例如：`gender CHAR(1) CHECK(gender='男' OR gender='女')`
 *  FOREIGN KEY：外键约束，用于限制两个表的关系，用于保证该字段的值必须为主表关联列的值，例如：`majorId INT REFERENCES major(id)`

添加约束可以在两个时间点，一是在创建表时，二是在修改表结构时

### 创建表时添加约束 ###

行级约束案例：

DROP TABLE IF EXISTS major;
    CREATE TABLE major(
      id INT PRIMARY KEY, # 主键约束
      name VARCHAR(255) NOT NULL # 非空约束
    )
    
    DROP TABLE IF EXISTS student;
    CREATE TABLE student(
      id INT PRIMARY KEY, # 主键约束
      name VARCHAR(255) NOT NULL, # 非空约束
      gender CHAR(1) CHECK(gender='男' OR gender='女'), # 检查约束
      seat INT UNIQUE, # 唯一约束
      age INT DEFAULT 18, # 默认值
      majorId INT # FOREIGN KEY REFERENCES major(id) # 外键约束这样写不支持
    )

表级约束案例:

DROP TABLE IF EXISTS major;
    CREATE TABLE major(
      id INT,
      name VARCHAR(255),
      CONSTRAINT pk PRIMARY KEY(id)
    );
    
    DROP TABLE IF EXISTS student;
    CREATE TABLE student(
      id INT,
      name VARCHAR(255),
      gender CHAR(1),
      seat INT,
      age INT,
      majorId INT,
      CONSTRAINT pk PRIMARY KEY(id),
      CONSTRAINT uq UNIQUE(seat),
      CONSTRAINT ck CHECK(gender='男' OR gender='女'),
      CONSTRAINT fk_student_major FOREIGN KEY(majorId) REFERENCES major(id)
    )

> 结合使用`DESC table_name`和`SHOW INDEX FROM table_name`查看表的约束，创建联合主键时需要定义为表级主键约束

一般的通用写法是除了外键，其他的都定义为行级约束，例如：

DROP TABLE IF EXISTS student;
    DROP TABLE IF EXISTS major;
    
    CREATE TABLE major(
      id INT,
      name VARCHAR(255),
      CONSTRAINT pk PRIMARY KEY(id)
    );
    
    CREATE TABLE student(
      id INT PRIMARY KEY, # 主键约束
      name VARCHAR(255) NOT NULL, # 非空约束
      gender CHAR(1) CHECK(gender='男' OR gender='女'), # 检查约束
      seat INT UNIQUE, # 唯一约束
      age INT DEFAULT 18, # 默认值
      majorId INT,
      CONSTRAINT fk_student_major FOREIGN KEY(majorId) REFERENCES major(id)
    )

### 修改表时增加约束 ###

# 增加非空约束
    ALTER TABLE student MODIFY COLUMN name VARCHAR(255) NOT NULL;
    # 增加默认值约束
    ALTER TABLE student MODIFY COLUMN age INT DEFAULT 18;
    # 增加列级主键约束
    ALTER TABLE student MODIFY COLUMN id INT PRIMARY KEY;
    # 增加表级主键约束
    ALTER TABLE student ADD PRIMARY KEY(id);
    # 增加列级唯一性约束
    ALTER TABLE student MODIFY COLUMN seat INT UNIQUE;
    # 增加表级唯一性约束
    ALTER TABLE student ADD UNIQUE(seat);
    # 增加外键约束
    ALTER TABLE student ADD FOREIGN KEY(majorId) REFERENCES major(id);

总结：

# 1. 增加列级约束
    ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束;
    # 2. 增加表级约束
    ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [外键引用 其他表(其他表字段)]

### 修改表时增加约束 ###

删除约束其实就是修改表的列结构

# 删除非空约束
    ALTER TABLE student MODIFY COLUMN name VARCHAR(255);
    # 删除默认值约束
    ALTER TABLE student MODIFY COLUMN age INT;
    # 删除列级主键约束
    ALTER TABLE student MODIFY COLUMN id INT;
    # 删除表级主键约束
    ALTER TABLE student DROP PRIMARY KEY;
    # 删除列级唯一性约束
    ALTER TABLE student MODIFY COLUMN seat INT;
    # 删除表级唯一性约束
    ALTER TABLE student DROP INDEX seat;
    # 删除外键约束
    ALTER TABLE student DROP FOREIGN KEY majorId;

### 标示列（自增列） ###

在列后面加上`AUTO_INCREMENT`即可，注意一个表只能有一个标示列

DROP TABLE IF EXISTS student;
    DROP TABLE IF EXISTS major;
    
    CREATE TABLE major(
      id INT ,
      name VARCHAR(255),
      CONSTRAINT pk PRIMARY KEY(id)
    );
    
    CREATE TABLE student(
      id INT PRIMARY KEY AUTO_INCREMENT, # 主键约束
      name VARCHAR(255) NOT NULL, # 非空约束
      gender CHAR(1) CHECK(gender='男' OR gender='女'), # 检查约束
      seat INT UNIQUE, # 唯一约束
      age INT DEFAULT 18, # 默认值
      majorId INT,
      CONSTRAINT fk_student_major FOREIGN KEY(majorId) REFERENCES major(id)
    )

可以通过修改系统变量`auto_increment_increment`来修改步长

# 查看变量
    SHOW VARIABLES LIKE '%auto_increment%'
    # 修改变量
    SET auto_increment_increment=3