MySQL 表約束-有解無憂

約束概述

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

查看約束

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不支持檢查約束，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/26186.html

標籤：MySQL

上一篇：請各位幫一下，剛開始學，好蒙

下一篇：MySQL學習筆記（3）：SQL