Mariadb学习总结（五）：数据库表约束及三范式

共计 7844 个字符，预计需要花费 20 分钟才能阅读完成。

数据库三大范式

Mariadb 学习总结（五）：数据库表约束及三范式

范式（NF）：设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。但是有些时候一昧的追求范式减少冗余，反而会降低数据读写的效率，这个时候就要反范式，利用空间来换时间。可以把它粗略地理解为 一张数据表的表结构所符合的某种设计标准的级别。

1NF

即表的列的具有原子性, 不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足 1NF。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性。通俗理解即一个字段只存储一项信息。

Mariadb 学习总结（五）：数据库表约束及三范式
以上，就不符合第一范式，因为进货、销售还可以再分为，进货数量、进货单位、销售单位、销售数量等，以下则满足了第一范式。

Mariadb 学习总结（五）：数据库表约束及三范式

2NF

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。满足 1NF 后，要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系，也就是说一个表只描述一件事情；

例如：订单表只描述订单相关的信息，所以所有字段都必须与订单 id 相关；产品表只描述产品相关的信息，所以所有字段都必须与产品 id 相关；因此不能在一张表中同时出现订单信息与产品信息；如下图所示：

Mariadb 学习总结（五）：数据库表约束及三范式

3NF

必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，表中的每一列只能依赖于主键。

例如：订单表中需要有客户相关信息，在分离出客户表之后，订单表中只需要有一个用户 id 即可，而不能有其他的客户信息。因为其他的客户信息直接关联于用户 id，而不是直接与订单 id 直接相关。

Mariadb 学习总结（五）：数据库表约束及三范式

各种约束

约束是用来限定表中数据准确性、完整性、一致性、联动性的一套规则。在 Mysql 中，约束保存在 information_schema 数据库的 table_constraints 中，可以通过该表查询约束信息。如下图：

Mariadb 学习总结（五）：数据库表约束及三范式

NOT NULL

非空约束，是否允许该列的值为 NULL，这里有一点很重要，很多字段（除了时间？）默认值如果不指定的话都是 NULL，所以除了 NULL=NULL，其他值并不等于 NULL，比如“”、0 等。

修改一个字段为 NOT NULL：

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.00 sec)

MariaDB [mydb]> ALTER TABLE user MODIFY logip varchar(20) NOT NULL;
Query OK, 5 rows affected, 5 warnings (0.04 sec)   
Records: 5  Duplicates: 0  Warnings: 5

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | NO   |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)

这里还有一个问题，对于默认值为 NULL 但是又没有指定插入这个字段：

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+----------------+
| Field    | Type        | Null | Key | Default             | Extra          |
+----------+-------------+------+-----+---------------------+----------------+
| id       | int(11)     | NO   | PRI | NULL                | auto_increment |
| username | varchar(10) | NO   |     | NULL                |                |
| password | varchar(10) | NO   |     | NULL                |                |
| regtime  | timestamp   | NO   |     | CURRENT_TIMESTAMP   |                |
| logtime  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
| logip    | varchar(20) | YES  |     | NULL                |                |
+----------+-------------+------+-----+---------------------+----------------+
// 看 username 这个字段，默认值为 NULL，不允许 NULL
MariaDB [mydb]> INSERT INTO user(password) VALUES('test7');
Query OK, 1 row affected, 1 warning (0.00 sec)
// 这里看到我们插入成功了。MariaDB [mydb]> SELECT * FROM user WHERE password='test7';
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
| 12 |          | test7    | 2018-02-25 15:25:14 | 0000-00-00 00:00:00 | NULL  |
+----+----------+----------+---------------------+---------------------+-------+
1 row in set (0.00 sec)

可以看到 username 这一列的值为空字符，而它的默认值为 NULL 啊，
而 logip 默认值为 NULL，但是允许插入 NULL 值，所以这里显示了 NULL 值。

查了一下~ 因为 NULL 为默认值，但是又不允许 NULL 值，所以，也就是说现在 username 这个字段没有值，因为 SQL_MODE 的原因，只会警告一下并不会直接报错，当我们指定 SQL_MODE 为 ’STRICT_ALL_TABLES’ 时，此时插入就会报如下错误：

MariaDB [mydb]> INSERT INTO user(password) VALUES('test88');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

UNIQUE

unique 代表唯一约束：唯一约束是指定 table 的列或列组合不能重复，保证数据的唯一性，虽然唯一约束不允许出现重复的值，但是 可以为多个 null，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同，MySQL 会给唯一约束的列上默认创建一个唯一索引。

添加唯一约束：

MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE(username);  //uq_username 为约束名称，UNIQUE(可多个字段）// 当插入用户名相同的数据事则会直接报错

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
ERROR 1062 (23000): Duplicate entry 'test4' for key 'uq_username'

