MySQL约束

发表于

约束保证数据的完整性和一致性
约束按功能分为PRIMARY KEY、UNIQUE KEY、DEFAULT、NOT NULL、FOREIGN KEY
按数目分为列级约束和表级约束


查看约束

1
2
SHOW INDEX FROM table_name\G;
SHOW INDEX FROM table_name;


主键约束：PRIMARY KEY

主键可以写为PRIMARY KEY，也可以写成KEY

  1. 唯一性（可以赋值但是不能给两个记录赋一样的值）每张表只存在一个主键
  2. 主键保证记录的唯一性
  3. 主键自动为NOT NULL，也就是说必须要为主键赋值。但如果主键选择了AUTO_INCREMENT，那么不需要手动赋值
  4. auto_increment必须和主键primary key一起使用，但是主键primary key不一定要和auto_increment一块使用
  5. 主键的字段是可以赋值的，但不能赋相同的值
1
2
3
4
CREATE TABLE user(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL
);

记录如果删除了编号会出现间断

自增 AUTO_INCREMENT

  1. 自动编号，保证记录的唯一性，且必须与主键组合使用
  2. 默认情况下，初始为1，每次增长量为1.
  3. 对应的字段设置必须是数值类型，若是浮点类型，小数位必须为0.
  4. 自动编号必须是主键字段
1
2
3
4
CREATE TABLE tb3(
id SMALLINT UNSIGNED AUTO_INCREMENT,
username VARCHAR(30) NOT NULL); 
//报错，自动增量字段必须设置成主键

AUTO_INCREMENT必须是主键
主键不一定要用AUTO_INCREMENT


唯一约束：UNIQUE KEY

  1. 唯一约束可以保证记录的唯一性
  2. 唯一约束的字段可以为空值（NULL），存储时只保留一个NULL
  3. 每张数据表可以存在多个唯一约束
1
2
3
4
5
CREATE TABLE tb2(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
age TINYINT UNSIGNED
);


默认约束：DEFAULT

如果没有明确为字段赋值会自动赋与默认值

1
2
3
4
5
6
CREATE TABLE tb6(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
sex ENUM('1','2','3') DEFAULT '3'
);
//表示当sex没有赋值时，默认值为3


非空约束：NOT NULL

NULL，字段值可以为空
NOT NULL，字段值禁止为空，不赋值会报错


外键约束：FOREIGN KEY

保持数据一致性，完整性；实现一对一或一对多关系

要求：

  1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表(子表：具有外键列的表；父表：子表参照的表)
  2. 数据表的存储引擎只能为InnoDB
  3. 外键列和参照列必须具有类似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
  4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引

在配置文件（my.ini\my.conf）中编辑默认的存储引擎：

1
default-storage-engine=INNODB

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE table_name1(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL
);
CREATE TABLE table_name2(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(20) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY (pid) REFERENCES table_name1(id)  
);
// 外键 pid 参照 table_name1中的 id 字段

显示创建表的语句：SHOW CREATE TABLE table_name；
查看表是否有索引：SHOW INDEXS FROM table_name；
以网格查看表是否有索引：SHOW INDEXS FROM table_name\G；

外键约束的参照操作

  1. CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行
  2. SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL
  3. RESTRICT：拒绝对父表的删除或更新操作
  4. NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同
1
2
3
4
5
6
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pid SMALLINT,
username VARCHAR(10) NOT NULL,
FOREIGN KEY (pid) REFERENCES province (id) ON DELETE CASCADE
);

从父表删除或更新行，会设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL

实际开发中，我们很少使用物理的外键约束，而是使用逻辑约束；物理的外键约束只有innoDB这种存储引擎才会支持，MYISAM这种引擎就不支持物理的外键约束。反过来说，当我们使用到的引擎为MYISAM时，只能使用逻辑外键（即两个表的设计的逻辑关系）。

表级约束与列级约束

对一个数据列建立的约束，称为列级约束
对多个数据列建立的约束，称为表级约束

列级约束既可以在列定义时声明，也可以在列定以后声明
表级约束只能在列定义后声明

主键、外键、唯一既可以作为表级约束，也可作为列级约束
not null、default只有列级约束