首頁  >  文章  >  資料庫  >  深入淺析MySQL中常見的6種約束類型

深入淺析MySQL中常見的6種約束類型

青灯夜游
青灯夜游轉載
2021-09-16 19:55:033126瀏覽

深入淺析MySQL中常見的6種約束類型

約束的字面意思是規定或限制某個事該如何去做,在MySQL中,約束就是對資料表中資料指定規則,也就是對資料進行限制,以此來確保可靠性,例如不允許某一列出現Null值,實際中我們會遇到以下類型的限制。

  • NOT NULL : 確保資料列不能有NULL值
  • CHECK : 確保列中的值符合特定條件
  • UNIQUE : 確保一列中的所有值都不同
  • #PRIMARY KEYNOT NULLUNIQUE組合,唯一識別表中的每一行
  • FOREIGN KEY : 外鍵約束
  • DEFAULT : 如果未指定值,則為列設定預設值

【相關推薦:mysql影片教學

#限制

1.NULL

MySQL中透過使用NOT NULL確保列中不會出現Null值,建立表格時候格式如下:

mysql> create table user(name varchar(255)not null);
Query OK, 0 rows affected (0.06 sec)

如果試圖插入一個null值,則會拋出異常。

mysql> insert user values(null);
ERROR 1048 (23000): Column 'name' cannot be null

或在現有表上新增NOT NULL約束。

mysql> alter table user modify name varchar(255) not null;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

刪除NOT NULL約束。

mysql> alter table user modify name varchar(255)  null;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.CHECK

如果想在列上定義條件約束,可以使用CHECK,例如下面,強制讓年齡欄位大於18,小於80,否則將會報錯。

mysql> create table user(age int(11) check(age>18 and age <80));
Query OK, 0 rows affected, 1 warning (0.06 sec)

插入測試,可以發現9、81在插入的時候拋出例外。

mysql> insert user values(9);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.

mysql> insert user values(19);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(81);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql>

也可以進行多列約束,如年齡必須大於18,城市必須為中國。

