mysql set foreign key_Mysql 外键(FOREIGN KEY)使用注意事项

外键，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;