MySQL 表約束_ZenDei技術網路在線

約束概述對數據表中數據的限制條件叫表的約束，目的是為了保證表中記錄的完整和有效。例如非空、唯一等。查看約束 1 通過查看建表語句查看表中的約束 show create table tb_name; 2 通過檢查約束表查看約束 select * from information_schema. ...

約束概述

對數據表中數據的限制條件叫表的約束，目的是為了保證表中記錄的完整和有效。例如非空、唯一等。

查看約束

1 通過查看建表語句查看表中的約束

show create table tb_name;

2 通過檢查約束表查看約束

select * from information_schema.table_constraints where table_name='usr';

Tips：在MySQL資料庫中所有的約束都存放在information_schema.table_constraints表中～～～～參看～～～～～information_schema資料庫介紹

主鍵約束

什麼是主鍵約束

在MySQL中主鍵是一種約束，也是一種索引。被設為主鍵的欄位要求不重覆不能為空不能預設。主鍵的作用為唯一的確定表中的一條記錄。需要註意的是當主鍵是複合主鍵時，不重覆的含義是組合起來不重覆即可，單列值允許重覆，但不允許同時重覆

創建主鍵

1 在欄位中定義主鍵

create table if not exists user(
    id int(11) auto_increment primary key,
  	name char(64) not null default 'No Name'
);

2 欄位定義結束後定義主鍵

create table if not exists user(
		id int(11) auto_increment,
  	name char(64) not null default 'No Name',
  	primary key(id,name)
);
-- 註意 聲明主鍵的完整格式應該是這樣的
-- constraint 約束名 primary key key_name

3 建表完成後添加主鍵

上邊的兩種方式都是在創建表時候指定主鍵約束，也可以在表建成後添加主鍵約束

alter table tb_name add primary key(key_name)
-- tb_name   表名
-- primary key() 括弧里填寫欄位名,可以是一個 也可以是多個(複合主鍵)

刪除主鍵

-- 如果主鍵欄位中包含有自增 auto_increment，需要先刪除自增屬性 在刪除主鍵
-- 如果沒有自增直接刪除主鍵即可

-- 刪除自增類型。 ！！！註意 自增是類型 所以 可以使用 修改欄位的類型就ok

--1 修改欄位的名 修改後需要重新指定類型
alter table tb_name change col_name col_name int;

--2 簡單的直接修改類型
alter table tb_name modify col_name int;

-- 刪除掉 auto_increment屬性後可以刪除主鍵
alter table tb_name drop primary key;

主鍵的分類

1 單一主鍵

創建 1 中創建的 id 就是單一的主鍵，需要註意，定義欄位時聲明主鍵是無法創建複合主鍵的。

2 複合主鍵

創建 2 中的創建主鍵的方式允許創建單一主鍵也允許創建複合主鍵，推薦使用這種方法進行主鍵的創建。

3 聯合主鍵

當處理多對多的關係時，需要創建中間表來進行多對多的關聯，此時中間表的主鍵成為聯合主鍵，他能唯一的確定多對多的若幹實體間的對應關係。聯合主鍵也可以是複合主鍵即聯合主鍵由第三表的多個列複合而成。保持唯一。

外鍵約束

什麼是外鍵

外鍵依托於具有唯一約束的欄位，一般來講主鍵會多一些，但是也可以不是主鍵。前提是它們具有唯一約束。外鍵依賴的欄位可以來自本（自參照表）也可以是其他表，一般的是其他表的情況多一些。此時要求 主表必須已經存在於資料庫中，或者是當前正在創建的表。如果被依賴欄位在本表中，那麼這樣的約束結構稱為自參照完整性。

Tips： 雖然主鍵不允許為空不允許重覆，但是外鍵可以為空，也可以重覆。外鍵約束要求每一個值都來自於主鍵，特別的插入空值時，MySQL不支持預設，必須顯式表達出來，例如

insert into user(name,leader_id) values('monkey',null);  
-- 顯示的寫出 null 否則 會報錯
insert into user(name,leader_id) values('monkey');  
-- 是不合法的語句

在父表的表名後面指定列名或列名的組合。這個列或列的組合必須是父表的主鍵或候選鍵。
外鍵中列的數目必須和父表的主鍵中列的數目相同。
外鍵中列的數據類型必須和父表主鍵中對應列的數據類型相同。

創建外鍵

1 建表時創建外鍵

[constraint <fk_name>] foreign key(fk_col_name [，col_name2，…])
references <tb_name> (pk_col_name1 [，pk_col_name2，…])
-- fk_name				外鍵名
-- fk_col_name		設置外鍵欄位的欄位名
-- tb_name				外鍵關聯的主表表名
-- pk_col_name 		主表被依賴的欄位名

2 建表完成後創建

