mysql資料庫基本操作（六）

彭方炎QAQ發表於2019-05-27
MySql資料庫

 

外來鍵約束

建立外來鍵

       前面講的表單查詢都是一張表，但專案中表與表之間是有關聯的，比如我們建立的學生表，他們可能在不同班級，不同班級有不同的班主任，他們之間的關係大概是這樣的：每一個班主任會對應多個學生 , 而每個學生只能對應一個班主任。所以建立一張班主任的表，這張表應該是主表，學生的那張表是子表，需要新增班主任資訊：

CREATE TABLE Classhost(

       id TINYINT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       age INT ,
       is_marriged boolean 
);

INSERT INTO Classhost (name,age,is_marriged) VALUES ("麗麗",22,0),
                                                       ("莉莉",24,0),
                                                       ("李麗",22,0),
                                                       ("李莉",20,0);
CREATE TABLE student(
        id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       host_id TINYINT   
)ENGINE=INNODB;

INSERT INTO Student(name,host_id) VALUES ("pengfy",2),
                                            ("pyq",4),
                                            ("xiaojiang",1),
                                            ("pyq2",3),
                                            ("pengfy2",1),
                                            ("pyq3",3),
                                            ("lily",2);

     建立好了兩張表如下:

 

     現在假設麗麗辭職了，我們試一下刪除名字叫麗麗的班主任：

delete from classhost where name="麗麗";

     還真的刪掉了，那現在看看學生表有沒有什麼變化：

        發現xiaojaing和pengfy2的班主任id還是1，這顯然是不合適的，一個班主任走了，學生資訊也要變化，要麼分到別的班，要麼一起走（特別喜歡這個班主任），大家也可是試一下在刪除班主任後，在學生表裡面插入一條學生資訊，host_id設為1，也是OK的。這兩種情況顯示不合理，那麼這個時候就要引入外來鍵這個概念了，現在給學生的表加上一個外來鍵，首先要找到兩張表關聯的地方，就是學生表裡面host_id和老師表裡面的id是關聯的，那我們新建一下學生表2：

CREATE TABLE student2(
        id INT PRIMARY KEY auto_increment,
       name VARCHAR (20),
       host_id TINYINT,    --切記:作為外來鍵一定要和關聯主鍵的資料型別保持一致       
       FOREIGN KEY(host_id) REFERENCES classhost(id)
)ENGINE=INNODB;

INSERT INTO Student2(name,host_id) VALUES ("pengfy",2),
                                            ("pyq",4),
                                            ("xiaojiang",4),
                                            ("pyq2",3),
                                            ("pengfy2",4),
                                            ("pyq3",3),
                                            ("lily",2);

       這裡新的學生資訊也要改一下，把id為1的要改成其他的，不然會報錯。現在再試一下，刪掉id為2的班主任：

delete from classhost where id=2;

       報錯了，不讓刪除了，因為外來鍵約束了，報錯資訊如下：

 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))

      再試一下給id=1的老師加一個學生，看是否能新增成功：

insert into student2(name,host_id) values ('lucy',1);
報錯資訊：
Cannot add or update a child row: a foreign key constraint fails (`test`.`student2`, CONSTRAINT `student2_ibfk_1` FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))

     同樣報錯了 ，看一下報錯資訊裡面有個東西，CONSTRAINT `student2_ibfk_1`這個東西我沒有寫過，後面的FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))才是我寫的，那這個是什麼呢?這個是外來鍵名稱，你不自定義的時候，會預設給你加上一個，所以給一個表增加一個外來鍵完整的寫法是這樣的：

ALTER TABLE student  ADD CONSTRAINT anyname
                     FOREIGN KEY(host_id)
                     REFERENCES  classhost(id);

     試著給我們學生表1增加一個外來鍵：

 Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-1110_2`, CONSTRAINT `anyname` FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))

     也報錯了，這是為什麼呢？因為老師裡面已經沒有id=1的了，而學生表還存在，看來要修改一下學生表才行：

     第三個和第五個改成4好了，

update student set host_id=4 where id=3 or id =5;

     再試一下增加外來鍵，看一下表結構：

      新增成功，那麼能增加就能刪除嘛，試一下刪除這個外來鍵：

 ALTER TABLE student DROP FOREIGN KEY anyname;

      檢視是否刪除成功：

     已經找不到外來鍵了，刪除成功嘍。那現在的問題就是主表怎麼正常刪除資料，並且把子表的資料也一併刪除。這就涉及下面要講的INNODB支援的ON語句。

INNODB支援的ON語句

    外來鍵約束對子表的含義:   如果在父表中找不到候選鍵,則不允許在子表上進行insert/update
    外來鍵約束對父表的含義: 在父表上進行update/delete以更新或刪除在子表中有一條或多條對應匹配行的候選鍵時,父表的行為取決於：在定義子表的外來鍵時指定的on update/on delete子句

innodb支援的四種方式

1.cascade方式

      在父表上update/delete記錄時，同步update/delete掉子表的匹配記錄，外來鍵的級聯刪除：如果父表中的記錄被刪除，則子表中對應的記錄自動被刪除，比如剛才在刪除班主任的同事，也把學生刪除，就應該在主鍵中加上一段ON DELETE CASCADE：

FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE CASCADE

      我們再給student加上這麼一個外來鍵：

ALTER TABLE student  ADD CONSTRAINT anyname
                     FOREIGN KEY(host_id)
                     REFERENCES  classhost(id)
                     ON DELETE CASCADE;

     同時我們也要把student2這張表刪除，因為主表也和這個表繫結了，外來鍵沒改到時候刪除主表還是會報錯的，刪掉之後，試一下刪除classhost表裡面的一個內容：

     成功了，沒有報錯啊，看看子表裡面，原先我們的子表id=1和id=7都是host_id=2的：

      現在子表裡面繫結了host_id=2的資訊也都一起刪除了，除了這種，還有以下三種情況。

2.set null方式

      在父表上update/delete記錄時，將子表上匹配記錄的列設為null，要注意子表的外來鍵列不能為not null

FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
                              ON DELETE SET NULL

      有興趣可以試一下這種方式，就不演示了。

3.Restrict方式

       拒絕對父表進行刪除更新操作，這個很少用，相當於不讓sql報錯，瞭解一下就行了。

4.No action方式

      在mysql中同Restrict,如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作，這個也作一個瞭解就行。

 

相關文章