MySQL常见约束

• 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