數(shù)據(jù)完整性指的是數(shù)據(jù)的一致性和正確性。完整性約束是指數(shù)據(jù)庫的內(nèi)容必須隨時遵守的規(guī)則。若定義了數(shù)據(jù)完整性約束，MySQL會負責數(shù)據(jù)的完整性，每次更新數(shù)據(jù)時，MySQL都會測試新的數(shù)據(jù)內(nèi)容是否符合相關的完整性約束條件，只有符合完整性的約束條件的更新才被接受。

1、主鍵約束

主鍵就是表中的一列或多個列的組合，其值能唯一地標識表中的每一行。MySQL為主鍵列創(chuàng)建唯一性索引，實現(xiàn)數(shù)據(jù)的唯一性。在查詢中使用主鍵時，該索引可用來對數(shù)據(jù)進行快速訪問。通過定義PRIMARY KEY約束來創(chuàng)建主鍵，而且PRIMARY KEY約束中的列不能取空值。如果PRIMARY KEY約束是由多列組合定義的，則某一列的值可以重復，但PRIMARY KEY約束定義中所有列的組合值必須是唯一的。

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

例：創(chuàng)建表book_copy，將書名定義為主鍵

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

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

創(chuàng)建course表來記錄每門課程的學生學號、姓名、課程號和學分。其中學號、課程號構(gòu)成復合主鍵

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

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

1、每個表只能定義一個主鍵。關系模型理論要求必須為每個表定義一個主鍵。然而，MySQL并不要求這樣，即可以創(chuàng)建一個沒有主鍵的表。但是，從安全角度應該為每個基本表指定一個主鍵。主要原因在于，沒有主鍵，可能在一個表中存儲兩個相同的行。當兩個行不能彼此區(qū)分時，在查詢過程中，它們將會滿足同樣的條件，更新的時候也總是一起更新，容易造成數(shù)據(jù)庫奔潰。

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

3、如果從一個復合主鍵中刪除一列后，剩下的列構(gòu)成主鍵仍然滿足唯一性原則，那么，該復合主鍵是不正確的，這條規(guī)則稱為最小化規(guī)則。也就是說，復合主鍵不應該包含不必要的列。

4、一個列名在一個主鍵的列表中只能出現(xiàn)一次。

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

例：創(chuàng)建course表來記錄每門課程的學生學號、姓名、課程號和學分。其中學號、課程號構(gòu)成復合主鍵，將主鍵創(chuàng)建的索引命名為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中替代鍵和主鍵的區(qū)別主要有以下幾點：

1、一個數(shù)據(jù)表只能創(chuàng)建一個主鍵。但一個表可以有若干個UNIQUE鍵，并且他們甚至可以重合，例如，在C1和C2列上定義了一個替代鍵，并且在C2和C3列上定義了另一個替代鍵，這兩個替代鍵在C2列上重合了，這是MySQL允許的。

2、主鍵字段的值不允許為NULL，而UNIQUE 字段的值可以是NULL，但必須使用NULL或NOT NULL聲明。

3、創(chuàng)建PRIMARY KEY約束時，系統(tǒng)自動產(chǎn)生PRIMARY KEY索引。創(chuàng)建UNIQUE約束時，系統(tǒng)自動產(chǎn)生UNIQUE索引。

3、參照完整性約束

只有圖書目錄表中有的圖書才可以銷售，因此，在Sell表中的所有圖書必須是Book表有的圖書，也就是說存儲在Sell表中的所有圖書編號必須存在于Book表的圖書編號列中。同樣Sell表中的所有身份證號也必須出現(xiàn)在Members表的身份證號列中。這種類型的關系就是參照完整性約束。參照完整性約束都是一種特殊的完整性約束，實現(xiàn)為一個外鍵。所以Sell表中的圖書編號列和身份證號列都可以定義為一個外鍵。可以在創(chuàng)建表或修改表時定義一個外鍵聲明

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

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

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

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

MySQL參照完整性約束目前只可以用在那些使用InnoDB存儲引擎創(chuàng)建的表中，對于其他類型的表，MySQL服務器能夠解析CREATE TABLE語句中的FOREIGN KEY語法，但不能使用或保存它。

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

例：創(chuàng)建book_ref表，所有的book_ref表中圖書編號都必須出現(xiàn)在Book表中，假設已經(jīng)使用圖書編號列作為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;

當指定一個外鍵時，適用以下規(guī)則：

1、被參照表必須已經(jīng)用1條CREATE TABLE語句創(chuàng)建了，或者必須是當前正在創(chuàng)建的表。在后一種情況下，參照表是同一個表。

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

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

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

5、外鍵中列的數(shù)目必須和被參照表的主鍵列的數(shù)目相同

6、外中列的數(shù)據(jù)類型必須和被參照表的主鍵中列的數(shù)據(jù)類型相同

例：創(chuàng)建帶有參照動作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完整性約束

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

CHECK完整性約束在創(chuàng)建表的時候定義?？梢远x為列完整性約束，也可以定義為表完整性約束。

語法格式：CHECK(表達式）

例：創(chuàng)建表student，只考慮學號和性別兩列，性別只能包含男或女 

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

例：創(chuàng)建表student，只考慮學號和出生日期兩列，出生日期必須大于1980年1月1日

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

到此這篇關于MySQL學習之完整性約束詳解的文章就介紹到這了,更多相關MySQL完整性約束內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論