Mysql 外來鍵(FOREIGN KEY)使用注意事項

Steven1981發表於2010-02-02
MySql
外來鍵，FOREIGN KEY, 這個東東，作為DBA，在Oracle我們都不建議在資料庫級別去實現約束，因為他的維護成本很高，
比如你要保證索引，匯入資料時你得保證先後順序等，所以我們更推薦由應用去控制邏輯。

在MYSQL中是更不推薦使用，不過在這裡主要是說說使用過程中要注意的問題。[@more@]## 建立約束，注意命名規範 FK1，FK2，FK3 ... 如果不指定約束名，系統會自動建立一個。
create table ... ...
constraint `FK1` foreign key (`user_id`) REFERENCES `user`(`id`)
ON DELETE CASCADE ON UPDATE CASCADE


## 相應的欄位（foreign key and the referenced key ），
Corresponding columns in the foreign key and the referenced key
>> 必須具有相同的內部資料型別；
must have similar internal data types inside InnoDB so that they can be compared without a type conversion.
>> 整型欄位的資料長度必須一樣；
The size and sign of integer types must be the same.
>> 字元的長度可以不一樣；
The length of string types need not be the same. For non-binary (character) string columns
>> 非二進位制字元欄位，the character set and collation 也必須一樣；
For non-binary (character) string columns, the character set and collation must be the same.

## 如果一個INNODB表有外來鍵，那麼他將不能直接轉變儲存引擎，除非把外來鍵給刪除了。
if an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first

=========================================================================
root@127.0.0.1 : test 12:21:05> alter table audit engine=myisam;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
root@127.0.0.1 : test 12:21:06>


root@127.0.0.1 : test 12:25:40> alter table audit drop foreign key FK1;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

root@127.0.0.1 : test 12:25:46> alter table audit engine=myisam;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
=========================================================================

## set FOREIGN_KEY_CHECKS = 0.
可以讓表不按依賴關係匯入；mysqldump就是這麼做的。
This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded

## 刪除約束，請指定正確的約束名

create table user (id int ,username varchar(20) , primary key (id) ) engine=innodb ;

create table audit (id int ,user_id int , primary key (id) ,
constraint foreign key (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) engine=innodb ;

insert into user values (1,'heyf'); insert into audit values (1,1);

=========================================================================
root@127.0.0.1 : test 11:00:19> alter table audit drop FOREIGN KEY user_id ;
ERROR 1025 (HY000): Error on rename of './test/audit' to './test/#sql2-4847-c' (errno: 152)

###### 這裡為什麼會報錯呢？？

root@127.0.0.1 : test 11:00:19> show innodb status G

LATEST FOREIGN KEY ERROR
------------------------
100202 11:00:30 Error in dropping of a foreign key constraint of table test/audit,
in SQL command
alter table audit drop FOREIGN KEY user_id
Cannot find a constraint with the given id user_id.

###### 系統提示說：你指定了一個錯誤的CONSTRAINT_NAME

root@127.0.0.1 : test 11:57:02> show create table audit G
*************************** 1. row ***************************
Table: audit
Create Table: CREATE TABLE `audit` (
`id` int(11) NOT NULL default '0',
`user_id` int(11) default NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT `audit_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

##### 我們看到系統自動產生的外來鍵名字不是簡單的欄位名。

root@127.0.0.1 : test 11:54:26> alter table audit drop FOREIGN KEY `audit_ibfk_1`;
Query OK, 1 row affected (0.21 sec)
Records: 1 Duplicates: 0 Warnings: 0

=========================================================================



## 刪除約束後，索引不會被自動刪除，如果有必要，請手動刪除
alter table Table_name drop index Index_name;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/703656/viewspace-1031067/，如需轉載，請註明出處，否則將追究法律責任。

相關文章