MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng),由瑞典MySQL AB 公司開發(fā),目前屬于 Oracle 旗下產(chǎn)品.MySQL 是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)之一,在 WEB 應用方面,MySQL是最好的 RDBMS (Relational Database Management System,關(guān)系數(shù)據(jù)庫管理系統(tǒng)) 應用軟件.

MySQL所使用的 SQL 語言是用于訪問數(shù)據(jù)庫的最常用標準化語言.MySQL 軟件采用了雙授權(quán)政策,分為社區(qū)版和商業(yè)版,由于其體積小、速度快、總體擁有成本低,尤其是開放源碼這一特點,一般中小型網(wǎng)站的開發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫.

由于其社區(qū)版的性能卓越,搭配 PHP 和 Apache 可組成良好的開發(fā)環(huán)境.

今天,尚硅谷的老師就來講一講MySQL的約束.

相關(guān)資料可訪問尚硅谷下載：http://www.atguigu.com/opensource.shtml

什么是約束?

約束即不隨意,有制約,管束.

數(shù)據(jù)完整性(Data Integrity)是指數(shù)據(jù)的精確性(Accuracy)和可靠性(Reliability).它是應防止數(shù)據(jù)庫中存在不符合語義規(guī)定的數(shù)據(jù)和防止因錯誤信息的輸入輸出造成無效操作或錯誤信息而提出的.數(shù)據(jù)的完整性要從以下四個方面考慮：

l實體完整性(Entity Integrity)：例如,同一個表中,不能存在兩條完全相同無法區(qū)分的記錄

l域完整性(Domain Integrity)：例如：年齡范圍0-120,性別范圍“男/女”

l引用完整性(Referential Integrity)：例如：員工所在部門,在部門表中要能找到這個部門

l用戶自定義完整性(User-defined Integrity)：例如：用戶名唯一、密碼不能為空等,本部門經(jīng)理的工資不得高于本部門職工的平均工資的5倍.

約束的分類

為了達到這個目標,我們要對數(shù)據(jù)庫表的設(shè)計增加約束,根據(jù)約束的作用范圍分,主要是表級約束和列級約束.

l列級約束只能作用在一個列上,跟在列的定義后面定義,與列的定義用空格分隔

l表級約束可以作用在多個列上,不與列一起,而是單獨定義,格式：[ CONSTRAINT <約束名> ] <約束類型>

根據(jù)約束的特點,分為幾種：

l鍵約束：主鍵約束、外鍵約束、唯一鍵約束

lNot NULL約束：非空約束

lCheck約束：檢查約束

lDefault約束：缺省約束

鍵約束之主鍵約束

1、主鍵：Primary key,簡稱PK,數(shù)據(jù)庫主鍵作用保證實體的完整性,可以是一個列或多列的組合.

l主鍵約束相當于唯一約束+非空約束的組合,主鍵約束列不允許重復,也不允許出現(xiàn)空值,如果是多列組合的主鍵約束,那么這些列都不允許為空值,并且組合的值不允許重復.

l每個表最多只允許一個主鍵約束,建立主鍵約束可以在列級別創(chuàng)建,也可以在表級別上創(chuàng)建.

lMySQL的主鍵名總是PRIMARY.

l當創(chuàng)建主鍵約束時,MySQL默認會在所在的列和列組合上建立對應的主鍵索引.

l刪除主鍵時,也會直接刪除主鍵索引

鍵約束之唯一鍵約束

2、唯一鍵：Unique key,簡稱UK,

l同一個表可以有多個唯一約束.

l唯一約束可以是某一個列的值唯一,也可以多個列組合值的唯一.

l建立唯一約束可以在列級別創(chuàng)建,也可以在表級別上創(chuàng)建.

l在創(chuàng)建唯一約束的時候,如果不給唯一約束名稱,會有默認名.

lMySQL會給唯一約束的列上默認創(chuàng)建一個唯一索引.

l刪除唯一鍵只能通過刪除唯一索引的方式刪除

主鍵約束與唯一鍵約束的區(qū)別?

一個表只能有一個主鍵,可以有0~n個唯一鍵

主鍵必須非空,而唯一鍵可以為空

鍵約束之外鍵約束

3、外鍵：Foreign key,簡稱FK

l外鍵約束是保證一個或兩個表之間的參照完整性,外鍵是構(gòu)建于一個表的兩個字段或是兩個表的兩個字段之間的參照關(guān)系.

l建立外鍵約束只能在表級別上創(chuàng)建.

l在創(chuàng)建外鍵約束時,如果不給外鍵約束名稱,外鍵也有默認名,也可以指定外鍵名.

l當創(chuàng)建外鍵約束時,系統(tǒng)默認會在所在的列上建立對應的普通索引.

l刪除外鍵時,關(guān)于外鍵列上的普通索引需要單獨刪除.

注意：

l外鍵是在從表中聲明

l從表的外鍵列,在主表中引用的只能是主鍵或唯一鍵約束的列.

l從表的外鍵列與主表被參照的列名字可以不相同,但是數(shù)據(jù)類型必須一樣

l從表的外鍵值必須"在主表中能找到"或者為空,從而約束了從表的外鍵值.

l當主表的記錄被從表參照時,主表中被參考記錄的刪除和更新也會受到限制.

n(1)默認情況下,主表和從表是嚴格依賴關(guān)系RESTRICT.當主表的記錄被從表參照時,主表的記錄將不允許刪除,如果要刪除數(shù)據(jù),需要先刪除從表中依賴該記錄的數(shù)據(jù),然后才可以刪除主表的數(shù)據(jù).

