Mariadb表約束及三范式是什么

數(shù)據(jù)庫(kù)三大范式

范式（NF）：設(shè)計(jì)關(guān)系數(shù)據(jù)庫(kù)時(shí)，遵從不同的規(guī)范要求，設(shè)計(jì)出合理的關(guān)系型數(shù)據(jù)庫(kù)，這些不同的規(guī)范要求被稱為不同的范式，各種范式呈遞次規(guī)范，越高的范式數(shù)據(jù)庫(kù)冗余越小。但是有些時(shí)候一昧的追求范式減少冗余，反而會(huì)降低數(shù)據(jù)讀寫(xiě)的效率，這個(gè)時(shí)候就要反范式，利用空間來(lái)?yè)Q時(shí)間。可以把它粗略地理解為一張數(shù)據(jù)表的表結(jié)構(gòu)所符合的某種設(shè)計(jì)標(biāo)準(zhǔn)的級(jí)別。

1NF

即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要數(shù)據(jù)庫(kù)是關(guān)系型數(shù)據(jù)庫(kù)(mysql/oracle/db2/informix/sysbase/sql server)，就自動(dòng)的滿足1NF。數(shù)據(jù)庫(kù)表的每一列都是不可分割的原子數(shù)據(jù)項(xiàng)，而不能是集合，數(shù)組，記錄等非原子數(shù)據(jù)項(xiàng)。如果實(shí)體中的某個(gè)屬性有多個(gè)值時(shí)，必須拆分為不同的屬性 。通俗理解即一個(gè)字段只存儲(chǔ)一項(xiàng)信息。

以上，就不符合第一范式，因?yàn)檫M(jìn)貨、銷(xiāo)售還可以再分為，進(jìn)貨數(shù)量、進(jìn)貨單位、銷(xiāo)售單位、銷(xiāo)售數(shù)量等，以下則滿足了第一范式。

2NF

第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來(lái)的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。滿足1NF后，要求表中的所有列，都必須依賴于主鍵，而不能有任何一列與主鍵沒(méi)有關(guān)系，也就是說(shuō)一個(gè)表只描述一件事情；

例如：訂單表只描述訂單相關(guān)的信息，所以所有字段都必須與訂單id相關(guān)；產(chǎn)品表只描述產(chǎn)品相關(guān)的信息，所以所有字段都必須與產(chǎn)品id相 關(guān)；因此不能在一張表中同時(shí)出現(xiàn)訂單信息與產(chǎn)品信息；如下圖所示：

3NF

必須先滿足第二范式（2NF），要求：表中的每一列只與主鍵直接相關(guān)而不是間接相關(guān)，表中的每一列只能依賴于主鍵。

例如：訂單表中需要有客戶相關(guān)信息，在分離出客戶表之后，訂單表中只需要有一個(gè)用戶id即可，而不能有其他的客戶信息。因?yàn)槠渌目蛻粜畔⒅苯雨P(guān)聯(lián)于用戶id，而不是直接與訂單id直接相關(guān)。

各種約束

約束是用來(lái)限定表中數(shù)據(jù)準(zhǔn)確性、完整性、一致性、聯(lián)動(dòng)性的一套規(guī)則。在Mysql中，約束保存在information_schema數(shù)據(jù)庫(kù)的table_constraints中，可以通過(guò)該表查詢約束信息。如下圖：

NOT NULL

非空約束，是否允許該列的值為NULL，這里有一點(diǎn)很重要，很多字段（除了時(shí)間？）默認(rèn)值如果不指定的話都是NULL，所以除了NULL=NULL，其他值并不等于NULL，比如“”、0等。

修改一個(gè)字段為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)

這里還有一個(gè)問(wèn)題，對(duì)于默認(rèn)值為NULL但是又沒(méi)有指定插入這個(gè)字段：

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這個(gè)字段，默認(rèn)值為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這一列的值為空字符，而它的默認(rèn)值為NULL啊， 而logip默認(rèn)值為NULL，但是允許插入NULL值，所以這里顯示了NULL值。

查了一下~因?yàn)镹ULL為默認(rèn)值，但是又不允許NULL值，所以，也就是說(shuō)現(xiàn)在username這個(gè)字段沒(méi)有值，因?yàn)镾QL_MODE的原因，只會(huì)警告一下并不會(huì)直接報(bào)錯(cuò)，當(dāng)我們指定SQL_MODE為’STRICT_ALL_TABLES’時(shí)，此時(shí)插入就會(huì)報(bào)如下錯(cuò)誤：

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

UNIQUE

unique代表唯一約束：唯一約束是指定table的列或列組合不能重復(fù)，保證數(shù)據(jù)的唯一性，雖然唯一約束不允許出現(xiàn)重復(fù)的值，但是可以為多個(gè)null，同一個(gè)表可以有多個(gè)唯一約束，多個(gè)列組合的約束。在創(chuàng)建唯一約束的時(shí)候，如果不給唯一約束名稱，就默認(rèn)和列名相同，MySQL會(huì)給唯一約束的列上默認(rèn)創(chuàng)建一個(gè)唯一索引。

