当前位置:Gxlcms > mysql > MySQL零散笔记--外键_MySQL

MySQL零散笔记--外键_MySQL

时间:2021-07-01 10:21:17 帮助过:33人阅读

Mysql外键

References: 《浅谈MySQL外键》《mysql创建外键关联》

MySQL中“键”和“索引”的定义相同, 所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引,但是外键字段必须由用户进行明确的索引。//查看Mysql手册发现从MySQL 4.1.2开始会自动建立这个INDEX

创建外键的实例代码:员工和工资表:

/*
建立员工表
*/
create table employees ( 
id int(5) not null auto_increment , 
name varchar(8) not null, 
primary key (id)
) 
type=innodb;
 
/*
建立工资表
*/
create table payroll( 
id int(5) not null, 
emp_id int(5) not null, 
name varchar(8) not null, 
payroll float(4,2) not null, 
primary key(id), 
index emp_id (emp_id),
foreign key (emp_id) references employees (id)
)
type = innodb;

参照完整性(Referentialintegrity)通常通过外键(foreign key)的使用而被广泛应用,在MySQL中通过新的InnoDB列表引擎支持。为了建立两个MySQL表之间的一个外键关系,必须满足以下三种情况:

* 两个表必须是InnoDB表类型。

* 使用在外键关系的域必须为索引型(Index)。

* 使用在外键关系的域必须与数据类型相似。

怪不得我试着创建带外键的表的时候出错呢,原来是之前的表不是InnoDB类型,果断该表表的类型:

ALTER TABLE table-name TYPE=INNODB;

居然有Warning。 show warnings之后发现原来是 “The syntax 'TYPE=storage_engine' is deprecated and will be removed in MySQL 6.0. Please use 'ENGINE=storage_engine' instead” TYPE已经OUT啦~以后要用Engine了嗯

关于表类型(type/engine)的介绍,可以在这里找到:

《浅谈MySQL表类型》,《MySQL engine/type类型InnoDB/MYISAM/MERGE/BDB/HEAP的区别》

注意事项:

  • 关系中的所有表必须是innoDB表,在非InnoDB表中,MySQL将会忽略FOREIGN KEY…REFERENCES修饰符。
  • 用于外键关系的字段必须在所有的参照表中进行明确地索引,InnoDB不能自动地创建索引。
  • 在外键关系中,字段的数据类型必须相似,这对于大小和符号都必须匹配的整数类型尤其重要。
  • 即使表存在外键约束,MySQL还允许我们删除表,并且不会产生错误(即使这样做可能会破坏更早创建的外键)

删除外键方法:

ALTER TABLE table-name DROP FOREIGN KEY key-id;

这里有一个概念,这个外键的id是啥玩意?我们可以通过SHOW CREATE TABLE 命令来获得key-id的值。

/*
显示建表结构语句,key-id为payroll_ibfk_1
*/
show create table payroll /G
/*
*************************** 1. row ***************************
       Table: payroll
Create Table: CREATE TABLE `payroll` (
  `id` int(5) NOT NULL,
  `emp_id` int(5) NOT NULL,
  `name` varchar(8) NOT NULL,
  `payroll` float(4,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id` (`emp_id`),
  CONSTRAINT `payroll_ibfk_1` FOREIGN KEY (`emp_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
 
*/

自动键更新和删除:

MySQL可能通过向FOREIGN KEY…REFERENCES 修饰符添加一个ON DELETE或ON UPDATE子句简化任务,它告诉了数据库在这种情况如何处理孤立任务。

关键字含义
CASCADE删除包含与已删除键值有参照关系的所有记录.就是删除外键记录
SET NULL修改包含与已删除键值有参照关系的所有记录,使用NULL值替换(只能用于已标记为NOT NULL的字段)
RESTRICT拒绝删除要求,直到使用删除键值的辅助表被手工删除,并且没有参照时(这是默认设置,也是最安全的设置)
NO ACTION啥也不做

请注意,通过 ON UPDATE 和ON DELETE规则,设置MySQL能够实现自动操作时,如果键的关系没有设置好,可能会导致严重的数据破坏。

人气教程排行