alter table tb_name add [constriant <fk_name>] foreign key(fk_name) references tb_name(pk_col_name)
-- fk_name				外鍵名
-- fk_col_name		設置外鍵欄位的欄位名
-- tb_name				外鍵關聯的主表表名
-- pk_col_name 		主表被依賴的欄位名

例子

-- 創建主表 leader
create table leader(
  	id int(11) primary key auto_increment, 
  	name char(64) not null
);

-- 創建外鍵所在表 employee
create table employee(
  	id int(11) primary key auto_increment, 
  	name char(64) not null,
  	leader_id int(11),
  	constraint fk_leader_employee foreign key(leader_id) references leader(id)
);
create table employee(
  	id int(11) primary key auto_increment, 
  	tid char(11),
  	name char(64) not null,
  	leader_id int(11),
  	constraint fk_leader_employee foreign key(name) references leader(name)
);

註意

外鍵和關聯的主表欄位類型必須一致，但是允許size不一致，但是這樣可能會出現無法預期的問題，不推薦這樣做，曾經在使用django的ORM時，手寫了migrations文件，導致出現類似情況，很大的坑。
外鍵欄位可以是主表的多個欄位（複合主鍵或者其他的情況都是被允許的，但是此時要保證關聯欄位和被關聯欄位要能對應起來）
特別要說明的是，作為其他表外鍵的主表欄位必須是具有唯一約束（主鍵欄位原生就自帶 unique 屬性，如果不是主鍵，那必須要手動的設置unique 當是外鍵是多個欄位時，需要設置複合唯一）

刪除外鍵

同刪除主鍵類似，不同的是，每張表只有一個主鍵，因此不需要指定主鍵名，但是外鍵外鍵名是不虛指定的。

alter table tb_name drop foreign key fk_name;
-- fk_name  			外鍵約束名 也就是 constrain 後面的名字

唯一約束

什麼是唯一約束

唯一約束就是要求欄位中的值是唯一，特別的它允許為空，但只能出現一個空值。唯一約束可以確保一列不出現重覆值，或者若幹列不出現重覆的組合。

創建唯一約束

1 欄位定義時添加

create table leader(
  	id int(11) primary key auto_increment,
  	name char(64) unique,
);
-- name 欄位唯一 ，不允許有重名的出現

2 欄位定義完成後添加

create table leader(
  	id int(11) primary key auto_increment, 
  	name char(64),
  	constraint uq_name unique key(name)    -- 在這裡寫唯一約束 與主鍵類似
);

註意

唯一約束和主鍵約束的區別在於主鍵中不許出現null 但是唯一要求只能出現一個空值
一張表中可以有若個個唯一約束，但是只能存在一個主鍵約束

3 建表完成後添加

alter table tb_name add [constaint uq_name] unique key(uq_col_name)
-- tb_name 				要修改的表名
-- uq_name 				約束名<索引名>
-- uq_col_name 		聲明唯一的欄位名
-- 約束名 可以不寫

刪除唯一約束

alter table tb_name drop index fk_name;
-- tb_name 			表名
-- fk_name    	約束名<索引名>

預設值約束和非空約束

default 應該被稱為預設值約束，他也是一種約束，作用和簡單，指定某欄位的預設值

創建預設值約束

1 欄位中定義預設值/非空

create table user(
		id int(11) auto_increment primary key,
  	age int(4) default 18,
  	name char(64) not null
);

2 表建成後添加

alter table tb_name change col_name col_type[size] default <預設值>
-- 就是簡單的修改表的結構語句
alter table user change col_name col_type[size] {default 預設值 | not null | null }

刪除預設值/非空約束

-- 修改表結構 將預設值 設置為null
alter table tb_name change col_name col_type[size] default null;
-- 或者
-- 修改表結構 修改欄位的類型
alter table tb_name modify col_name col_type[size];

檢查約束

MySQL不支持檢查約束，官方文檔上說，即使設置了檢查約束，但是mysql不會強制的要求這樣做。創建之後在information_schema.table_constraints 找不到相關的記錄。甚至他可能本身就不存在。

什麼是檢查約束

檢查約束一般來說是為了滿足用戶實際的實體完整性而存在的。例如身高應該 high > 10 and high < 280

創建檢查約束

1 創建表時添加

check <約束條件>
-- 例子
create table userinfo(
		id int(11) auto_increment primary key,
  	high int(4) default 160,
  	weight int(4) default 50,
  	check(heigh > 10 and heigh < 280)
);

2 創建完成後添加

alter table userinfo add constraint check_name check(約束條件)
-- 例如
alter table userinfo add constraint check_weight check(weight > 10 and weight < 300);

刪除檢查約束

alter table tb_name drop constraint check_name;
-- 例如
alter table userinfo drop constraint check_weight;
-- 值得註意的是，mysql 雖然說創建成功了，但是在information_schema 資料庫的 table_constraints 表中並不能找到相關的信息。MySQL不支持檢查約束。