萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql外鍵約束使用詳解
mysql外鍵約束使用詳解
MySQL有兩種常用的引擎類型：MyISAM和InnoDB。目前只有InnoDB引擎類型支持外鍵約束。InnoDB中外鍵約束定義的語法如下：
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION 外鍵的使用需要滿足下列的條件：
1. 兩張表必須都是InnoDB表，並且它們沒有臨時表。
2. 建立外鍵關系的對應列必須具有相似的InnoDB內部數據類型。
3. 建立外鍵關系的對應列必須建立了索引。
4. 假如顯式的給出了CONSTRAINT symbol，那symbol在數據庫中必須是唯一的。假如沒有顯式的給出，InnoDB會自動的創建。
如果子表試圖創建一個在父表中不存在的外鍵值，InnoDB會拒絕任何INSERT或UPDATE操作。如果父表試圖UPDATE或者DELETE任何子表中存在或匹配的外鍵值，最終動作取決於外鍵約束定義中的ON UPDATE和ON DELETE選項。InnoDB支持5種不同的動作，如果沒有指定ON DELETE或者ON UPDATE，默認的動作為RESTRICT:
1. CASCADE: 從父表中刪除或更新對應的行，同時自動的刪除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
2. SET NULL: 從父表中刪除或更新對應的行，同時將子表中的外鍵列設為空。注意，這些在外鍵列沒有被設為NOT NULL時才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
3. NO ACTION: InnoDB拒絕刪除或者更新父表。
4. RESTRICT: 拒絕刪除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE選項的效果是一樣的。
5. SET DEFAULT: InnoDB目前不支持。
外鍵約束使用最多的兩種情況無外乎：
1）父表更新時子表也更新，父表刪除時如果子表有匹配的項，刪除失敗；
2）父表更新時子表也更新，父表刪除時子表匹配的項也刪除。
前一種情況，在外鍵定義中，我們使用ON UPDATE CASCADE ON DELETE RESTRICT；後一種情況，可以使用ON UPDATE CASCADE ON DELETE CASCADE。
InnoDB允許你使用ALTER TABLE在一個已經存在的表上增加一個新的外鍵:
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
InnoDB也支持使用ALTER TABLE來刪除外鍵：
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
約束
約束保證數據的完整性與一致性
約束分為表級約束和列級約束
約束類型包括 -- NOT NULL (非空約束) -- PRIMARY KEY (主鍵約束) -- UNIQUE KEY (唯一約束) -- DEFAULT (默認約束) -- FOREIGN KEY (外鍵約束)
外鍵約束的要求解析
FOREIGN KEY 保證數據的一致性,完整性. 實現一對一或一對多關系
父表與子表必須使用相同的存儲引擎,而且禁止使用臨時表.
數據表的存儲引擎只能為InnoDB
外鍵列和參照列必須具有相似的數據類型.其中數字的長度或者是否有符號位必須相同;而字符的長度則可以不同.
外鍵列和參照列必須創建索引.如果外鍵列不存在索引的話,MySQL將自動創建索引
顯示數據表的創建數據
SHOW CREATE TABLE tbl_name;
顯示數據表索引
SHOW INDEXES FROM tbl_name;
創建兩個表並且進行外鍵約束
``` CREATE TABLE provinces( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL );
CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) );
``` 外鍵與主鍵數據類型必須的一直 比如 pname SMALLINT 對應的 pid SMALLINT 而且符號位也必須相同 有外鍵的表稱為子表 子表參照的表稱為附表 參照列自動創建了索引
外鍵約束的參照操作
CASCADE:從父表刪除或更新且自動刪除或更新子表中匹配的行
SET NULL:從父表刪除或更新行,並設置子表中的外鍵列為NULL.如果使用該選項,必須保證子表列沒有指定NOT NULL.
RESTRICT:拒絕對父表的刪除或更新操作.
NO ACTION:標准的SQL關鍵字,在MYSQL中與RESTRICT相同.
外鍵約束僅僅支持innodb引擎
CREATE TABLE user1( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE ); 參照操作需要開啟ON DELETE後再加上參數
添加數據
``` INSERT provinces(pname) VALUES("A"); INSERT provinces(pname) VALUES("B"); INSERT provinces(pname) VALUES("C");
//插入用戶數據
INSERT user1(username,pid) VALUES("Tom",3); ``` 插入用戶數據只需要對應設置外鍵的id即可.
如果記錄沒有寫入成功,但是編號會自動遞增
刪除記錄
刪除provinces id為3的記錄 DELETE FROM provinces WHERE id = 3; 那麼相應的子表中外鍵id為3的也會刪除.
表級約束與列級約束
對一個數據列建立的約束,稱為列級約束.
對多個數據列建立的約束,稱為表級約束.
列級約束既可以在列定義時聲明,也可以在列定義後聲明.
表級約束只能在列定義後聲明.
修改數據表
添加單列
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name] FIRST 指的是插入的這一列會在最前面.AFTER col_name則是在某一列後面
添加單列數據
SHOW COLUMNS FROM user1; ALTER TABLE user1 ADD age INT UNSIGNED NOT NULL DEFAULT 10; ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;//插入某列後面 ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST; DEFAULT:未明確表明數據的時候.默認指定的數據 UNSIGNED:針對數值型類型 是否有符號
添加多列
添加多列無法指定位置
ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition);
給user1添加多列數據
ALTER TABLE user1 ADD( num INT UNSIGNED NOT NULL DEFAULT 1, sex ENUM("男","女") NOT NULL DEFAULT "男" );
刪除列
ALTER TABLE user1 DROP truename;
刪除多列
ALTER TABLE user1 DROP truename,DROP password;
修改列的時候可以刪除的時候再添加.中間通過逗號分隔.
刪除的同時再添加列
ALTER TABLE user1 DROP num, ADD pm FLOAT UNSIGNED DEFAULT 15;
添加主鍵約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,...)
范例一
CREATE TABLE user2( username VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED ); //增加主鍵 ALTER TABLE user2 ADD id SMALLINT UNSIGNED; //增加主鍵約束 ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY (id); CONSTRAINT:用來起別名
添加唯一約束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name,...)
范例一
ALTER TABLE user2 ADD UNIQUE (username);
添加外鍵約束
范例一
user2中pid參照provinces; ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);
添加/刪除默認約束
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
范例一
ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL; ALTER TABLE user2 ALTER age SET DEFAULT 15; 給age添加默認約束
刪除主鍵約束
任何一個表有且只有一個主鍵 ALTER TABLE user2 DROP PRIMARY KEY;
刪除主鍵索引
ALTER TABLE user2 DROP INDEX username; 刪除的僅僅是索引
顯示索引列表名稱
SHOW INDEXes FROM user2;
刪除外鍵約束
刪除約束是刪除約束的名稱 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbok
范例
``` //查詢外鍵名稱
SHOW CREATE TABLE user2; //找到pid的外鍵名稱為 user2_ibfk_1;然後進行刪除
ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;
雖然外鍵不在了,但是索引還是存在的 如果不想要索引則刪除即可 ALTER TABLE user2 DROP INDEX pid; ```
修改列定義和更名數據表
修改列定義
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name]
修改列順序
ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; column_definition指的是列定義 不變
修改列定義-范例
ALTER TABLE user2 MODIFY id TINYINT UNSIGNED NOT NULL; 修改列定義的時候有可能會造成數據的丟失.
修改列名稱
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new col_name column_definition [FIRST |AFTER col_name]
修改id的類型與名稱 ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;
數據表更名
一. ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
二. RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2];
范例 ``` ALTER TABLE user2 RENAME users2;
RENAME TABLE users2 TO user2; ```
多個外鍵存在:
product_order表對其它兩個表有外鍵。
一個外鍵引用一個product表中的雙列索引。另一個引用在customer表中的單行索引：
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
-- 雙外鍵
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
-- 單外鍵
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
(6) 說明:
1.若不聲明on update/delete,則默認是采用restrict方式.
2.對於外鍵約束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式.
總結
約束 - 按功能化為:NOT NULL,PRIMARY KEY, UNIQUE KEY, DEFAULT,FOREIGN KEY - 按數據列的數目化為:表級約束,列級約束
修改數據表 - 針對字段的操作:添加/刪除字段,修改列定義,修改列名稱等 - 針對約束的操作:添加/刪除各種約束. - 針對數據表的操作:數據表更名(兩種方式).
列級約束：只能應用於一列上。 表級約束：可以應用於一列上，也可以應用在一個表中的多個列上。
默認約束(DEFAULT)與非空約束(NOT NULL)不存在表級約束
