資料庫實驗回顧
-
物體完整性
物體完整性即主碼的屬性不能為空，而主碼就可保證元組是不重復的，即主碼值是不能重復的，
-
參照完整性
參照完整性保證外碼的值要么是被參照關系中的主碼值，要么取空值，
-
用戶自定義完整性
可以按系統的需求設計各種自定義的完整性檢查，
一、物體完整性
1、主鍵約束
主鍵（promary key）用于唯一的標識表中的某一條記錄，在兩個表的關系中，主鍵用來在一個表中參考來自另一個表中的特定記錄，一個表的主鍵可以由多個關鍵字共同組成，并且主鍵的列不能包含空值，主鍵的值能唯一標識表中的每一行，這就好比所有人都有身份證，每個人的身份證號是不同的，能唯一標識每一個人，
添加主鍵
ALTER TABLE 表名 ADD PRIMARY KEY(列名);
設定主鍵
ALTER TABLE orders ADD PRIMARY KEY(列名);
在創建表的時候，設定主鍵
-- 單個欄位的主鍵
CREATE TABLE 表名(
欄位名 資料型別 PRIMARY KEY
);
-- 多個欄位組合的主鍵
CREATE TABLE 表名(
欄位名1 資料型別,
欄位名2 資料型別,
.....
PRIMARY KEY(欄位名1, 欄位名2, 欄位名n)
);
2、唯一約束
唯一約束用于保證資料表中欄位值的唯一性，在 MySQL 中使用 UNIQUE 關鍵字添加唯一約束，在創建表時為某個欄位添加唯一約束的具體語法格式如下：
CREATE TABLE 表名(
欄位名 資料型別 UNIQUE,
....
);
注意：被定義成唯一約束的欄位，欄位值不能相同，但是可以為 NULL，
唯一約束也可以添加到已經創建完成的表中，語法格式如下：
ALTER TABLE 表名 ADD UNIQUE(列名);
3、自動增長列
資料表中的 id 欄位一般從1開始插入，不斷增加，每次插入新資料時，都要添加一個 id 欄位的值，當資料內容龐大時，容易出錯，為了解決這個問題，可以將 id 欄位的值設定為自動增加，在 MySQL 中使用 AUTO_INCREMENT 關鍵字設定表欄位值自動增加，在創建表時將某個欄位的值設定為自動增長，語法格式如下：
CREATE TABLE 表名(
欄位名 資料型別 AUTO_INCREMENT,
....
);
此外，也可以為已經創建完成的表欄位設定自動增長列，語法格式如下：
ALTER TABLE 表名 MODIFY 欄位名 資料型別 PRIMARY KEY AUTO_INCREMENTL;
二、參照完整性
MySQL參照完整性一般是通過MySQL外鍵(foreign key)實作的
洗掉參照約束
ALTER 表 DROP FOREIGN KEY fg_fk;
給現有表增加參照約束
ALTER TABLE`score`ADD CONSTRAINT`score_fk2`FOREIGN KEY (`sid`) REFERENCES`student`(`sid`);
資料庫系統的外鍵值更新模式一般有3種：
-
默認的外鍵管理是Restict，即限制方式，前面實驗我們已經見識了這種方式的作用，
-
CASCADE，即級聯方式，可以理解為株連九族，即主鍵改變后，參考它的外鍵值自動改變成新主鍵值來保證參照完整性，
-
SET NULL，置空，即主鍵值改變后，參考它的外鍵值自動改為NULL來保證參照完整性，
eg
alter table 表名
add constraint fk_js foreign key(任課教師編號) references teacher (工號) on update cascade;
三、用戶自定義完整性
像MS SQL Server等資料庫管理系統有CHECK約束可以很方便地實作用戶自定義完整性約束，但MySQL沒有提供真正的CHECK約束，但用戶自定義約束的原理都差不多，通過觸發器就可以實作，
例如：學生年齡不能取負值的約束
-- insert 觸發器 年齡不能為負
delimiter ;
delimiter $$
create trigger st_ins_chk_age before insert
on student
for each row
begin
if new.年齡 is not NULL and new.年齡 < 0 then
signal sqlstate 'HY000'
set message_text = "年齡不能為負";
end if;
end$$
-- update 觸發器 年齡不能為負
delimiter ;
delimiter $$
create trigger st_up_chk_age before update
on student
for each row
begin
if new.年齡 is not NULL and new.年齡 < 0 then
signal sqlstate 'HY000'
set message_text = "年齡不能為負";
end if;
end$$
delimiter ;
附加
不用外鍵，而利用觸發器實作socre的課號要級聯參照course中的課號？
score 表
-- 觸發器 實作 score 外鍵 學號 參照 student 學號 -- score
delimiter ;
delimiter $$
create trigger sc_fk_ins_xh before insert
on score
for each row
begin
if (select count(*) from student where 學號=new.學號)=0 and new.學號 is not NULL then
signal sqlstate 'HY000'
set message_text = "Cannot add or update a child row: a foreign key constraint ...";
end if;
end$$
create trigger sc_fk_up_xh before update
on score
for each row
begin
if (select count(*) from student where 學號=new.學號)=0 and new.學號 is not NULL then
signal sqlstate 'HY000'
set message_text = "Cannot add or update a child row: a foreign key constraint ...";
end if;
end$$
delimiter ;
注意：score 中是 new ，student 中是 old
student 表
-- 觸發器 實作 score 外鍵 學號 參照 student 學號 -- student
delimiter ;
delimiter $$
create trigger st_del_xh before delete
on student
for each row
begin
if (select count(*) from score where 學號=old.學號)>0 then
signal sqlstate 'HY000'
set message_text = "Cannot delete or update a child row: a foreign key constraint ...";
end if;
end$$
create trigger st_up_xh before update
on student
for each row
begin
if (select count(*) from score where 學號=old.學號)>0 then
signal sqlstate 'HY000'
set message_text = "Cannot delete or update a child row: a foreign key constraint ...";
end if;
end$$
delimiter ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/238460.html
標籤：MySQL
上一篇：[翻譯]——MySQL Server Variable: sync_binlog (Doc ID 1501926.1)
下一篇：MySQL存盤程序和定時任務