// 删除此约束
MariaDB [mydb]> ALTER TABLE user DROP KEY uq_username;

// 添加两个字段的约束
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE(username,password);

// 测试添加数据
MariaDB [mydb]> SELECT * FROM user;                                                     
+----+----------+----------+---------------------+---------------------+-------+
| id | username | password | regtime             | logtime             | logip |
+----+----------+----------+---------------------+---------------------+-------+
|  7 | test2    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  8 | test3    | test3    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
|  9 | test4    | test5    | 2018-02-24 16:42:48 | 0000-00-00 00:00:00 |       |
+----+----------+----------+---------------------+---------------------+-------+
3 rows in set (0.00 sec)

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test123');
Query OK, 1 row affected (0.01 sec)

// 仅当两个字段的数据都相同时才违反唯一约束
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test5');
ERROR 1062 (23000): Duplicate entry 'test4-test5' for key 'uq_user'

PRIMARY KEY

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。每个表 最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建，MySQL 的主键名总是 PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

操作如下：

// 因为现在的表中已经有主键了，先把主键删掉
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

// 告诉我们一张表里只允许有一个字段为自动增长，且这个字段必须是主键，所以，我们要先取消它的自动增长。MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id int(11) NOT NULL;
Query OK, 4 rows affected (0.07 sec)               
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [mydb]> DESC user;
+----------+-------------+------+-----+---------------------+-------+
| Field    | Type        | Null | Key | Default             | Extra |
+----------+-------------+------+-----+---------------------+-------+
| id       | int(11)     | NO   | PRI | NULL                |       |


// 再次删除主键
MariaDB [mydb]> ALTER TABLE user DROP PRIMARY KEY;
Query OK, 4 rows affected (0.03 sec)               
Records: 4  Duplicates: 0  Warnings: 0

// 好了，再让我们把主键加上吧~~~   以下两种方式都可以哦~
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT PRIMARY KEY(id);
MariaDB [mydb]> ALTER TABLE user MODIFY COLUMN id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT;

FOREIGN KEY

外键约束是 保证一个或两个表之间的参照完整性 ，外键是构建于 一个表的两个字段或是两个表的两个字段之间的参照关系 。也就是说从表的 外键值必须在主表中能找到或者为空，当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据，还有一种就是级联删除子表数据。
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录，那么从表引用的数据就不确定记录的位置，同一个表可以有多个外键约束。

现在，我们创建一个 GROUP 表吧，用于记录用户的分组信息，

CREATE TABLE `usergroup` (`id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `comment` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 |

然后呢~ 为 user 表添加一个记录，记录用户属于那个组

MariaDB [mydb]> ALTER TABLE user ADD COLUMN groupid INT(3);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

// 添加一个外键

ALTER TABLE user ADD CONSTRAINT fk_groupid FOREIGN KEY (groupid) REFERENCES usergroup(id);

// 验证外键约束

MariaDB [mydb]> INSERT INTO user(username,password,groupid) VALUES('test99','test00',1); 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user`, CONSTRAINT `fk_groupid` FOREIGN KEY (`groupid`) REFERENCES `usergroup` (`id`))

// 可以为空，但是不可以为参照表中没有的值

MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test99','test00');
Query OK, 1 row affected (0.01 sec)

外键定义：

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

其中一下级联操作需要注意：
ON DELETE CASCADE：当删除父（参照）表中的行时，如果子表中有依赖于被删除父行的子行存在，那么连同子行一起删除，不推荐使用。
ON DELETE SET NULL：当删除父（参照）表中的行时，如果子表中有依赖于被删除父行的子行存在，那么不删除，而是将子行的外键列设置为 NULL

CHECK

CHECK 约束就是当向表中插入一行或更新一行数据时进行 CHECK 约束检查，CHECK 接受一个表达式，如果这个表达式为 TRUE 则允许插入，如果这个表达式为 FALSE 则拒绝插入，在 MariaDB10.2 版本才开始支持 CHECK。

常见的 CHECK 约束有：

CONSTRAINT non_empty_name CHECK (CHAR_LENGTH(name) > 0)
CONSTRAINT consistent_dates CHECK (birth_date IS NULL OR death_date IS NULL OR birth_date 
例子：检查用户名长度是否大于 0 
ALTER TABLE user ADD CONSTRAINT non_empty_name CHECK(CHAR_LENGTH(username)>0);
INSERT INTO user(id,username) VALUES(1,'');
/* SQL 错误（4025）：CONSTRAINT `non_empty_name` failed for `test`.`user` */
这个东西看起来很鸡肋的样子，好像一般都是在业务层进行数据判断了，而且数据库嘛~ 就存数据就好了。

阿里云 2 核 2G 服务器 3M 带宽 61 元 1 年，有高配
腾讯云新客低至 82 元 / 年，老客户 99 元 / 年
代金券：在阿里云专用满减优惠券