Mysql中外键使用注意事项有哪些

55次阅读

共计 3343 个字符，预计需要花费 9 分钟才能阅读完成。

这篇文章主要介绍了 Mysql 中外键使用注意事项有哪些，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让丸趣 TV 小编带着大家一起了解一下。

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