MySQL實戰七：你不知道的外鍵與約束使用！

2019 年 10 月 6 日
筆記

MySQL學習倉庫Up-Up-MySQL，這是一個學習MySQL從入門實戰到理論完善，再到精通的一個倉庫，後面會把MySQL的學習資料上傳上去！歡迎大家star與fork起來！

倉庫地址：

https://github.com/Light-City/Up-Up-MySQL

也可以點擊閱讀原文！

今天上手第七彈，DDL及check約束無效，你知造!

1.標準的SQL語句

DML（Data Manipulation Language，數據操作語言）語句：主要由select、insert、update和 delete 四個關鍵字完成。

DDL（Data Definition Language，數據定義語言）語句：主要由create、alter、drop和 truncate 四個關鍵字完成。

DCL（Data Control Language，數據控制語言）語句：主要grant和revoke 兩個關鍵字完成。

事務控制語句：主要由commit、rollback和savepoint 三個關鍵字完成。

2.DDL

2.1 check約束無效，如何解決？

定義學生表，練習check、primary key 、enum。

CREATE TABLE Student (      SId varchar(7),      Sname varchar(10) NOT NULL,      Ssex varchar(4) CHECK (Ssex = '男'      OR Ssex = '女'),      Sage int CHECK (Sage >= 15      AND Sage <= 45),      Sdept varchar(20) DEFAULT '電腦系',      PRIMARY KEY (SId)  );

當往裡面插入數據的時候，發現check約束不起作用！如下所示：

mysql> insert into Student values('01','趙雷','男1',70,'1');  Query OK, 1 row affected (0.10 sec)    mysql> select * from Student;  +-----+--------+------+------+-------+  | SId | Sname  | Ssex | Sage | Sdept |  +-----+--------+------+------+-------+  | 01  | 趙雷   | 男1  |   70 | 1     |  +-----+--------+------+------+-------+  1 row in set (0.00 sec)

此時查看錶定義：

show create table Student

果不其然，沒得check約束：mysql是不支援check約束的。如果你創建表的時候加上了check約束也是不起作用的。所以,你不用更改或刪除之前的check約束。

