常見的6種

摘要：一篇文章帶你徹底了解MySQL各種限制

MySQL限制

<1> 概念

• 是一種限制，它是對表的行和列的資料做出限制，確定表中資料的完整性和唯一性。

<2> 使用場景

• 建立表的時候，添加限制

<3> 分類

• default: 預設限制, 域完整性
• not null: 非空限制，域完整性
• unique: 唯一限制，實體完整性
• primary key: 主鍵限制，實體完整性
• foreign key: 外鍵限制，參照完整性
• check: 檢查限制（MySQL不支援），域完整性
• auto_increment: 自增長限制
• unsigned: 無符号限制
• zerofill: 零填充限制

資料庫中有三個完整性: 域、實體、參照完整性

• 域(列)完整性：
• 域完整性是對資料表中字段屬性的限制
• 實體完整性在MySQL中實作：
• 通過主鍵限制和候選鍵限制實作的
• 參照完整性：
• 也就是說是MySQL的外鍵

1. default

• 概念
• 指定某列的預設值，插入資料時候，此列沒有值，則用default指定的值來填充
• 添加
• 在建立表的時候添加： create … default

• create table t1(

  id int default 1,

  name varchar(20) default ‘老王’

  );

• 通過alter語句添加： alter … modify/change …
• alter table t1 modify id int default 2;
• alter table t1 change name name varchar(20) default ‘若塵’;
• 删除
• alter … modify/change
• alter table t1 modify id int;
• alter table t1 change name name varchar(20);

2. not null

• 指定某列的值不為空，在插入資料的時候必須非空 ‘’ 不等于 null, 0不等于 null
• 在建立表的時候添加： create … not null

• create table t2(

  id int not null,

  name varchar(20) not null

• alter table t2 modify id int not null;
• alter table t2 change name name varchar(20) not null;
• alter table t2 modify id int;
• alter table t2 change name name varchar(20);

3. unique

• 指定列或者列組合不能重複，保證資料的唯一性
• 不能出現重複的值，但是可以有多個null
• 同一張表可以有多個唯一的限制
• 添加唯一限制
• 在建立表的時候添加： create … unique

• create table t3(

  id int unique,

• insert t3 value (1, ‘老王’);

  insert t3 value (1, ‘老李’); – id 唯一限制，添加異常

• id int,

  name varchar(20) not null,

  constraint id_unique unique(id, name) – 添加複合限制

• insert t3 value (1, ‘老李’);

  select * from t3;

• 通過alter語句添加： alter … modify/change … / alter … add unique
• alter table t3 modify id int unique;
• alter table t3 add unique(name);
• alter table t3 add constraint un_id unique(id);

4. 删除唯一限制

• alter … drop … index 名稱
• drop index on 表名
• alter table t3 drop index id_unique;
• 注意：如果删除的唯一限制列具有自增長限制，則必須先删除自增長限制，再去删除唯一限制

5. 主鍵限制

• 目前行的資料不為空并且不能重複
• 相當于：唯一限制+非空限制
• 添加主鍵限制
• 在建立表的時候添加： create … primary key

• create table t4(

  id int primary key,

  name varchar(20)

• name varchar(20),

  [constraint id_primary] primary(id, name) – 聯合限制

• 通過alter語句添加： alter … modify/change … / alter … add primary key …
• alter table t4 modify id int primary key;
• alter table t3 add constraint un_primary primary key(id, name);
• 删除主鍵
• alter … drop primary key
• alter table t4 drop primary key;
• 注意：如果删除的主鍵限制具有自增長限制，則必須先删除自增長限制，再去删除主鍵限制。

6. auto_increment: 自增長限制

• 概述
• 列的數值自動增長，列的類型隻能是整數類型
• 通常給主鍵添加自增長限制
• 在建立表的時候添加： create … auto_increment

• create table t5(

  id int primary key auto_increment,

• 通過alter語句添加： alter … modify/change …auto_increment
• alter table t5 change id id int auto_increment;
• 删除自增長
• alter … modify/change…
• alter table t5 modify id int;
• 注意：
• 一張表隻能有一個自增長列，并且該列需要定義限制。

7. unsigned: 無符号限制

• 指定目前列的數值為非負數
• age tinyint 1 -128～127 unsigned 0～255
• 在建立表的時候添加： create … unsigned

• create table t6(

  age tinyint unsigned

• 通過alter語句添加： alter … unsigned modify/change …
• alter table t6 change age age tinyint unsigned;
• alter table t6 modify age tinyint unsigned;
• alter … modify/change …
• alter table t6 modify age tinyint;
• alter table t6 change age age tinyint;

8. zerofill: 零填充限制

• 指定目前列的數值的顯示格式，不影響目前列顯示範圍
• 在建立表的時候添加： create … zerofill

• create table t7(

  age int(6) zerofill

• alter table t7 modify age int;
• alter table t7 change age age int;

9. foreign key: 外鍵限制

• 通過建立外鍵，設定表與表之間的限制性，限制資料的錄入

員工表(從表)                       部門表(主表)
  員工号    員工姓名  部門名稱          部門号  部門名稱
  1        張三      1                1         人力
  2        李四      2                2         銷售
  3        王五      3      

• 建立表與表之間的關系，建立參照完整性，一個表可以有多個外鍵，每個外鍵必須參照另一個主鍵。
• 被外鍵限制的列，取值必須參照其主表列中的值
• 注意：通常先建立主表，再建立從表
• 添加外鍵限制

• create table emp(

  empno int promary key auto_increment,

  ename varchar(32) not null,

  deptno int,

  [constraint fk_name] foreign key(deptno) references dept(deptno) – 添加外鍵限制

• create table dept(

  deptno int primary key auto_increment,

  dname varchar(32),

  loc varchar(32)

• 使用alter add constraint …
• alter table emp add constraint fk_name foreign key(deptno) references dept (deptno);
• 删除外鍵限制
• alter … drop foreign key fk_name
• alter table emp drop foreign key fk_name;
• 在建立表時，不去明确指定外鍵限制的名稱，系統會自動地生成一個外鍵的名稱。
• 使用 show create table 表名 檢視具體的外鍵名稱
• 設定外鍵中的級聯關系
• on delete cascade: 删除主表中的資料時，從表中的資料随之删除
• on update cascase: 更新主表中的資料時，從表中的資料随之更新
• on delete set null: 删除主表中的資料時，從表中的資料置空
• 級聯删除
• [constraint fk_name] foreign key(deptno) references dept(deptno) on delete cascade-- 添加外鍵限制
• 插入資料時，先插入主表的資料，再插入從表的資料
• 删除資料時，先删除從表的資料，再删除主表的資料

資料庫的設計

• 主鍵限制
• 自增長限制
• 外鍵限制(慎用)
• 唯一限制
• 非空限制
• 預設限制

本文分享自華為雲社群《一篇文章帶你徹底了解MySQL各種限制》，原文作者：ruochen。

點選關注，第一時間了解華為雲新鮮技術~