資料庫系統之實體完整性約束

star發表於2021-10-15

資料庫完整性

今天做了一道資料庫的上機題，其中的一些知識覺得挺有用的，因此打算整理出來一篇部落格來與大家分享一下。~~這裡的語句針對的都是建立表後所需要的語句。~~

一、題目

1.練習定義三類完整性：
(1)為student表新增主碼約束
(2)為course表新增主碼和外碼約束
(3)為sc表新增主碼和外碼約束
(4)為course表新增cname取值唯一的約束
(5)為course表新增credit預設值為4的default約束
(6)為student表新增check約束，ssex取值只能為男或女。
2.驗證上面新增的約束
(1) 將student表中學號為201910122的學號修改為201215123，驗證主碼約束機制。
(2) 將course表中課程號為6的先行課號修改為9，驗證外碼約束機制。
(3) 向sc表中新增一條記錄，（’666666666’,’1’,80），驗證外碼約束機制。
(4) 修改student表中學號為201215122的學生記錄，將學號修改為‘11111’驗證外碼約束機制。
(5) 刪除student表中學號為201215122的學生記錄，驗證外碼約束機制。
(6) 將course表中課程號為2號的課程名稱修改為資料庫，驗證唯一約束機制。
3.刪除上面新增的約束
(1)刪除student表的主碼約束
(2)刪除course表的外碼約束
(3)刪除course表中cname取值唯一的約束

二、題目準備

此次上機需要的各個資料庫各個欄位的資料型別為：

1.student表

2.course表

3.sc表

此次上機需要的各個資料庫資料為：

1.student表

2.course表

3.sc表

三、題目分析

1.練習定義三類完整性：

(1)為student表新增主碼約束

新增主碼約束的MySQL語句是：alter table 表名 add primary key(列名);

因為主碼必須需要唯一併且資料不能重複，發現在student表中只有sno(學號)是每個人唯一標識。

因此這裡只需輸入這個語句即可：alter table student add primary key(sno);

最後檢驗是否成功，輸入desc student;

(2)為course表新增主碼和外碼約束

1.新增主碼約束

根據第一步我們可以知道cno(課程號)是唯一標識

因此新增主碼約束的語句是：alter table course add primary key(cno);

最後檢驗是否成功，輸入desc course;

2.新增外碼約束

新增外碼約束的MySQL語句是：alter table 表名 add constraint 約束名 foreign key(關聯欄位) references 主表(關聯欄位)

其中constraint 約束名可以省略，即也可以寫成：alter table 表名 add foreign key(關聯欄位) references 主表(關聯欄位)

首先先嚐試一下使用course表的cpno來參照sc表的cpno：

alter table course add foreign key(cpno) references sc(cpno);

,發現報錯

這是因為在參照時候，參照的和被參照的都需要是同一種資料型別和長度，比如tinyint型別的只能參照tinyint型別的，不能參照char(9)型別的。同樣的，資料長度不一樣也不能進行參照，char(9)型別的也不能參照char(4)的。

因此我們把cpno的資料型別更改一下再試一次

先更改cpno欄位的資料型別：alter table sc modify cpno tinyint;

然後再次輸入上面的語句。

成功了

(3)為sc表新增主碼和外碼約束

1.新增主碼約束

根據第一步我們可以知道cpno是唯一標識

因此新增主碼約束的語句是：alter table sc primary key(cpno);

最後檢驗是否成功，輸入desc sc;

2.新增外碼約束

使用sc表的sno參照student的sno：alter table sc add foreign key(sno) references student(sno);

(4)為course表新增cname取值唯一的約束

新增取值唯一約束的MySQL的語句是：alter table 表名 add unique(列名);

因此我們輸入：alter table course add unique(cname);

使用desc course;驗證一下

其中PRI表示主鍵

UNI表示取值唯一約束

MUL表示外來鍵

(5)為course表新增credit預設值為4的default約束

新增default約束的MySQL語句是：alter table 表名 alter 列名 set default '預設值';

因此輸入：alter table course alter credit set default '4';

最後檢驗是否成功，輸入desc course;

(6)為student表新增check約束，ssex取值只能為男或女。

新增check約束的MySQL語句是：alter table 表名 add check(約束條件);

因此這條語句是：alter table student add check(ssex="男" or ssex="女");

2.驗證上面新增的約束

(1) 將student表中學號為201910122的學號修改為201910123，驗證主碼約束機制。

修改元組的MySQL語句為：update 表名 set 列名=表示式 where 條件;

實現語句為：update student set sno='201910123' where sno='201910122';

此處表達的意思是因為有外碼約束，所以不能修改。

但是如果沒有這個外碼約束，它也修改不了。

我們知道，主碼的每個元組都必須不一樣，我們先看下我們的student表

其中sno是我們的主鍵，其中已經有‘201910123‘這個資料，因此根據主碼約束我們也修改不了

(2) 將course表中課程號為3的先行課號修改為4，驗證外碼約束機制。

根據第一步可以知道，實現語句為：update course set cno=4 where cno=3;

此處是因為cno是course的主鍵，所以此處報的是主鍵錯誤

沒有驗證的原因是因為我們在設定外碼的時候嗎，沒有讓別的外來鍵關聯這個表的cno

如果想要實現的話，我們可以再新增一條關聯

語句為：alter table sc add foreign key(cno) references course(cno);

然後再驗證就會報因為外碼約束而錯誤了。

(3) 向sc表中新增一條記錄，（’666666666’,’1’，8,80），驗證外碼約束機制。

向表中追加元組的MySQL語句是：insert into 表名 values(值，值，···);

實現語句是：insert into sc values('666666666','1',8,80);

這個錯誤意思是關聯強制約束造成的錯誤，即因為外碼約束機制引起的錯誤。

(4) 修改student表中學號為201910122的學生記錄，將學號修改為‘11111’驗證外碼約束機制。

實現語句為：update student set sno='11111' where sno='201910122';

此處也成功驗證了外碼約束機制。

(5) 刪除student表中學號為201910122的學生記錄，驗證外碼約束機制。

刪除元組的MySQL語句是：delete from 表名 where 條件;

因此實現語句是：delete from student where sno='201910122';

成功驗證外碼約束機制。

(6) 將course表中課程號為2號的課程名稱修改為資料庫，驗證唯一約束機制。

實現語句是：update course set cname="資料庫" where cno=2;

此處我們首先檢視一下course表的內容：

此處我們發現課程號為1的課程名已經是資料庫了，由唯一約束機制可知，cname的資料必須是唯一的，所以報錯。

成功驗證唯一約束機制。

3.刪除上面新增的約束

(1)刪除student表的主碼約束

首先刪除主碼約束的MySQL語句是：alter table 表名 drop primary key;

我們先輸入一下

此時我們發現一個問題，語句並沒有錯，但是不能刪除。

我們看一下錯誤內容：不能刪除主鍵：被某個外來鍵所需要

因此我們也就知道了，要是我們想要刪除這個主鍵，必須先讓那個外來鍵與這個主鍵的關聯斷開才行。

通過檢視前面的語句，我們發現，這個外來鍵是由sc表的sno與student表的sno關聯

刪除外碼約束的MySQL語句是：alter ![](https://img2020.cnblogs.com/blog/2309068/202110/2309068-20211015114119542-185235895.png) table 表名 drop foreign key 外碼名;

那麼這個外碼名怎麼知道呢，我們之前在建立的時候並沒有給它起別名，那麼這個外碼名就是sno嗎？

不，並不是。

我們來檢視一下，輸入show create table sc;