深入淺析MySQL從刪庫到跑路_高級（一）——數(shù)據(jù)完整性

一、數(shù)據(jù)完整性簡介

1、數(shù)據(jù)完整性簡介

數(shù)據(jù)冗余是指數(shù)據(jù)庫中存在一些重復的數(shù)據(jù)，數(shù)據(jù)完整性是指數(shù)據(jù)庫中的數(shù)據(jù)能夠正確反應實際情況。

數(shù)據(jù)完整性是指數(shù)據(jù)的可靠性和準確性，數(shù)據(jù)完整性類型有四種：

A、實體完整性：實體的完整性強制表的標識符列或主鍵的完整性（通過唯一約束，主鍵約束或標識列屬性）。

B、域完整性：限制類型（數(shù)據(jù)類型），格式（通過檢查約束和規(guī)則），可能值范圍（通過外鍵約束，檢查約束，默認值定義，非空約束和規(guī)則）。

C、引用完整性：在刪除和輸入記錄時，引用完整性保持表之間已定義的關系。引用完整性確保鍵值在所有表中一致，不能引用不存在的值。如果一個鍵。

D、定義完整性：用戶自己定義的業(yè)務規(guī)則，比如使用觸發(fā)器實現(xiàn)自定義業(yè)務規(guī)則。

2、數(shù)據(jù)完整性實現(xiàn)方式

MySQL不支持Check約束，雖然可以在列上添加check約束，但不起作用。

二、實體完整性實現(xiàn)

1、實體完整性的實現(xiàn)簡介

實體完整性的實現(xiàn)有兩種方式：

A、主鍵約束：一張表只能有一列設置主鍵，值必須唯一，不允許為空，innoDB存儲引擎，主鍵就是索引。

B、唯一值約束：一張表可以有多個列添加唯一值約束，一直允許一條記錄為空值。

實體完整性，由主鍵和唯一性約束來實現(xiàn)，確保表中記錄有一列唯一標識。主鍵又分為Primary key和AUTO_INCREMENT PRIMARY KEY兩種。

2、主鍵

MySQL的主鍵名總是PRIMARY，當創(chuàng)建主鍵約束時，如果表的存儲引擎是innoDB，系統(tǒng)默認會在所在的列和列組合上建立對應的唯一索引，

主鍵約束相當于唯一約束與非空約束的組合，主鍵約束列不允許重復，也不允許出現(xiàn)空值；多列組合的主鍵約束，列都不允許為空值，并且組合的值不允許重復。每個表最多只允許一個主鍵，建立主鍵約束可以在列級別創(chuàng)建，也可以在表級別上創(chuàng)建。

A、創(chuàng)建表時指定主鍵

創(chuàng)建表時指定主鍵的方式一：

create table product
 (
 productID int PRIMARY KEY,
 pName VARCHAR(10),
 price DOUBLE
 )ENGINE=MyISAM default CHARSET=utf8;

創(chuàng)建表時指定主鍵的方式二：

create table product
 (
 productID int,
 pName VARCHAR(10),
 price DOUBLE, CONSTRAINT pk_s_productID PRIMARY KEY(productID)
 )ENGINE=MyISAM default CHARSET=utf8;

在指定主鍵的表中插入記錄時，不允許插入重復ID，如果不指定主鍵的值，默認為0。

MylSAM類型的存儲引擎不會在主鍵列上創(chuàng)建索引，表中記錄的存儲順序與插入順序相同。

InnoDB存儲引擎會自動在主鍵列上創(chuàng)建索引，插入的記錄會根據(jù)主鍵的值的順序排放。

alter table product ENGINE=InnoDB;

B、增加主鍵

alter table TStudent add primary key(studentid);

C、刪除主鍵

alter table TStudent drop primary key;

3、自增主鍵

AUTO_INCREMENT PRIMARY KEY

如果不指定主鍵值，會自動在現(xiàn)有的主鍵值的最大值上自動增加1作為新記錄的主鍵，主鍵值默認從1開始。可以在數(shù)據(jù)數(shù)據(jù)類型整數(shù)型的列上添加自增主鍵。