n(2)但是有一種是級聯(lián)刪除：

nON DELETE SET NULL(級聯(lián)置空)：當外鍵設(shè)置了SET NULL,當主表的相關(guān)記錄刪除時,從表對應的字段改為NULL.

nON DELETE CASCADE(級聯(lián)刪除)：當外鍵設(shè)置了CASCADE(級聯(lián)),當主表的相關(guān)記錄刪除時,從表對應的行都刪除了.

n對于外鍵約束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

例如：

在成績表中有兩個外鍵,學號外鍵參考學生表的學號,課程編號外鍵參考課程表.

例如：

請點擊此處輸入圖片描述請點擊此處輸入圖片描述

在t_emp員工表中也有兩個外鍵,t_emp員工表的department_id(員工所屬部門)參考t_dept部門表的did,t_emp員工表的manager_id(領(lǐng)導編號)的參考t_emp員工表的eid(員工編號).

非空約束

lNOT NULL 非空約束,規(guī)定某個字段不能為空

l非空約束只能出現(xiàn)在表對象的列上,即只能是列級約束.

檢查約束

注意： MySQL不支持check約束,但可以使用check約束,而沒有任何效果；

例如：age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

Default缺省約束

default：默認值,在插入數(shù)據(jù)時某列如果沒指定其他的值,那么會將默認值添加到所有的新記錄.

約束的創(chuàng)建與刪除

(一)建表時聲明約束

#主鍵和唯一鍵可以使用列級約束聲明方式,但外鍵只能使用表級約束聲明方式

create table 表名(

字段1 數(shù)據(jù)類型 【primary key】,

字段2 數(shù)據(jù)類型【unique】【not null】【default 默認值】,

字段3 數(shù)據(jù)類型【unique】【not null】【default 默認值】,

【constraint FK_從表_主表1】foreign key (從表的字段) references 主表1(對應字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】,

【constraint FK_從表_主表2】foreign key (從表的字段) references 主表2(對應字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】

);

#主鍵和唯一鍵可以使用表級約束聲明方式

create table 表名(

字段1 數(shù)據(jù)類型,

字段2 數(shù)據(jù)類型【not null】【default 默認值】,

字段3 數(shù)據(jù)類型【not null】【default 默認值】,

【constraint PK_字段】 primary key (字段),

【constraint uk_字段2】 unique(字段2),

【constraint uk_字段3】 unique(字段3),

【constraint FK_從表_主表1】foreign key (從表的字段) references 主表1(對應字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】,

【constraint FK_從表_主表2】foreign key (從表的字段) references 主表2(對應字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】

);

#注意：如果是復合主鍵,復合唯一鍵,必須使用表級約束定義

create table 表名(

字段1 數(shù)據(jù)類型,

字段2 數(shù)據(jù)類型,

字段3 數(shù)據(jù)類型,

字段4 數(shù)據(jù)類型,

字段5 數(shù)據(jù)類型【not null】【default 默認值】,

【constraint PK_字段1_字段2】 primary key (字段1,字段2),

【constraint uk_字段3_字段4】 unique(字段3,字段4),

【constraint uk_字段5】 unique(字段5),

【constraint FK_從表_主表1】foreign key (從表的字段) references 主表1(對應字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】,

【constraint FK_從表_主表2】foreign key (從表的字段) references 主表2(對應字段) 【on delete cascade/set null/RESTRICT】【on update cascade/set null/RESTRICT】

);

(二)如果表已經(jīng)創(chuàng)建完畢,需要建立約束、刪除約束

(1)增加主鍵約束

alter table 表名稱 add 【constraint 約束名】 primary key (字段名);

alter table 表名稱 add 【constraint 約束名】 primary key (字段名1,字段名2);#復合主鍵

(2)增加唯一鍵約束

alter table表名稱 add 【constraint 約束名】 unique 【key】 (字段名);

alter table表名稱 add 【constraint 約束名】 unique 【key】 (字段名1,字段名2);

(3)增加外鍵約束

alter table表名稱 add 【constraint 約束名】 foreign key (從表字段名) references 主表名(主表被參照字段名);

(4)增加/刪除缺省約束或非空約束

alter table 表名 modify 【column】 列名 數(shù)據(jù)類型 【default 默認值】 【not null】;

(三)刪除約束

#鍵約束只能通過這種方式刪除,不能通過modify/change列來刪除

(1)如何查看一個表的約束信息：

#如果不知道要刪除的約束名,可以通過SHOW CREATE TABLE 表名;查看 或

#select * from information_schema.table_constraints where table_name = '表名稱';

(2)如何查看一個表的索引

show index from表名稱;

(3)刪除主鍵約束,不需要指定主鍵名,一個表只有一個主鍵

alter table表名稱drop primary key;

刪除主鍵約束,也會把主鍵上的索引刪除

(4)刪除唯一鍵約束

#唯一鍵只能通過刪除索引的方式刪除

alter table 表名稱 drop index 唯一鍵的索引名;

(5)刪除外鍵約束

alter table 表名稱 drop foreign key 約束名;

刪除外鍵約束,不會把外鍵上的索引刪除,需要再單獨刪除索引

約束與索引

索引：索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結(jié)構(gòu).

索引是一個單獨的、物理的數(shù)據(jù)庫結(jié)構(gòu),它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數(shù)據(jù)頁的邏輯指針清單.由此可知,索引是要消耗數(shù)據(jù)庫空間的.而約束是一種邏輯概念.

Mysql索引