添加唯一約束：

MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_username UNIQUE(username);  //uq_username為約束名稱，UNIQUE(可多個(gè)字段）

//當(dāng)插入用戶名相同的數(shù)據(jù)事則會(huì)直接報(bào)錯(cuò)

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;

//添加兩個(gè)字段的約束
MariaDB [mydb]> ALTER TABLE user ADD CONSTRAINT uq_user UNIQUE(username,password);

//測(cè)試添加數(shù)據(jù)
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)

//僅當(dāng)兩個(gè)字段的數(shù)據(jù)都相同時(shí)才違反唯一約束
MariaDB [mydb]> INSERT INTO user(username,password) VALUES('test4','test5');
ERROR 1062 (23000): Duplicate entry 'test4-test5' for key 'uq_user'

PRIMARY KEY

主鍵約束相當(dāng)于唯一約束+非空約束的組合，主鍵約束列不允許重復(fù)，也不允許出現(xiàn)空值。如果是多列組合的主鍵約束， 那么這些列都不允許為空值，并且組合的值不允許重復(fù)。每個(gè)表最多只允許一個(gè)主鍵，建立主鍵約束可以在列級(jí)別創(chuàng)建，也可以在表級(jí)別上創(chuàng)建，MySQL的主鍵名總是PRIMARY， 當(dāng)創(chuàng)建主鍵約束時(shí)，系統(tǒng)默認(rèn)會(huì)在所在的列和列組合上建立對(duì)應(yīng)的唯一索引。

操作如下：

//因?yàn)楝F(xiàn)在的表中已經(jīng)有主鍵了，先把主鍵刪掉
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

//告訴我們一張表里只允許有一個(gè)字段為自動(dòng)增長(zhǎng)，且這個(gè)字段必須是主鍵，所以，我們要先取消它的自動(dòng)增長(zhǎng)。

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

外鍵約束是保證一個(gè)或兩個(gè)表之間的參照完整性，外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或是兩個(gè)表的兩個(gè)字段之間的參照關(guān)系。 也就是說(shuō)從表的外鍵值必須在主表中能找到或者為空，當(dāng)主表的記錄被從表參照時(shí)，主表的記錄將不允許刪除，如果要?jiǎng)h除數(shù)據(jù)，需要先刪除從表中依賴該記錄的數(shù)據(jù)，然后才可以刪除主表的數(shù)據(jù)，還有一種就是級(jí)聯(lián)刪除子表數(shù)據(jù)。 注意：外鍵約束的參照列，在主表中引用的只能是主鍵或唯一鍵約束的列，假定引用的主表列不是唯一的記錄，那么從表引用的數(shù)據(jù)就不確定記錄的位置，同一個(gè)表可以有多個(gè)外鍵約束。

現(xiàn)在，我們創(chuàng)建一個(gè)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表添加一個(gè)記錄，記錄用戶屬于那個(gè)組

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

//添加一個(gè)外鍵

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

//驗(yàn)證外鍵約束

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`))

//可以為空，但是不可以為參照表中沒(méi)有的值

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

其中一下級(jí)聯(lián)操作需要注意： ON DELETE CASCADE：當(dāng)刪除父（參照）表中的行時(shí)，如果子表中有依賴于被刪除父行的子行存在，那么連同子行一起刪除，不推薦使用。 ON DELETE SET NULL：當(dāng)刪除父（參照）表中的行時(shí)，如果子表中有依賴于被刪除父行的子行存在，那么不刪除，而是將子行的外鍵列設(shè)置為NULL

CHECK

CHECK約束就是當(dāng)向表中插入一行或更新一行數(shù)據(jù)時(shí)進(jìn)行CHECK約束檢查，CHECK接受一個(gè)表達(dá)式，如果這個(gè)表達(dá)式為T(mén)RUE則允許插入，如果這個(gè)表達(dá)式為FALSE則拒絕插入，在MariaDB10.2版本才開(kāi)始支持CHECK。

常見(jiàn)的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

例子：檢查用戶名長(zhǎng)度是否大于0

ALTER TABLE user ADD CONSTRAINT non_empty_name CHECK(CHAR_LENGTH(username)>0);
INSERT INTO user(id,username) VALUES(1,'');
/* SQL錯(cuò)誤（4025）：CONSTRAINT `non_empty_name` failed for `test`.`user` */

這個(gè)東西看起來(lái)很雞肋的樣子，好像一般都是在業(yè)務(wù)層進(jìn)行數(shù)據(jù)判斷了，而且數(shù)據(jù)庫(kù)嘛~就存數(shù)據(jù)就好了。