InnoDB外鍵使用小結，InnoDB鍵使用小結

InnoDB外鍵使用小結，InnoDB鍵使用小結

USE `wfc_database`;
#  主表（也可以稱作：被參照表、referenced table、outTable）
ALTER TABLE `app` ENGINE=INNODB;
#  從表（也可以稱作：參照表、外表、referencing table ）
ALTER TABLE `app_version` ENGINE=INNODB;
# 一個 【應用 】可以有多個【應用版本】
# 因此 app 和 app_version 是 1:n 的關系 （一個 app_id 對應有多個 av_app_id）
# app_id是 app表 的主鍵， av_app_id是 app_version表 的索引

# app_version數據訂正（在 app_version表中刪除 app已經沒有的 app_id）
DELETE FROM `app_version` WHERE av_app_id NOT IN ( SELECT app_id FROM app);
# 此步驟極為重要，否則無法添加外鍵（從表中存在主表中沒有的外鍵id是不允許的）

# app_version添加外鍵
ALTER TABLE `app_version`
ADD CONSTRAINT fk_av_app_id                
FOREIGN KEY (av_app_id)
REFERENCES `app` (app_id)
ON DELETE CASCADE ON UPDATE CASCADE;

# 建立外鍵的前提：
  被約束字段與外鍵的數據類型必須相同
  被約束字段需要設置為索引，外鍵需要設置為 PRIMARY
  外鍵作用： 使兩張表形成關聯，外鍵只能引用從表中的列的值！
  指定從表關鍵字： foreign key (列名)
  引用外鍵關鍵字： references <外鍵表名> (外鍵列名)
# 事件觸發（級聯操作）限制 ： on delete 和 on update
  可設參數 cascade (跟隨外鍵改動) ，強烈推薦，能夠保存數據一致性
  restrict (限制主表中的外鍵改動)
  [默認] no action 

# 如果以上語句在創建過程中有報如下錯誤
  Can not create table 'd91.#sql-197e_18b4' (errno: 150)
# 請把 av_app_id 和 app_id 的字段類型設置為完全相同
  也就是約束字段的 類型、長度、有無符號、是否為空、默認值 要設置跟外鍵相同
  對於這次的場景，我把 app_id 和 av_app_id 都設置為 INT(10) , UNSIGNED，NOT NULL
  確保外鍵的名字沒有和已經存在的 鍵值/索引名 重名

# 補充，如果要刪除外鍵約束可以這麼做
  alter table 表名 drop foreign key 外鍵約束名稱；
# 比如，要刪掉剛剛創建的外鍵，你可以
  ALTER TABLE `app_version` DROP FOREIGN KEY fk_av_app_id ;

# --------------------- InnoDB外鍵知識 ------------------------- #

方法一：　　
定義數據表
　　假如某個電腦生產商，它的數據庫中保存著整機和配件的產品信息。用來保存整機產品信息的表叫做 Pc;用來保存配件供貨信息的表叫做Parts。
　　在Pc表中有一個字段，用來描述這款電腦所使用的CPU型號;
　　在Parts 表中相應有一個字段，描述的正是CPU的型號，我們可以把它想成是全部CPU的型號列表。
　　很顯然，這個廠家生產的電腦，其使用的CPU一定是供貨信息表(parts)中存在的型號。這時，兩個表中就存在一種約束關系(constraint)——Pc表中的CPU型號受到Parts 表中型號的約束。

　　首先我們來創建 parts 表：
CREATE TABLE parts (
... 字段定義 ...,
model VARCHAR(20) NOT NULL,
... 字段定義 ...
);
接下來是Pc表：   　　　　　　　　　　　　　　　　
CREATE TABLE pc (
... 字段定義 ...,
cpumodel VARCHAR(20) NOT NULL,
... 字段定義 ...
};
　　設置索引
　　若要設置MySQL外鍵，在 參照表 [外表] (referencing table，即Pc表) 和被參照表 [主表] (referenced table，即parts表) 中，相對應的兩個字段必須都設置索引(index)。

　　對Parts表：
　　ALTER TABLE parts ADD INDEX idx_model (model);
　　這句話的意思是，為 parts 表增加一個索引，索引建立在 model 字段上，給這個索引起個名字叫idx_model。

　　對Pc表也類似：
　　ALTER TABLE pc ADD INDEX idx_cpumodel (cpumodel);
　　事實上這兩個索引可以在創建表的時候就設置。這裡只是為了突出其必要性。

　　定義外鍵
　　下面為兩張表之間建立前面所述的那種“約束”。因為pc的CPU型號必須參照parts表中的相應型號，所以我們將Pc表的cpumodel字段設置為“外鍵”(FOREIGN KEY)，即這個鍵的參照值來自於其他表。

ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model); 

