一文理解MySQL資料庫的約束與表的設計

1.資料庫約束

約束是關係型資料庫提供的一種校驗資料合法性的機制. 

1.1 約束型別

約束型別	說明	範例
null	使用not null 指定列不為空	name varchar(20) not null;
unique	指定列為唯一的,不重複的	name varchar(20) unique;
default	指定列為空時的預設值	name varchar(20) default "無名氏";
主鍵約束	not null 和unique 的結合	id int primary key;
外來鍵約束	關聯其他表的主鍵或唯一鍵	foreign key (欄位名) references 主表(列)
check約束	保證列的值符合指定的條件	check(sex='男'or sex='女')

1.2 null 約束

建立表時指定某列不為空.

語法:

create table 表名(欄位1 型別 not null,欄位2 型別...);

範例:

1.3 unique 唯一的約束

建立表時指定某列為唯一的不重複的.

語法:

create table 表名(欄位1 型別 unique,欄位2 型別....);

範例:

1.4 default 預設值約束

指定列為空時的預設值

語法:

create table 表名(欄位1 型別 default 預設值,欄位2 型別....);

範例:

1.5 primary key 主鍵約束

建立表時指定列有唯一確定的識別符號

語法:

create table 表名(欄位1 型別 parmary key,欄位2 型別...);

範例:

學生id 不為null 且不能重複.

1.6 primary key auto_increment 自增主鍵

對於整數型別的主鍵, 常搭配自增auto_increment變為自增主鍵, 插入欄位不給值時, 使用最  大值+1.

語法:

create table 表名(欄位1 int primary key auto_increment,欄位2 型別...);

範例:

分散式系統中唯一id生成演演算法 

對於較小的資料量, 所有的資料都在一臺MySQL伺服器上儲存,此時自增主鍵可以很好的工作, 但如果涉及的資料量較為龐大, 需要將資料部署到多臺MySQL伺服器上, 這時再插入資料就無法保證自增主鍵的唯一性.因此MySQL內部採用分散式系統中唯一id生成演演算法.

實現公式:

時間戳+主機編號+隨機因子 結合這三部分就可以得到一個全域性唯一的id.

1.7 foreign key 外來鍵約束

MySQL外來鍵是用來在兩個表之間建立連線,可以是一列或多列,一個表可以有多個外來鍵.

• 外來鍵是本表的一個欄位, 不是本表的主鍵, 但對於另一個表的主鍵.
• 定義外來鍵之後不允許刪除另一個表中具有關聯關係的行.
• 主表(父表): 相關聯欄位中主鍵所在的表就是主表.
• 從表(子表): 相關聯欄位中外來鍵所在的表就是從表.

語法:

新增外來鍵約束

(constraint 外來鍵名) foreign key (欄位名1),(欄位名2..) reference 主表名(主鍵列1,主鍵列2..)

修改表時新增外來鍵約束 

alter table 表名 add (constraint 外來鍵名) foreign key (列名) references 主表名(列名);

刪除外來鍵約束

alter table 表名 drop forigen key (外來鍵名);

範例:

MySQL表中, 沒有預設對兩張有關聯的表提供檢查機制, 例如:下圖中插入非法班級, 不會報錯. 因此需要引入外來鍵約束.

加入外來鍵約束之後再插入非法資料就會報錯. 

非法修改已插入的資料也會報錯.

Tips:

• 父表必須已存在資料庫中或是當前正在建立的表.
• 父表中的主鍵不能出現空值, 但外來鍵可以.所以通常用primary key來約束主鍵.
• 外來鍵中列是數目和資料型別必須和父表的主鍵一一對應.
• 父表限制子表的插入,字表也約束父表的刪除.(互相制約) 

父表中元素的刪除:

實際應用中刪除父表中資料的方式通常是邏輯刪除. 當一個商品下架之後, 為了讓客戶可以檢視到歷史訂單, 所以不能直接刪除商品資訊. 通常需要在建表之初預留商品下架的欄位, 商品下架之後, 給商品下架的欄位標記賦值達到偽刪除的效果.

2.表的設計

基本思路:

• 先明確實體
• 再明確實體之間的聯絡
• 根據上述內容, 巢狀到下面的公式中, 就可做出表.

範例:

粗略設計一個學生教務管理系統.

• 確定實體為: 學生, 課程, 學生賬號, 學生班級
• 學生與課程之間存在關係, 學生與賬號之間存在關係, 學生與班級之間存在關係.
• 學生與課程:多對多, 學生與賬號:一對一, 學生與班級:一對多.

2.1 一對一

一個人只能有一個身份證號碼, 一個身份證號碼只對應一個人.

2.2 一對多

一個班級有多個學生, 一個學生只對應一個班級.

2.3 多對多

一個學生可以選擇多個課程, 一個課程也可以被多個學生選擇.

總結

到此這篇關於一文理解MySQL資料庫的約束與表設計的文章就介紹到這了,更多相關MySQL約束與表設計內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！