MySQL學習之完整性約束詳解

資料完整性指的是資料的一致性和正確性。完整性約束是指資料庫的內容必須隨時遵守的規則。若定義了資料完整性約束，MySQL會負責資料的完整性，每次更新資料時，MySQL都會測試新的資料內容是否符合相關的完整性約束條件，只有符合完整性的約束條件的更新才被接受。

1、主鍵約束

主鍵就是表中的一列或多個列的組合，其值能唯一地標識表中的每一行。MySQL為主鍵列建立唯一性索引，實現資料的唯一性。在查詢中使用主鍵時，該索引可用來對資料進行快速存取。通過定義PRIMARY KEY約束來建立主鍵，而且PRIMARY KEY約束中的列不能取空值。如果PRIMARY KEY約束是由多列組合定義的，則某一列的值可以重複，但PRIMARY KEY約束定義中所有列的組合值必須是唯一的。

可以使用兩種方式定義主鍵來作為列或表的完整性約束。作為列的完整性約束時，只需在列定義的時候加上關鍵字PRIMARY KEY。作為表的完整性約束時，需要在語句最後加上一條PRIMARY KEY（col_name,...）語句。

例：建立表book_copy，將書名定義為主鍵

CREATE TABLE book_copy
(圖書編號 varchar(6) NULL,
書名 varchar(20) NOT NULL PRIMARY KEY,
出版日期 date
);

當表中的主鍵為複合主鍵時，只能定義為表的完整性約束。

建立course表來記錄每門課程的學生學號、姓名、課程號和學分。其中學號、課程號構成複合主鍵

CREATE TABLE course
(學號 varchar(6) NOT NULL,
姓名 varchar(8) NOT NULL,
課程號 varchar(3),
學分 tinyint,
PRIMARY KEY(學號，課程名)
);

原則上，任何列或者列的組合都可以充當一個主鍵。但是主鍵列必須遵守一些規則：

1、每個表只能定義一個主鍵。關係模型理論要求必須為每個表定義一個主鍵。然而，MySQL並不要求這樣，即可以建立一個沒有主鍵的表。但是，從安全形度應該為每個基本表指定一個主鍵。主要原因在於，沒有主鍵，可能在一個表中儲存兩個相同的行。當兩個行不能彼此區分時，在查詢過程中，它們將會滿足同樣的條件，更新的時候也總是一起更新，容易造成資料庫奔潰。

2、表中兩個不同的行在主鍵上不能具有相同的值，這就是唯一性規則。

3、如果從一個複合主鍵中刪除一列後，剩下的列構成主鍵仍然滿足唯一性原則，那麼，該複合主鍵是不正確的，這條規則稱為最小化規則。也就是說，複合主鍵不應該包含不必要的列。

4、一個列名在一個主鍵的列表中只能出現一次。

MySQL自動地為主鍵建立一個索引。通常，這個索引名為PRIIMARY。不過，也可以重新給改索引另起名。

例：建立course表來記錄每門課程的學生學號、姓名、課程號和學分。其中學號、課程號構成複合主鍵，將主鍵建立的索引命名為INDEX_C

CREATE TABLE course
(學號 varchar(6) NOT NULL,
姓名 varchar(8) NOT NULL,
課程號 varchar(3),
學分 tinyint,
PRIMARY KEY INDEX_C(學號，課程名)
);

2、替代鍵約束

替代鍵像主鍵一樣，是表的一列或一組列，他們的值在任何時候都是唯一的。替代鍵是沒有被選做主鍵的候選鍵。定義替代鍵的關鍵字是UNIQUE

例：在表book中將圖書編號作為主鍵，書名列定義為一個替代鍵。 

CREATE TABLE book
(
圖書編號 varchar(20) NOT NULL,
書名 varchar(20) NOT NULL UNIQUE,
PRIMARY KEY(圖書編號)
); 

在MySQL中替代鍵和主鍵的區別主要有以下幾點：

1、一個資料表只能建立一個主鍵。但一個表可以有若干個UNIQUE鍵，並且他們甚至可以重合，例如，在C1和C2列上定義了一個替代鍵，並且在C2和C3列上定義了另一個替代鍵，這兩個替代鍵在C2列上重合了，這是MySQL允許的。

