MySQL | 表约束

一般在语法格式描述中会使用如下符号：

• <>：表示在语句中必须指定的数据对象，是不可或缺的。
• [ ]：表示可以根据需要进行选择，也可以不选。
• | ：表示多个选项只能选择其一。
• { }：表示必选项。

1、约束的作用

在MySQL中，各种完整性约束是数据库关系模型定义的一部分，可以通过CREATE TABLE或ALTER TABLE语句来定义。
对关系模型定义完整性约束，则是为了在数据库应用中保障数据的正确性和一致性，防止数据库中存在不符合语义的、不正确的数据。一旦定义了完整性约束，MySQL服务器会随时检测处于更新状态的数据库内容是否符合相关的完整性约束，从而保证数据的一致性与正确性。防止操作对数据库的意外破坏，也能提高完整性检测的效率。
关系模型中有三类完整性约束，分别是实体完整性、参照完整性和用户定义完整性。其中，实体完整性和参照完整性是关系模型必须满足的完整性约束条件，被称为是关系的两个不变性。

• 实体完整性：主键约束、唯一性约束
• 参照完整性：外键约束
• 用户定义完整性：非空约束、CHECK约束

2、主键约束

主键(PRIMARY KEY)是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以定义表的实体完整性。

2.1、选取设置主键约束的字段

主键约束即在表中定义一个主键来唯一确定表中的每一行数据的标识。主键可以是某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键的值必须是唯一的，而且构成主键的每一列的值都不允许为空。
主键列应该遵守下面的规则：

• 每个表只能定义一个主键。
• 主键值必须唯一标识表中的每一行，且不能为NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
• 一个列名只能在复合主键列表中出现一次。
• 复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的，这是最小化原则。

2.2、在创建表时设置主键约束

在MySQL表中，主键通过PRIMAEY KEY关键字来指定。
在定义列的同时，指定主键，语法格式如下：

<字段名> <数据类型> PRIMARY KEY 

在定义完所有列之后，指定主键，语法格式如下：

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

案例：创建s_student表，其主键为id

CREATE TABLE tb_emp(
      id INT(11) PRIMARY KEY,             # 主键
      name VARCHAR(25),
      deptId INT(11),
      salary FLOAT
);

CREATE TABLE tb_emp1(
      id INT(11),
      name VARCHAR(25),
      deptId INT(11),
      salary FLOAT,
      PRIMARY KEY(id)                      # 主键
);

2.3、在创建表时设置复合主键

复合主键由多个字段联合组成
语法格式：

PRIMARY KEY [字段1，字段2，...]

案例：创建s_student表，假设表中没有主键id，为了唯一确定一个学生，可以将name、deptId联合起来作为主键

CREATE TABLE s_student(
      name VARCHAR(25),
      deptId INT(11),
      salary FLOAT,
      PRIMARY KEY(name,deptId)
);

2.4、在修改表时添加主键约束

语法格式：

ALTER TABLE <表名> ADD PRIMARY KEY(<列名>)

案例：修改s_sdtudent表中的id为主键

ALTER TABLE s_student ADD PRIMARY KEY(id);

3、外键约束

外键用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
外键是表的一个字段，不是本表的主键，但对应另一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。例如，部门表tb_dept1的主键id，在员工表tb_emp4有一个键deptId与这个id关联。

• 主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
• 从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

3.1、选取设置外键约束的字段

定义一个外键时，需要遵守的规则：

• 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为父表定义主键。
• 主键不能包含空格，但允许在外键中出现空值，也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或者候选键。
• 外键中列的数目必须和父表的主键中列的数目相同。
• 外键中列的数据类型必须和父表主键中对应列的数据类型相同。

3.2、在创建表时设置外键约束

语法格式：

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，...] REFERENCES <主表名> 主键列1 [，主键列2，...]

语法说明：

• <外键名>：定义的外键约束的名称，一个表中不能有相同名称的外键。
• 字段名：表示子表需要添加外键约束的字段列
• <主表名>：被子表外键所依赖的表的名称
• 主键列：表示主表中定义的主键列或者列组合
  案例：创建学生表s_student和班级表s_class,并在s_student表上创建外键约束，让s_student表的class_id作为外键关联到表s_class的主键id

# 创建s_class
CREATE TABLE s_class(
      id INT(11) PRIMARY KEY,
      name VARCHAR(22) NOT NULL,
      location VARCHAR(50)
);

# 创建s_student
CREATE TABLE tb_emp(
      id INT(11) PRIMARY KEY,    
      name VARCHAR(25),
      class_id INT(11),
      salary FLOAT,
      CONSTRAINT fk_s_dept
      FOREIGN KEY (class_id) REFERENCES s_class(id)
);

关联指的是关系数据库中，相关表之间的联系。它是通过相同的属性或属性组来表示的。子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时会出现错误“ERROR 1005(HY000):Cant't create table 'database.tablename'(errno:150)”。

3.3、在修改表时添加外键约束

语法格式：

