常見的約束類型有哪些(MySQL中常見的約束類型是什么)

約束的字面意思就是規定或者限制某個事該如何去做,在MySQL中,約束就是對數據表中數據指定規則,也就是對數據進行限制,以此來確?？煽啃?比如不允許某列出現Null值,實際中我們會遇到以下類型的約束。

• NOT NULL : 確保列不能有NULL值

• CHECK : 確保列中的值滿足特定條件

• UNIQUE : 確保一列中的所有值都不同

• PRIMARY KEY: NOT NULL和UNIQUE組合,唯一標識表中的每一行

• FOREIGN KEY : 外鍵約束

• DEFAULT : 如果未指定值,則為列設置默認值

約束

1.NULL

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

mysql>createtableuser(namevarchar(255)notnull);QueryOK,0rowsaffected(0.06sec)

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

mysql>insertuservalues(null);ERROR1048(23000):Column'name'cannotbenull

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

mysql>altertableusermodifynamevarchar(255)notnull;QueryOK,0rowsaffected(0.07sec)Records:0Duplicates:0Warnings:0

刪除NOT NULL約束。

mysql>altertableusermodifynamevarchar(255)null;QueryOK,0rowsaffected(0.09sec)Records:0Duplicates:0Warnings:0

2.CHECK

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

mysql>createtableuser(ageint(11)check(age>18andage<80));QueryOK,0rowsaffected,1warning(0.06sec)

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

mysql>insertuservalues(9);ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(19);QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(81);ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>

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

mysql>createtableuser(ageint(11),cityvarchar(255),check(age>18andcity='中國'));QueryOK,0rowsaffected,1warning(0.05sec)

插入測試。

mysql>insertuservalues(81,'2');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(8,'2');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(20,'2');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(20,'中國');QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(20,'中國1');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues(85,'中國');QueryOK,1rowaffected(0.01sec)mysql>insertuservalues(9,'中國');ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.

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

mysql>createtableuser(sexvarchar(255)check(sexin('男','女','未知','人妖')));QueryOK,0rowsaffected(0.05sec)

插入測試。

mysql>insertuservalues("男");QueryOK,1rowaffected(0.02sec)mysql>insertuservalues("男男");ERROR3819(HY000):Checkconstraint'user_chk_1'isviolated.mysql>insertuservalues("女");QueryOK,1rowaffected(0.01sec)mysql>insertuservalues("人妖");QueryOK,1rowaffected(0.00sec)

為約束命名并刪除約束。

mysql>createtableuser(ageint(11),constraintCHK_AGEcheck(age>18));QueryOK,0rowsaffected,1warning(0.05sec)mysql>insertuservalues(5);ERROR3819(HY000):Checkconstraint'CHK_AGE'isviolated.mysql>altertableuserdropcheckCHK_AGE;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>insertuservalues(5);QueryOK,1rowaffected(0.01sec)

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

猜猜下面的作用是什么。

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

CREATETABLE`user`(`age`int(11)CHECK(((casewhen(`age`>=18)then1else(casewhenage<10andage>0then1else2end)end)=1)));

3.UNIQUE

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

如下面name字段不能重復。

mysql>createtableuser(namevarchar(255),unique(name));QueryOK,0rowsaffected(0.07sec)

插入測試。

mysql>insertuservalues("張三");QueryOK,1rowaffected(0.02sec)mysql>insertuservalues("張三");ERROR1062(23000):Duplicateentry'張三'forkey'user.name'mysql>

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

mysql>createtableuser(namevarchar(255),constraintname_ununique(name));QueryOK,0rowsaffected(0.07sec)mysql>insertuservalues("張三");QueryOK,1rowaffected(0.02sec)mysql>insertuservalues("張三");ERROR1062(23000):Duplicateentry'張三'forkey'user.name_un'mysql>altertableuserdropindexname_un;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>insertuservalues("張三");QueryOK,1rowaffected(0.02sec)

