如何正确定义外码?
- 目前只有InnoDB引擎下的表才支持外码,所以想使用外码,必须将表定义为InnoDB引擎的表。定义语句如下: ```sql CREATE TABLE Parent { Pid CHAR(10) NOT NULL PRIMARY KEY, Pid2 CHAR(10) NOT NULL PRIMARY KEY, … }ENGINE=INNODB;
CREATE TABLE Child { Pid CHAR(10), Pid2 CHAR(10), … FOREIGN KEY(Pid) REFERENCES Parent(Pid) }ENGINE=INNODB;
> 关于InnoDB和MyISAM引擎的区别,见[https://blog.csdn.net/enmotech/article/details/80249131](https://blog.csdn.net/enmotech/article/details/80249131)
2. 外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示建立
2. 外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如int和tinyint可以,而int和char则不可以
<a name="17InC"></a>
### 向已存在表中创建外码
- 是将表中已有的字段修改成外码,不是创建新的字段
```sql
ALTER TABLE Child FOREIGN KEY(Pid2) REFERENCES Parent(Pid2);
参照表级主码约束的外码创建
表级主码约束一般用于两个及以上字段形成的主码元组,作为整个表的主码。即(1,2)和(1,1)代表不同的数据主码字段,参照这种主码定义的外码创建如下
CREATE TABLE Student (
sid CHAR(20) NOT NULL,
cid CHAR(20) NOT NULL,
...
PRIMARY KEY(sid, cid)
)ENGINE=InnoDB;
CREATE TABLE SC(
sid CHAR(20) NOT NULL,
cid CHAR(20) NOT NULL,
...
PRIMARY KEY(sid, cid),
FOREIGN KEY(sid, cid) REFERENCES Student(sid, cid)
}ENGINE=InnoDB;
向已有的表中新添加这样的外码也是一个道理。如果上面的外码创建语句写成了下面这样就会出错,报错的内容就是上面这条语句找不到Student表中对应的 约束项
FOREIGN KEY(sid) REFERENCES Student(sid)
FOREIGN KEY(cid) REFERENCES Student(cid)
根本原因是 Student
表中的 PRIMARY KEY(sid, cid)
是一个约束 ,而不是两个约束,只不过约束的形式是元组
完整性约束方式
- cascade方式
- 在父表上update/delete记录时,同步update/delete掉子表的匹配记录
- set null方式
- 在父表上update/delete记录时,将子表上匹配记录的列设为null
- 要注意子表的外键列不能为not null
- no action方式
- 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
- restrict方式
- 同no action, 都是立即检查外键约束
set default方式
- 父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
创建约束
CREATE TABLE Child {
Pid CHAR(10),
Pid2 CHAR(10),
...
FOREIGN KEY(Pid) REFERENCES Parent(Pid) ON DELETE CASCADE ON UPDATE NO ACTION
}ENGINE=INNODB;
修改、添加约束
ALTER TABLE B ADD CONSTRAINT `bfk` FOREIGN KEY(`fk_column_name`) REFERENCES A(`column_name`) \
ON DELETE NO ACTION ON UPDATE NO ACTION;
删除约束
ALTER TABLE B DROP FOREIGN KEY `bfk`;
关闭外键约束检查
SET FOREIGN_KEY_CHECKS = 0;
- 父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
当然尽量还是不要用了