mysql> create table user(age int(11),city varchar(255) ,check(age>18 and city=&#39;中国&#39;));
Query OK, 0 rows affected, 1 warning (0.05 sec)

插入測試。

mysql> insert user values(81,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(8,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;2&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(20,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(20,&#39;中国1&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values(85,&#39;中国&#39;);
Query OK, 1 row affected (0.01 sec)

mysql> insert user values(9,&#39;中国&#39;);
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.

也可以讓列值必須在指定集合中,如性別必須在男、女、未知、人妖集合中。

mysql> create table user(sex varchar(255) check (sex in (&#39;男&#39;,&#39;女&#39;,&#39;未知&#39;,&#39;人妖&#39;)));
Query OK, 0 rows affected (0.05 sec)

插入測試。

mysql> insert user values("男");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("男男");
ERROR 3819 (HY000): Check constraint &#39;user_chk_1&#39; is violated.
mysql> insert user values("女");
Query OK, 1 row affected (0.01 sec)

mysql> insert user values("人妖");
Query OK, 1 row affected (0.00 sec)

為約束命名並刪除約束。

mysql> create table user (age int(11) ,constraint CHK_AGE check(age>18));
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values(5);
ERROR 3819 (HY000): Check constraint &#39;CHK_AGE&#39; is violated.

mysql> alter table user drop check CHK_AGE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> insert user values(5);
Query OK, 1 row affected (0.01 sec)

但是,這樣的寫法你看過嗎?

猜猜看下面的作用是什麼。

這其實是一個case when條件判斷,讓其只是可以插入>=18,或是在0-10之間的數。

CREATE TABLE `user` (`age` int(11) CHECK 
(((case when (`age` >=18) then 1 
else 
(case when age<10 and age >0 then 1 else 2 end) end) =1)));

3.UNIQUE

UNIQUE約束確保列中的沒有重複的值,UNIQUE和  PRIMARY KEY約束都為一列值的唯一性提供保障,但是UNIQUE每個表可以出現多次,而PRIMARY KEY也只能出現一個。

如下面name欄位不能重複。

mysql> create table user (name varchar(255),unique(name));
Query OK, 0 rows affected (0.07 sec)

插入測試。

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name&#39;mysql>

對此約束進行起名,並刪除。

mysql> create table user (name varchar(255),constraint name_un unique(name));
Query OK, 0 rows affected (0.07 sec)

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

mysql> insert user values("张三");
ERROR 1062 (23000): Duplicate entry &#39;张三&#39; for key &#39;user.name_un&#39;
mysql> alter table user drop index name_un;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert user values("张三");
Query OK, 1 row affected (0.02 sec)

插入後可以用以下語句查看建立語句。

mysql> show create table user;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  UNIQUE KEY `name_un` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

要刪除UNIQUE約束,可以使用DROP INDEXALTER TABLE語句:

mysql> DROP INDEX name_un ON user;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在現有表上加。

mysql> alter table user add constraint name_un unique(name);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.PRIMARY KEY

通常每個表包含一個用來唯一識別每一行的值,這個欄位就稱為PRIMARY KEY。

mysql> create table user (id int(11) ,age int(11),primary key (id));
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert user values(1,2);
Query OK, 1 row affected (0.02 sec)

mysql> insert user values(1,2);
ERROR 1062 (23000): Duplicate entry &#39;1&#39; for key &#39;user.PRIMARY&#39;mysql>

5.FOREIGN KEY

FOREIGN KEY用於約束表中的一個欄位必須是另一個表中某個欄位所存在的值,但是在另一個表中,這個列不一定是主鍵,但必須是唯一性索引,否則會建立失敗。

例如orders表中的userId必須參考user表中的id,如果插入的userId在user表中不存在,則無法插入。

mysql> create table orders (id int(11) primary key ,userId int(11) ,  FOREIGN KEY (userId) REFERENCES user(id) );
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> insert orders values(1,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))

mysql> insert orders values(1,1);
Query OK, 1 row affected (0.01 sec)

但是有一個問題,如果主表(user)中記錄被刪除或更新,那麼orders中的記錄該怎麼辦? ,如下面的例子,可以發現直接報錯了。

mysql> update user set id =2 where id =1;

Cannot delete or update a parent row: a foreign key constraint fails (`t`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)

MySQL提供了幾個限制可以幫助我們解決這類問題,例如在user表更新時,orders也相繼更新。

  1. RESTRICT:如果子表中有記錄,則拒絕更新或刪除父表中的記錄。

  2. CASCADE:更新或刪除父表中的記錄時,自動更新或刪除子表中的記錄。

  3. SET NULL:在更新或刪除父表記錄時,將子表中欄位的值設為空。

可以發現,預設採用的是RESTRICT,下面來修改一下,讓在更新時候也同樣更新,在刪除時候設定null。

mysql> alter table orders add constraint orders_ibfk_1  FOREIGN KEY (`userId`) REFERENCES `user` (`id`) on update cascade on
delete set null;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

測試更新

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql> insert orders values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update user set id =2 where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |      2 |
+----+--------+
1 row in set (0.01 sec)

測試刪除。

mysql> delete from user where id =2;
Query OK, 1 row affected (0.02 sec)

mysql> select * from orders;
+----+--------+
| id | userId |
+----+--------+
|  1 |   NULL |
+----+--------+
1 row in set (0.00 sec)

6.DEFAULT

DEFAULT約束用於為列設定預設值,如果沒有為某個欄位賦值,系統就會自動為這個字段插入預設值,沒有賦值指的是在insert插入資料時沒有指明這個字段,如果指定null值,最終存放的還是null值。

mysql> create table user(age int(11) default 18);
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> insert user values();
Query OK, 1 row affected (0.02 sec)

mysql> select * from user;
+------+
| age  |
+------+
|   18 |
+------+
1 row in set (0.00 sec)

原文網址:https://juejin.cn/post/7000352993572814885

作者:i聽風夜

更多程式相關知識,請訪問:編程視頻! !

以上是深入淺析MySQL中常見的6種約束類型的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:juejin.cn。如有侵權,請聯絡admin@php.cn刪除