A、創(chuàng)建表時指定自增自增列

create table product
 (
 productID int PRIMARY KEY AUTO_INCREMENT not NULL,
 pName VARCHAR(10),
 price DOUBLE
 )ENGINE=MyISAM default CHARSET=utf8;

B、為現(xiàn)有的表指定自增列

alter table TStudent modify column studentID int PRIMARY KEY AUTO_INCREMENT;

C、刪除表中自增列

alter table TStudent modify column studentID int not NULL;

刪除自增列，仍然時主鍵，但是沒有自增長功能

4、復合主鍵

使用表的兩列或多列創(chuàng)建主鍵。

A、創(chuàng)建表時指定復合主鍵

create table student
 (
 studentID int, id INT,
 sname VARCHAR(10),
 score int,
 PRIMARY KEY(studentid,id)
 )ENGINE=MyISAM default CHARSET=utf8;

B、給表增加復合主鍵

alter table student add PRIMARY KEY(studentID,id)；

C、刪除復合主鍵

alter table student drop PRIMARY KEY;

5、唯一約束

UNIQUE KEY，唯一約束，指定某列和幾列組合的數(shù)據(jù)不能重復。

A、創(chuàng)建表時指定唯一性約束

create table score
 (sname VARCHAR(10) UNIQUE,
 score int not NULL
 );

B、給現(xiàn)有列增加唯一性約束

alter table score add CONSTRAINT us_sname UNIQUE(sname)；

如果表中現(xiàn)有記錄有重復值，不允許添加唯一性約束?？梢酝ㄟ^聚合函數(shù)，查找有重復的記錄，刪除，再創(chuàng)建唯一性約束。

C、創(chuàng)建復合唯一性索引

create table student
 (
 studentID int, id INT,
 sname VARCHAR(10),
 score int, CONSTRAINT uc_id UNIQUE(studentID, id)
 )ENGINE=MyISAM default CHARSET=utf8;

D、刪除列的唯一性約束

alter table score drop index uc_sname;

三、域完整性

1、默認值

在表中插入一條新1的記錄時，如果沒有為該字段賦值，那么數(shù)據(jù)庫系統(tǒng)會自動為該字段賦一條默認值。

create table st
(sid INT not null primary key auto_increment,
sname varchar(10),
subject varchar(20) default '軟件工程',
entertime TIMESTAMP default now()
);

給表中一列添加默認值約束：

alert table st modify column subject VARCHAR(20) default '計算機科學與技術'；

刪除表中一列的默認值約束：

alert table st modify column subject VARCHAR(20) default NULL;

2、創(chuàng)建非空約束

非空約束用于確保當前列的值不為空值，非空約束只能出現(xiàn)在表對象的列上。

Null類型特征：所有的類型的值都可以是null，包括int、float等數(shù)據(jù)類型 空字符串是不等于NULL，0也不等于NULL。

A、創(chuàng)建表時給列指定非空約束

create table score
 (sname VARCHAR(10) not NULL,
 score int not NULL
 );

B、給指定列指定非空約束

alert table score modify column score int not NULL;

C、刪除非空約束

alter table score modify column score int;

3、檢查check

check關鍵字，在插入新行或者更改已有行時才起作用，作用是阻止不滿足條件的值進入該列，對null值無效，因為插入null就相當于沒有插入。一個列可有多個check。

age int check(age between 10 and 20);

目前MySQL不支持check約束，微軟MSSQL支持Check約束，但創(chuàng)建表時可以指定Check約束，但不起作用。

四、參照完整性

1、參照完整性簡介

MySQL參照完整性一般是通過MySQL外鍵（foreign key）實現(xiàn)的。

外鍵（僅innoDB支持）所引用表的列必須是主鍵。

外鍵聲明包括三個部分：

A、哪個列或列組合是外鍵

B、指定外鍵參照的表和列