ALTER TABLE <数据表名> ADD CONSTRAINT <外键约束名>
FOREIGN KEY(<列名>) REFERENCES <主表名>(<列名>);

案例：修改学生表s_student，将class_id设置为外键，并与班级表s_class的主键id进行关联，确定外键约束名为fk_s_dept

ALTER TABLE s_student ADD CONSTRAIN fk_s_dept1 
FOREIGN KEY(class_id) REFERENCES s_class(id);

3.4、删除外键约束

语法格式：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

语法说明：

• 外键删除后，就会解除主表和从表之间的关联关系
  案例：删除s_student表的外键约束

ALTER TABLE s_student DROP FOREIGN KEY fk_s_dept;

4、唯一约束

4.1、选取设置唯一约束的字段

唯一约束要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

4.2、在创建表时设置唯一约束

语法格式：

<字段名> <数据类型> UNIQUE

案例：创建s_class表，班级名唯一

CREATE TABLE tb_dept2(
      id INT(11) PRIMARY KEY,
      name VARCHAR(22) UNIQUE,
      location VARCHAR(50)
);

MySQL中PRIMARY KEY与UNIQUE之间的区别：

• 一个表只能创建一个PRIMARY KEY，但可以定义多个UNIQUE。
• 定义为PRIMARY KEY的列不允许有空值，但UNIQUE的字段允许。
• 定义约束时，系统会自动产生索引。

4.3、在修改表时添加唯一约束

语法格式：

ALTER TABLE <表名> ADD CONSTRAINT <唯一约束名> UNIQUE (<列名>);

案例：修改s_class表，指定班级名唯一

ALTER TABLE s_class ADD CONSTRAINT unique_name UNIQUE (name);

4.4、删除唯一约束

语法格式：

ALTER TABLE <表名> DROP INDEX <唯一约束名>;

案例：修改s_class表，指定班级名唯一

ALTER TABLE s_class DROP INDEX unique_name ;

5、检查约束

5.1、选取设置检查约束的字段

CHECK约束可以通过CREATE TABLE或ALTER TABLE语句来实现。

CHECK (<表达式>)

语法说明：

• <表达式>：SQL表达式，用于指定需要检查的限定条件
  若将CHECK约束子句置于表中某个列的定义之后，则这种约束也称为基于列的CHECK约束。
  若将CHECK约束子句置于所有列的定义以及主键约束和外键定义之后，则这种约束也称为基于表的CHECK约束。
  在更新表数据的时候，系统会检查更新后的数据行是否满足CHECK约束中的限定条件。CHECK约束可以使用简单的表达式，也可以使用复杂的表达式作为限定条件，例如在限定条件中加入子查询。

5.2、在创建表时设置检查约束

语法格式：

check(<检查约束>)

案例：创建s_student表，要求salary字段值大于18且小于22

CREATE TABLE s_student(
    id INT(11) PRIMARY KEY,    
    name VARCHAR(25),
    salary FLOAT,
    CHECK(salary>0 and salary<10000)
);

5.3、在修改表时添加检查约束

语法格式：

ALTER TABLE <表名> ADD CONSTRAINT <检查约束名> CHECK (<检查约束>);

案例：修改s_student表，要求id字段值大于0

ALTER TABLE tb_dept ADD CONSTRAINT check_id CHECK (id>0);

5.4、删除检查约束

语法格式：

ALTER TABLE <表名> DROP CONSTRAINT <检查约束名>;

案例：删除s_student表check_id检查约束

ALTER TABLE s_student DROP CONSTRAINT check_id;

6、默认值约束

6.1、选取设置默认值约束的字段

默认值约束指定某列的默认值

6.2、在创建表时设置默认值约束

语法格式：

<字段名> <数据类型> DEFAULT <默认值>;

6.3、在修改表时添加默认值约束

语法格式：

ALTER TABLE <表名>
CHANGE COLUMN <字段名> <数据类型> DEFAULT <默认值>;

6.4、删除默认值约束

语法格式：

ALTER TABLE <数据表名> 
CHANGE COLUMN <字段名> 
<字段名> <数据类型> DEFAULT NULL;

7、非空约束

7.1、选取设置非空约束的字段

非空约束指字段的值不能为空，通过在表中某个列的定义后加上关键字NOT NULL作为限定词，来约束该列的取值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库就会报错。

7.2、在创建表时设置非空约束

语法格式：

<字段名> <数据类型> NOT NULL

7.3、在修改表时添加非空约束

语法格式：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

7.4、删除非空约束

语法格式：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <数据类型> NULL;

8、自增长约束

8.1、选取设置自增长约束的字段

• 列的数值自动增长，列的类型只能是整数类型
• 通常给主键添加自增长约束。
• 一张表只能有一个自增长列，并且该列需要定义约束。

8.2、在创建表时设置自增长约束

语法格式：

<字段名> <数据类型> AUTO_INCREMENT

8.3、在修改表时添加自增长约束

语法格式：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> AUTO_INCREMENT;

8.4、删除自增长约束

语法格式：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名> 
<字段名> <字段类型>;