限制約束Constraint

為了資料的完整性，對表訂下的各種強制規定

所謂資料的完整性可以體現為4個方向:

• 實體完整性(Entity Integrity):同一個表中，不能存在兩條完全相同無法區分的記錄
• 域完整性(Domain Integrity):年齡範圍0-120，性別範圍"男/女"
• 參考完整性(Referential Integrity):員工所在部門，在部門表中要能找到這個部門
• 用戶自訂完整性(User-defined Integrity):用戶名唯一、密碼不能為空等，本部門經理的工資不得高於本部門職工的平均工資的5倍。

管理約束

• 在create table時添加約束:

create table test01
(
    id    int         not null,
    name  varchar(15) not null,
    email varchar(25)
);

• 用alter table添加或刪除約束:

alter table test01
    modify email varchar(25) not null;

• 查看表中約束:直接desc 表名，或:

#information_schema資料庫名（系統庫）
#table_constraints表名稱（專門存儲各個表的約束）
SELECT * FROM information_schema.table_constraints
WHERE table_name = '表名稱';

約束的分類

• 根據列
  • 單列
  • 多列:複合約束，綜合多列的值判斷
• 根據作用範圍
  • 列級:只作用於一個列，跟在列的定義之後
  • 表級:單獨定義

• 根據效用:下面一一說道

NOT NULL非空

• 限定某列的值不能為空
• 一個表可以有多列分別限定非空
• 只能跟在某列之後，只能單獨限定該列非空，不能組合限定
  • 例如限定id非空，而不能限定id與name只要一個非空就合格
• ''空字串、0數值不等於null

UNIQUE唯一

• 限制某列的值不能重複
• 可以是某個列的值唯一，也能是多列組合的值唯一
  • 例如
• 允許null且允許多個null
• 創建時若不給唯一約束命名，則預設與列名相同
• MySQL會給唯一約束的列預設創建唯一索引

-- 添加列約束範例
create table test02
(
    id    int unique, -- 列級
    name  varchar(15),
    email varchar(25),
    constraint uk_test02_email unique (email) -- 表級
);
-- 方式2
alter table test02
    add constraint uk_test02_name unique (name);
-- 方式3
alter table test02
    modify name varchar(25) unique;

-- 複合唯一約束範例
create table student_course(
    id int,
	sid int,
    cid int,
    score int,
    unique key(sid,cid)  -- 複合唯一
);

刪除唯一約束

• 只能通過刪除唯一索引的方式刪除
• 刪除時需要指定唯一索引名，唯一索引名就和唯一約束名一樣
• 如果創建唯一約束時未指定名稱:
  • 如果是單列，就預設和列名相同
  • 如果是組合列，那麼預設和()中排在第一個的列名相同
  • 也可以自訂唯一性約束名

-- 方法1，查看表的所有約束
select *
from information_schema.TABLE_CONSTRAINTS
where TABLE_NAME = '表名';
-- 見返回的約束名CONSTRAINT_NAME欄位

-- 方法2，查看表的索引
show index from test02;
-- 見返回的索引名Key_name欄位

-- 得到約束名(=索引名)後，刪除
alter table 表名 drop index 索引名;

PRIMARY KEY主鍵

• 主鍵約束 = 唯一 + 非空約束
• 一個表只能有一個主鍵
• 可以在列級宣告，也可以在表級；即可以對應一列或複合約束
• 如果是複合列主鍵，則任何列都不許為空，且組合值不可重複
• 在mysql中主鍵名總是PRIMARY，不可更改且自己命名無效
• 宣告主鍵約束時，系統會建立對應的主鍵索引
  • 如果約束刪除了，索引也會自動刪除，反之亦然
• 不要修改主鍵的值，因為主鍵是紀錄資料的唯一標示，亂改很可能出事

-- 創建表時宣告主鍵
create table if not exists user01(
    id int,
    last_name varchar(15),
    salary decimal(10,2),
    email varchar(30),
    primary key (id)
);

-- 創建表後修改某列為主鍵
alter table user01 add primary key (id,salary);

-- 刪除主鍵約束 (NOT NULL約束會保留)
alter table user01 drop primary key;
-- 實際開發中誰敢刪除主鍵直接拖出去打

自增列AUTO_INCREMENT

常跟主鍵一起使用，當作唯一標示符或順序

• 一個表最多有一個自增列
• 自增約束的列必須是主鍵或唯一
• 數據類型必須是整數類型
• 如果手很賤在插入資料時去指定自生列的值:
  • 指定賦的值為0或null，相當於讓系統自增
    • 所謂系統自增，就是當前最大值+1
    • 第一筆資料為1
  • 指定賦的值為其他數值，則為該數值
    • 比如現在有1到5號，我指定值為10可以成功
    • 並且之後系統自增為11號，6~9號被跳過
• 即使移除某筆資料，自增變量加了就不會扣回去
  • 例如新增了10筆資料，現在編號為1~10
  • 接著刪除了7、8、9號共3筆，但下一個自增號還是11
• 自增變量在mysql版本5.7與8.0有區別，5.7版本有坑就是自增變量只保存在記憶體，如果DBMS伺服器重啟自增變量會被重置，舉剛剛的例子就是下一個增加的會變7號；8.0則會從日誌找尋紀錄修正了這個坑
• 修改、刪除自增列用alter table ... modify即可

FOREIGN KEY外鍵

此列的資料限定源自其他表某列中的資料

實際開發中99%不准用外鍵，趕時間可跳過這段

• 主(父)表:被引用的表，被引用的列必須為主鍵或唯一
• 從(子)表:引用別人的，一個表可引用多個外鍵
• 宣告時強烈建議起外鍵名，否則會是一個自動的xxx_ibfk_n之類的名字而非列名
• 主從表的列名可以不同，但數值類型必須一樣
• 宣告外鍵時，系統會自動為該列建立普通索引，如果沒有指定外建名，則索引名是列名
• 刪除外鍵約束，還須手動刪除對應的索引

-- 建表時宣告
constraint 約束名 foreign key (列名) references 主表(列名)

-- 追加
alter table 從表名 add constraint 約束名
	foreign key (列名) references 主表(列名)
		on update 約束等級 on delete 約束等級

-- 刪除有2步驟
alter table 表名 drop foreign key 約束名;

alter table 表名 drop index 約束名或列名;

約束等級

修改主從表資料時對應的處理方式

• Cascade:主表的修改同步到從表
• Set null:修改主表時，從表的對應值設為null，但要注意從表該列不能為not null
• No action:若從表有匹配的紀錄，不允許修改主表
• Restrict:同上，這是預設的等級
• Set default:修改主表時，將從表對應的設成一個指定預設值，但innodb不能識別
• 最常見的就是on update cascade on delete restrict

外鍵的問題

• 外鍵約束要一個個去檢查對應，非常拖累計算成本
• 實際開發中禁用外鍵，在應用層面檢查資料的一致性
• 冷知識補充:mysql中每個表都可以自訂儲存引擎，若引擎不同則不能引用外鍵

CHECK檢查

mysql 5.7版本沒用，8.0才行

• 用以檢查，舉例:

gender char check (gender='男' or gender='女')
age int check(age > 20)

DEFAULT預設值

• 用以指定預設值，舉例:

salary int default 22000
tel char(11) not null default ''
age tinyint not null default 0

• not null default 空或0是鍵表時很常用的語句，可以避免表中有null
  • 因為null不好比較，且效率不高，會拖累索引

上次修改於 2021-12-24