C、參照動作[cascade(級聯(lián)操作)，restrict(拒絕操作)，set null(設為空)，no action，set default]。

如果外鍵約束指定了參照動作，主表記錄做修改，刪除，從表引用的列會做相應修改，或不修改，拒絕修改或設置為默認值。

引用表的列名必須是主鍵，且在刪除引用表時必須刪除引用關系或者刪除當前表。

2、創(chuàng)建表時指定外鍵

創(chuàng)建兩張表，學生表student和成績表score，成績表的sid列的取值參照學生表（學生表student的sid列設置為主鍵，且表的存儲引擎為innodb,成績表score的存儲引擎也必須設置為innodb）。

create table student
(sid int not null primary key,
sname varchar(20)
) engine=innodb;create table score
(sid int not null,
mark INT,constraint score_fk FOREIGN KEY (sid)references student(sid) on delete cascade on update cascade) engine=innodb;

在學生表插入一條記錄

insert into student values (1,'孫悟空')

在成績表插入一條記錄，學號是1，成功。

instert into score values (1,98)

在成績表插入一條記錄，學號是2，失敗。

insert into score values (2,88)

在學生表插入學號是2的一條記錄

insert into student values (2,'唐僧')

再在成績表插入一條學號是2的記錄，成功，證明外鍵參照成功。

insert into score values (2,88);

3、刪除參照約束

alter table score drop foreign key score_fk;

4、給現(xiàn)有表增加參照約束

alter table score add constraint score_fk2 foreing key (sid) references student (sid);

5、驗證級聯(lián)動作刪除和更新

在score表創(chuàng)建的參照完整性，刪除動作和更新動作的參照動作選擇了cascade（級聯(lián)操作），當學生表的sid更新時，分數(shù)表score的相應的sid也會更新，當學生被刪除，分數(shù)表對應的sid的記錄也會自動刪除。

更新學生表學號是1的學生的學號為10

update student set sid = 10 where sid = 1

查看成績表，可以看到以前學號是1的已經變成了10

select * from score

刪除學生表學號是2的學生

delete from student where sid = 2

可以看到成績表，該學生的成績已經級聯(lián)刪除

select * from score

6、驗證級聯(lián)動作No Aaction

級聯(lián)動作設置為NO ACTION,如果子表中有匹配的記錄，則不允許對父表對應候選鍵進行update/delete操作。

Restrict動作同no action，都是立即檢查外鍵約束。

將參照動作設置為no action，如果成績表score有該學生sid，將不能更改學生表student表的學生sid列，也不能刪除該學生。除非你先刪除該學生的成績，再刪除該學生。

刪除score表的外鍵約束

alter table score drop foreign key score_fk;

增加score表的sid列外鍵約束

ALTER TABLE `score` ADD CONSTRAINT `score_fk` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION;

更新學號是10的學生的學號，失敗

update student set sid = 11 where sid = 10

刪除學號是10的學生，失敗

delete from student where sid = 10

需要先刪除學生成績表中的記錄，再刪除該學生。

delete from student where sid=10delete from score where sid=10;

7、驗證級聯(lián)動作Set NULL

在父表上update/delete記錄時，將子表上匹配記錄的列設為null，要注意子表的外鍵不能為not null。

刪除成績表的外鍵約束

alter table score drop foreign key score_fk;

增加成績表的sid列外鍵約束，參照動作為set null

alter table score add constraint score_fk foreign key (sid) references student (sid) on delete set null on update set null;

修改成績表的sid列默認值為NULL

ALTER TABLE `score` MODIFY COLUMN `sid` INTEGER(11) DEFAULT NULL;insert into student values (1,'孫悟空')insert into student values (2,'豬八戒')insert into score values (1,98)insert into score values (2,88)

刪除學生表中學號1的學生

delete from student where sid = 1

查看成績表，成績表中的學號為1的列為NULL

select * from score

總結

以上所述是小編給大家介紹的深入淺析MySQL從刪庫到跑路_高級（一）——數(shù)據(jù)完整性，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對億速云網(wǎng)站的支持！