2、主鍵欄位的值不允許為NULL，而UNIQUE 欄位的值可以是NULL，但必須使用NULL或NOT NULL宣告。

3、建立PRIMARY KEY約束時，系統自動產生PRIMARY KEY索引。建立UNIQUE約束時，系統自動產生UNIQUE索引。

3、參照完整性約束

只有圖書目錄表中有的圖書才可以銷售，因此，在Sell表中的所有圖書必須是Book表有的圖書，也就是說儲存在Sell表中的所有圖書編號必須存在於Book表的圖書編號列中。同樣Sell表中的所有身份證號也必須出現在Members表的身份證號列中。這種型別的關係就是參照完整性約束。參照完整性約束都是一種特殊的完整性約束，實現為一個外來鍵。所以Sell表中的圖書編號列和身份證號列都可以定義為一個外來鍵。可以在建立表或修改表時定義一個外來鍵宣告

定義外來鍵的語法格式：REFERENCES 表名 [ ( 列名 | (長度）] [ ASC | DESC ],...) ]

[ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]

[ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]

外來鍵被定義為表的完整性約束，語法中包含了外來鍵所參照的表和列，還可以宣告參照動作。如果沒有指定動作，兩個參照動作就會預設地使用RESTRICT。

MySQL參照完整性約束目前只可以用在那些使用InnoDB儲存引擎建立的表中，對於其他型別的表，MySQL伺服器能夠解析CREATE TABLE語句中的FOREIGN KEY語法，但不能使用或儲存它。

要修改表的儲存引擎，可以採用ALTER TABLE語句。例如，修改Book表的儲存引擎為InnoDB，使用：ALTER TABLE book ENGINE=INNODB;

例：建立book_ref表，所有的book_ref表中圖書編號都必須出現在Book表中，假設已經使用圖書編號列作為Book表主鍵。

CREATE TABLE book_ref
(
圖書編號 varchar(20) null,
書名 varchar(20) null,
出版日期 date null,
PRIMARY KEY（書名）,
FOREIGN KEY(圖書編號)
REFERENCES Book(圖書編號)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)ENGINE=INNODB;

當指定一個外來鍵時，適用以下規則：

1、被參照表必須已經用1條CREATE TABLE語句建立了，或者必須是當前正在建立的表。在後一種情況下，參照表是同一個表。

2、必須為被參照表定義主鍵

3、必須在被參照表的表名後面指定列名（或列名的組合）。該列（或該列組合）必須是這個表的主鍵或替代鍵。

4、儘管主鍵不能夠包含空值，但允許在外來鍵中出現一個空值。這意味著，只要外來鍵的每個非空值出現在指定的主鍵中，該外來鍵的內容就是正確的。

5、外來鍵中列的數目必須和被參照表的主鍵列的數目相同

6、外中列的資料型別必須和被參照表的主鍵中列的資料型別相同

例：建立帶有參照動作CASCADE的book_refl表 

CREATE TABLE book_refl
(
圖書編號 varchar(20) null,
書名 varchar(20) not null,
出版日期 date null,
PRIMARY KEY(書名),
FOREIGN KEY(圖書編號)
REFERENCES Book(圖書編號)
ON UPDATE CASCADE
)ENGINE=INNODB;

4、CHECK完整性約束

主鍵、替代鍵和外來鍵都是常見的完整性約束的例子。但是，每個資料庫都還有一些專用的完整性約束。例如，Sell表中訂購冊數要在1~5000之間，Book表中出版時間必須大於1986年1月1日。這樣的規則可以使用CHECK完整性約束來指定。

CHECK完整性約束在建立表的時候定義。可以定義為列完整性約束，也可以定義為表完整性約束。

語法格式：CHECK(表示式）

例：建立表student，只考慮學號和性別兩列，性別只能包含男或女 

CREATE TABLE student
(
學號 char(6) not null,
性別 char(2) not null,
CHECK(性別 IN('男','女'))
);

例：建立表student，只考慮學號和出生日期兩列，出生日期必須大於1980年1月1日

CREATE TABLE student
(
學號 char(6) not null,
出生日期 date not null
CHECK(出生日期>'1980-01-01')
);

到此這篇關於MySQL學習之完整性約束詳解的文章就介紹到這了,更多相關MySQL完整性約束內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！