| Student | CREATE TABLE `Student` (    `SId` varchar(7) NOT NULL,    `Sname` varchar(10) NOT NULL,    `Ssex` varchar(4) DEFAULT NULL,    `Sage` int(11) DEFAULT NULL,    `Sdept` varchar(20) DEFAULT '電腦系',    PRIMARY KEY (`SId`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8     |

使用enum限制插入的值

所以為了完成性別這種的離散範圍，可以使用enum，此時我們對上述表進行修改：

mysql> alter table Student modify column Ssex enum('男','女');  ERROR 1265 (01000): Data truncated for column 'Ssex' at row 1

上面提示，需要truncate data，由於之前插入的數據不符合要求，所以必須先：

mysql> truncate table Student;  Query OK, 0 rows affected (0.25 sec)

再進行修改：

mysql> alter table Student modify column Ssex enum('男','女');  Query OK, 0 rows affected (0.62 sec)  Records: 0  Duplicates: 0  Warnings: 0  mysql> insert into Student values('01','趙雷','男1',70,'1');  ERROR 1265 (01000): Data truncated for column 'Ssex' at row 1  mysql> insert into Student values('01','趙雷','男',70,'1');  Query OK, 1 row affected (0.18 sec)

此時，再次插入錯誤數據，就會提示不滿足列要求。

現在來驗證一下這個表結構是否修改：

mysql> show create table Student;  | Student | CREATE TABLE `Student` (    `SId` varchar(7) NOT NULL,    `Sname` varchar(10) NOT NULL,    `Ssex` enum('男','女') DEFAULT NULL,    `Sage` int(11) DEFAULT NULL,    `Sdept` varchar(20) DEFAULT '電腦系',    PRIMARY KEY (`SId`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8       |

會發現，enum已經有了！表結構修改成功！

但是問題又來了，上述方法對於離散數據沒問題，可是對於範圍的連續數據呢？比如上述的年齡欄位，本來想約束限制為15到45的，結果發現70竟然可以插入，這肯定不符合要求！

那麼如何解決呢，下面一起來看！

觸發器解決限制範圍內數據

創建觸發器：

delimiter $$  create trigger age_check before insert on Student  for each row  begin      declare msg varchar(100);      if new.Sage<15 or new.Sage>45 then set msg=concat('您輸入的年齡值：',new.Sage,'為無效的年齡，請輸入15到45以內的有效數字。');      signal sqlstate 'HY000' set message_text=msg;  end if;  end;  $$  delimiter ;

delimiter解釋：其實就是告訴mysql解釋器，該段命令是否已經結束了，mysql是否可以執行了。默認情況下，delimiter是分號(😉 。但有時候，不希望MySQL這麼做。在為可能輸入較多的語句，且語句中包含有分號。

上述就是先修改語句執行當碰到$$時候再去執行，然後復原;號。

mysql> insert into Student values('02','趙雷','男',70,'1');  ERROR 1644 (HY000): 您輸入的年齡值：70為無效的年齡，請輸入15到45以內的有效數字。  mysql> insert into Student values('02','趙雷','男',20,'1');  Query OK, 1 row affected (0.10 sec)

第一次插入不符合要求的年齡，發現確實提示報錯了！證明check約束起作用了，當插入範圍內數據，可以看到插入成功！

2.2 外鍵

2.2.1 創建外鍵

（1）不帶別名的外鍵,資料庫自動生成

首先創建department表：

CREATE TABLE department (      dept_name varchar(20) PRIMARY KEY NOT NULL,      building varchar(20),      budget int  );

其次，創建instructor表：

CREATE TABLE instructor (      ID char(5),      name varchar(20) NOT NULL,      dept_name varchar(20),      salary numeric(8, 2),      PRIMARY KEY (ID),      FOREIGN KEY (dept_name) REFERENCES department (dept_name)  );

查看生成的外鍵名：

show create table instructor    | instructor | CREATE TABLE `instructor` (    `ID` char(5) NOT NULL,    `name` varchar(20) NOT NULL,    `dept_name` varchar(20) DEFAULT NULL,    `salary` decimal(8,2) DEFAULT NULL,    PRIMARY KEY (`ID`),    KEY `dept_name` (`dept_name`),    CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

可以看到是instructor_ibfk_1。

（2）帶別名的外鍵

CREATE TABLE instructor (      ID char(5),      name varchar(20) NOT NULL,      dept_name varchar(20),      salary numeric(8, 2),      PRIMARY KEY (ID),      CONSTRAINT fk_dept_name FOREIGN KEY (dept_name) REFERENCES department (dept_name)  );

查看生成的外鍵名：

show create table instructor    | instructor | CREATE TABLE `instructor` (    `ID` char(5) NOT NULL,    `name` varchar(20) NOT NULL,    `dept_name` varchar(20) DEFAULT NULL,    `salary` decimal(8,2) DEFAULT NULL,    PRIMARY KEY (`ID`),    KEY `fk_dept_name` (`dept_name`),    CONSTRAINT `fk_dept_name` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

可以看到是fk_dept_name。

（2）刪除外鍵

alter table instructor drop foreign key fk_dept_name;

（3）添加外鍵

alter table instructor add constraint fk_dept_name foreign key(dept_name) references department(dept_name);

2.2.2 on update和on delete

這是資料庫外鍵定義的一個可選項，用來設置當主鍵表中的被參考列的數據發生變化時，外鍵表中響應欄位的變換規則的。update 則是主鍵表中被參考欄位的值更新，delete是指在主鍵表中刪除一條記錄： on update 和 on delete 後面可以跟的詞語有四個:no action ， set null ， set default ，cascade。 no action 表示不做任何操作， set null 表示在外鍵表中將相應欄位設置為null set default 表示設置為默認值(restrict)

（1）on delete cascade學習

cascade 表示級聯操作，就是說，如果主鍵表中被參考欄位更新，外鍵表(子表)中也更新，主鍵表(父表)中的記錄被刪除，外鍵表(子表)中改行也相應刪除。

還是上述例子，首先創建instructor與department表。

創表

父表：

CREATE TABLE department (      dept_name varchar(20) PRIMARY KEY NOT NULL,      building varchar(20),      budget int  );

子表：

CREATE TABLE instructor (      ID char(5),      name varchar(20) NOT NULL,      dept_name varchar(20),      salary numeric(8, 2),      PRIMARY KEY (ID),      CONSTRAINT fk_dept_name FOREIGN KEY (dept_name) REFERENCES department (dept_name)      ON UPDATE cascade  );

插入數據

當父表無數據，直接向子表插入數據，報錯

mysql> insert into instructor values(1,'小米','小米手機部門',1000.2);  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ddl`.`instructor`, CONSTRAINT `fk_dept_name` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE CASCADE)

不管有沒有on delete cascade都會出現這個問題。

現在往先往父表中插入數據：

mysql> insert into department values('小米手機部門','北京',10000);  Query OK, 1 row affected (0.07 sec)

往從表插入數據：

mysql> insert into instructor values(1,'小米','小米手機部門',1000.2);  Query OK, 1 row affected (0.08 sec)

查看兩表數據：

mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手機部門       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 小米手機部門       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)

刪除

父子表都有數據，刪除子表數據

mysql> delete from instructor where dept_name='小米手機部門';  Query OK, 1 row affected (0.13 sec)    mysql> select * from instructor;  Empty set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手機部門       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.01 sec)

發現子表數據被刪除，父表數據不變。

父子表都有數據，刪除父表數據

mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 小米手機部門       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手機部門       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> delete from department where dept_name='小米手機部門';  Query OK, 1 row affected (0.10 sec)    mysql> select * from department;  Empty set (0.00 sec)    mysql> select * from instructor;  Empty set (0.00 sec)

父表數據被刪除，子表聯帶被刪除！

如果沒有使用`on delete/update cascade`，不能刪除或更新父表數據，當刪除父表的數據時候報錯！如下所示：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ddl`.`instructor`, CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`))

要想刪除，必須先子表再父表！如下所示：

mysql> delete from instructor where dept_name='小米手機部門';  Query OK, 1 row affected (0.15 sec)    mysql> select * from instructor;  Empty set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 小米手機部門       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> delete from department where dept_name='小米手機部門';  Query OK, 1 row affected (0.11 sec)    mysql> select * from department;  Empty set (0.00 sec)

父子表都有數據，更新父表主鍵或者子表外鍵，都無效

如果想要更新，那麼就必須換成on update cascade。

上述on delete cascade換成on update cascade，可以發現只能更新父表的主鍵，同時父子表數據都會被更新，但是在子表的外鍵上做更新操作無效！

mysql> update departme set dept_name='蘋果手機部門' where dept_name='小米手機部門'  Query OK, 1 row affected (0.12 sec)  Rows matched: 1  Changed: 1  Warnings: 0    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 蘋果手機部門       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)    mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 蘋果手機部門       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)    mysql> update instructor set dept_name='小米手機部門' where dept_name='蘋果手機 部門';  Query OK, 0 rows affected (0.00 sec)  Rows matched: 0  Changed: 0  Warnings: 0    mysql> select * from instructor;  +----+--------+--------------------+---------+  | ID | name   | dept_name          | salary  |  +----+--------+--------------------+---------+  | 1  | 小米   | 蘋果手機部門       | 1000.20 |  +----+--------+--------------------+---------+  1 row in set (0.00 sec)    mysql> select * from department;  +--------------------+----------+--------+  | dept_name          | building | budget |  +--------------------+----------+--------+  | 蘋果手機部門       | 北京     |  10000 |  +--------------------+----------+--------+  1 row in set (0.00 sec)

2.2.3 總結

on delete cascade 不能更新父表主鍵或子表外鍵，刪除父表主鍵數據會將子表聯同刪除，刪除子表外鍵數據不影響父表。而on update只能刪除子表外鍵數據，不能刪除父表主鍵數據，只能更新父表的主鍵，同時父子表數據都會被更新，但是在子表的外鍵上做更新操作無效。如果沒有使用on delete/update cascade，不能刪除或更新父表數據。