　　第一行是說要為Pc表設置MySQL外鍵，給這個外鍵起一個名字叫做fk_cpu_model;第二行是說將本表的cpumodel字段設置為外鍵;第三行是說這個外鍵受到的約束來自於Parts表的model字段。

　　這樣，我們的外鍵就可以了。如果我們試著CREATE一台Pc，它所使用的CPU的型號是Parts表中不存在的，那麼MySQL會禁止這台PC被CREATE出來。

　　級聯操作
　　考慮以下這種情況：
　　技術人員發現，一個月之前輸入到 parts 表中的某個系列的 cpu (可能有很多款)的型號全都輸錯了一個字母，現在需要改正。我們希望的是，當 parts 表中那些 Referenced Column 有所變化時，相應表中的 Referencing Column 也能自動更正。
　　可以在定義MySQL外鍵的時候，在最後加入這樣的關鍵字：
　　ON UPDATE CASCADE; 即在主表更新時，子表(們)產生連鎖更新動作，似乎有些人喜歡把這個叫“級聯”操作。:)
　　如果把這語句完整的寫出來，就是：
# 例子一
ALTER TABLE pc ADD CONSTRAINT fk_cpu_model
FOREIGN KEY (cpumodel)
REFERENCES parts(model)
ON UPDATE CASCADE; 

　　除了 CASCADE 外，還有 RESTRICT(禁止主表變更)、SET NULL(子表相應字段設置為空)等操作。
　　延伸閱讀

　　外鍵(Foreign Key)
　　如果公共關鍵字在一個關系中是主關鍵字，那麼這個公共關鍵字被稱為另一個關系的外鍵。由此可見，外鍵表示了兩個關系之間的聯系。以另一個關系的外鍵作主關鍵字的表被稱為主表，具有此外鍵的表被稱為主表的從表。外鍵又稱作外關鍵字。

　　外鍵的作用：
　　保持數據一致性，完整性，主要目的是控制存儲在外鍵表中的數據。 使兩張表形成關聯，外鍵只能引用外表中的列的值!

方法二：
建立外鍵的前提： 本表的列必須與外鍵類型相同(外鍵必須是外表主鍵)。
外鍵作用： 使兩張表形成關聯，外鍵只能引用外表中的列的值！
指定主鍵關鍵字： foreign key(列名)
引用外鍵關鍵字： references <外鍵表名>(外鍵列名)
事件觸發限制: on delete和on update , 可設參數cascade(跟隨外鍵改動), restrict(限制外表中的外鍵改動),set Null(設空值）,set Default（設默認值）,[默認]no action 

例如：
outTable表 主鍵 id 類型 int
創建含有外鍵的表： 

create table temp(
id int,
name char(20),
foreign key(id) references outTable(id)
on delete cascade on update cascade); 

創建後修改表：
alter table temp add constraint  foreign key(id) references outTable(id) on delete cascade on update cascade; 

說明：把id列設為外鍵，參照外表outTable的id列，當外鍵的值刪除本表中對應的列刪除；當外鍵的值改變本表中對應的列值改變。在使用alter中constraint是針對使用外鍵的情況。
這種主外鍵的級聯操作在mysql中只支持InnoDB類型。要設置為該類型要在mysql中的配置文件中改動默認不支持InnoDB為支持，可輸入命令查看是否支持：

mysql > show variables like "have%";
如果不支持停止mysql，打開my.ini配置文件找到skip-innodb，前面加#，重啟mysql，然後修改主外鍵兩表類型：

mysql > alter table xxx ENGINE = InnoDB;
最後在命令中輸入上述創建外鍵的方法。 不過，如果你的業務不需要使用到事務，那麼使用myisam是最佳考慮， 因為myisam不支持事務，有比較好的性能。 但是如果你的業務必須要使用到事務，也就是說對數據一致性要求很高的話，需要使用到INODB,由於INODB要使用到鎖，因此它的並發能力就差一些，因此性能方面也會差一些。

如果要刪除外鍵約束可使用如下命令：
mysql > alter table ss_accesscode drop foreign key 外鍵約束名稱；

注：添加外鍵約束時若沒有指定外鍵約束的名稱，則系統會自動添加外鍵約束名：表名_ibfk_n(表示第n個外鍵約束)。比如我們創建外鍵時就省略了外鍵線束名稱。

spring+hibernate+mysql 只可以使用mysql的innodb設置外鍵進行級聯刪除？

CREATE TABLE SC(
Sno CHAR(8) ,
foregin key (Sno) references Student(Sno)
);

友情提示一下樓主，MySQL的外鍵只能在InnoDB表中使用。這個很關鍵，因為一般都會使用默認的MyISAM。

 

在MySQL中怎啟動innoDB引擎，以便我設置外鍵約束？

改一下默認表的引擎就可以了:alter table 表的名字 engine=innodb;