Mariadb學習總結（五）：資料庫表格約束及三範式
- 王林原創
- 2024-07-20 03:00:00350瀏覽
範式（NF）：設計關係資料庫時，遵從不同的規範要求，設計出合理的關係型資料庫，這些不同的規範要求稱為不同的範式，各種範式呈現次規範，越高的範式資料庫冗餘餘越小。但有些時候一昧的追求範式減少冗餘，反而會降低資料讀寫的效率，這個時候就要反範式，利用空間換時間。可以把它粗略地理解為一張資料表的表結構所符合的某種設計標準的等級。
即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要數據庫是關係型數據庫(mysql/oracle/db2/informix/sysbase/sql server)，就自動的滿足1NF。資料庫表的每一列都是不可分割的原子資料項，而不能是集合，數組，記錄等非原子資料項。如果實體中的某個屬性有多個值時，必須拆分為不同的屬性 。通俗理解即一個字段只儲存一項資訊。
以上，就不符合第一範式，因為進貨、銷售還可以再分為，進貨數量、進貨單位、銷售單位、銷售數量等，以下則滿足了第一範式。
第二範式（2NF）是在第一範式（1NF）的基礎上建立起來的，即滿足第二範式（2NF）必須先滿足第一範式（1NF）。滿足1NF後，要求表中的所有列，都必須依賴主鍵，而不能有任何一列與主鍵沒有關係，也就是說一個表只描述一件事情；
例如：訂單表只描述訂單相關的信息，所以所有字段都必須與訂單id相關；產品表只描述產品相關的信息，所以所有字段都必須與產品id相關；因此不能在一張表中同時出現訂單信息與產品資訊；如下圖所示：
必須先滿足第二範式（2NF），要求：表中的每一列只與主鍵直接相關而不是間接相關，表中的每一列只能依賴主鍵。
例如：訂單表中需要有客戶相關資訊，在分離出客戶表之後，訂單表中只需要有一個用戶id即可，而不能有其他的客戶資訊。因為其他的客戶資訊直接關聯於用戶id，而不是直接與訂單id直接相關。
約束是用來限定表中資料準確性、完整性、一致性、連動性的一套規則。在Mysql中，約束保存在information_schema資料庫的table_constraints中，可以透過此表查詢約束資訊。如下圖：
非空約束，是否允許該列的值為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代表唯一約束：唯一約束是指定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'
主鍵約束相當於唯一約束+非空約束的組合，主鍵約束列不允許重複，也不允許出現空值。如果是多列組合的主鍵約束，則這些列都不允許為空值，且組合的值不允許重複。每個表最多只允許一個主鍵，建立主鍵約束可以在列級別創建，也可以在表級別上創建，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;
外鍵約束是保證一個或兩個表之間的參照完整性，外鍵是構建於一個表的兩個字段或是兩個表的兩個字段之間的參照關係。 也就是說從表的外鍵值必須在主表中能找到或為空，當主表的記錄被從表參照時，主表的記錄將不允許刪除，如果要刪除數據，需要先刪除從表中依賴該記錄的數據，然後才可以刪除主表的數據，還有一種就是級聯刪除子表數據。
注意：外鍵約束的參考列，在主表中引用的只能是主鍵或唯一鍵約束的列，假定引用的主表列不是唯一的記錄，那麼從表引用的資料就不確定記錄的位置，同一個表可以有多個外鍵約束。
現在，我們建立一個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接受一個表達式，如果這個表達式為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 <p>範例：檢查使用者名稱長度是否大於0</p> <pre class="brush:php;toolbar:false"> 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` */
這個東西看起來很雞肋的樣子，好像一般都是在業務層進行資料判斷了，而且資料庫嘛~就存資料就好了。
以上是Mariadb學習總結（五）：資料庫表格約束及三範式的詳細內容。更多資訊請關注PHP中文網其他相關文章！