插入后可以用以下語句查看創建語句。

mysql>showcreatetableuser;+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+|user|CREATETABLE`user`(`name`varchar(255)DEFAULTNULL,UNIQUEKEY`name_un`(`name`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

要刪除UNIQUE約束,可以使用drop INDEX或ALTER TABLE語句:

mysql>DROPINDEXname_unONuser;QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0mysql>showcreatetableuser;+-------+-----------------------------------------------------------------------------------------------------------------------------+|Table|CreateTable|+-------+-----------------------------------------------------------------------------------------------------------------------------+|user|CREATETABLE`user`(`name`varchar(255)DEFAULTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+-----------------------------------------------------------------------------------------------------------------------------+1rowinset(0.00sec)

在現有表上添加。

mysql>altertableuseraddconstraintname_ununique(name);QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0

4.PRIMARY KEY

通常每個表中包含一個用于唯一標識每一行的值,這個列就被稱為PRIMARY KEY。

mysql>createtableuser(idint(11),ageint(11),primarykey(id));QueryOK,0rowsaffected,2warnings(0.06sec)mysql>insertuservalues(1,2);QueryOK,1rowaffected(0.02sec)mysql>insertuservalues(1,2);ERROR1062(23000):Duplicateentry'1'forkey'user.PRIMARY'mysql>

5.FOREIGN KEY

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

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

mysql>createtableorders(idint(11)primarykey,userIdint(11),FOREIGNKEY(userId)REFERENCESuser(id));QueryOK,0rowsaffected,2warnings(0.06sec)mysql>insertordersvalues(1,3);ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`t`.`orders`,CONSTRAINT`orders_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`))mysql>insertordersvalues(1,1);QueryOK,1rowaffected(0.01sec)

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

mysql>updateusersetid=2whereid=1;Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`t`.`orders`,CONSTRAINT`orders_ibfk_1`FOREIGNKEY(`userId`)REFERENCES`user`(`id`)

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

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

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

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

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

mysql>altertableordersaddconstraintorders_ibfk_1FOREIGNKEY(`userId`)REFERENCES`user`(`id`)onupdatecascadeondeletesetnull;QueryOK,0rowsaffected(0.12sec)Records:0Duplicates:0Warnings:0

測試更新

mysql>select*fromuser;+----+--------+|id|name|+----+--------+|1|張三|+----+--------+1rowinset(0.00sec)mysql>select*fromorders;Emptyset(0.00sec)mysql>insertordersvalues(1,1);QueryOK,1rowaffected(0.01sec)mysql>updateusersetid=2whereid=1;QueryOK,1rowaffected(0.01sec)Rowsmatched:1Changed:1Warnings:0mysql>select*fromorders;+----+--------+|id|userId|+----+--------+|1|2|+----+--------+1rowinset(0.01sec)

測試刪除。

mysql>deletefromuserwhereid=2;QueryOK,1rowaffected(0.02sec)mysql>select*fromorders;+----+--------+|id|userId|+----+--------+|1|NULL|+----+--------+1rowinset(0.00sec)

6.DEFAULT

DEFAULT約束用于為列設置默認值,如果沒有為某個字段賦值,系統就會自動為這個字段插入默認值,沒有賦值指的是在insert插入數據時沒有指明這個字段,如果指定null值,最終存放的還是null值。

mysql>createtableuser(ageint(11)default18);QueryOK,0rowsaffected,1warning(0.05sec)mysql>insertuservalues();QueryOK,1rowaffected(0.02sec)mysql>select*fromuser;+------+|age|+------+|18|+------+1rowinset(0.00sec)

感謝各位的閱讀!關于“MySQL中常見的約束類型是什么”這篇文章就分享到這里了,希望以上內容可以對大家有一定的幫助,讓大家可以學到更多知識,如果覺得文章不錯,可以把它分享出去讓更多的人看到吧!