MySQL-约束

一、基本含义

目的：保证数据的一致性和完整性。
根据参照数目划分：表级约束（数目大于1）、列级约束（数目为1）。
根据功能划分：非空约束、主键约束、唯一约束、默认约束、外键约束。

二、非空约束——NOT NULL

NULL：字段值可以为空。
NOT NULL：字段值禁止为空。

username TINYINT UNSIGNED NOT NULL,

三、自动编号——AUTO_INCREMENT

（1）自动编号，且必须和主键组合使用。
（2）默认情况下，起始值为1，每次的增量为1。

id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '省份ID（参照列）',

四、主键约束——PRIMARY KEY

（1）主键约束，一张表只能存在一个主键。
（2）保证记录的唯一性（UNIQUE KEY）。
（3）自动非空（NOT NULL）。
（4）添加主键约束时，会自动创建索引。

4.1 建表时声明

id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '省份ID（参照列）',

4.2 建表后声明

4.2.1 添加主键约束

语法：
ALTER TABLE table_name ADD[CONSTRAINT [symbol] PRIMARY KEY [index_type] (index_col_name,…);

ALTER TABLE t_student ADD CONSTRAINT pk_student_id PRIMARY KEY (id);

4.2.2 删除主键约束

语法：
ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE t_student DROP PRIMARY KEY;

五、唯一约束——UNIQUE KEY

（1）唯一约束，一张表可以存在多个唯一约束。
（2）保证记录的唯一性（UNQUE KEY）。
（3）字段可以为空值。但只能只有一个空值。

5.1 建表时声明

username VARCHAR(20) NOT NULL UNIQUE KEY,

5.2 建表后声明

5.2.1 添加唯一约束

语法：
ALTER TABLE table_name ADD[CONSTRAINT [symbol] UNIQUE [INDEX | KEY ] [index_name] [index_type] (index_col_name,…);

ALTER TABLE t_student ADD UNIQUE KEY (username);

5.2.2 删除唯一约束

语法：
ALTER TABLE table_name DROP {INDEX | KEY} index_name;

-- 查看索引
SHOW INDEXES FROM t_student\G;
ALTER TABLE t_student DROP INDEX username;

六、默认约束——DEFAULT

（1）默认值。
（2）当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

6.1 建表时声明

sex ENUM('男','女','保密') DEFAULT '保密'

6.2 建表后声明

ALTER TABLE table_name ALTER[CONSTRAINT] column_name {SET DEFAULT literal | DROP DEFAULT};

ALTER TABLE t_student ALTER sex SET DEFAULT '男';
ALTER TABLE t_student ALTER sex DROP DEFAULT ;

七、外键约束——FOREIGN KEY

7.1 目的

（1）保持数据一致性、完整性。
（2）实现一对一或一对多关系。
注意：
（1）此为物理约束。在实际操作过程中，使用逻辑约束（物理约束只有InnoDB引擎支持）。
（2）同时给子父表插入信息时，必须先父表，后子表。

7.2 设置外键的前提

（1）父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
子表：具有外键列（修饰词含FOREIGN KEY）的表。外键列所在的表。
父表：子表所参照的表。参照列所在的表。
（2）数据表的存储引擎只能为InnoDB。
（3）外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。
（4）外键列和参照列必须创建索引。外键列（不会自动创建索引）参照列（自动创建索引）。

7.3 外键约束的参照操作

7.3.1 CASCADE

当删除或更新父表时，子表相关记录自动删除或修改。

建表时声明

FOREIGN KEY (pid) REFERENCES t_province (id) ON DELETE CASCADE

建表后声明
添加
ALTER TABLE table_name ADD[CONSTRAINT [symbol] FOREIGN KEY [index_name] [index_type] (index_col_name,…) reference_definition;

ALTER TABLE t_student ADD FOREIGN  KEY (pid) REFERENCES t_province (id);

删除
ALTER TABLE table_name DROP FOREIGN KEY fk_name;

SHOW CREATE TABLE t_student;
ALTER TABLE t_student DROP FOREIGN  KEY t_studentidfk_1;

7.3.2 SET NULL

前提：子表对应字段，无NOT NULL约束。
当删除或更新父表时，子表相关记录自动转化为NULL。

7.3.3 REATRICT（或NO ACTION）

拒绝对父表进行删除和修改操作。

八、列级约束

既可在列定义时声明，又可在列定义后声明。

九、表集约束

只能在列定义后声明。

-- 省份表。父表。
CREATE TABLE t_province(
  id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '省份ID（参照列）',
  username VARCHAR(20) NOT NULL COMMENT '省份名称'
)ENGINE=InnoDB DEFAULT CHARACTER=utf8;
-- 学生表。子表。
CREATE TABLE t_student(
  id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
  username VARCHAR(10) NOT NULL COMMENT '学生名称',
  pid TINYINT UNSIGNED COMMENT '外键列',
  -- 列定义之后，声明外键。
  FOREIGN KEY (pid) REFERENCES t_province (id)
)ENGINE=InnoDB DEFAULT CHARACTER=utf8;