1.外键的创建,用代码写的
具体的语法格式如下:
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [,字段名2,…]
REFERENCES <主表名> 主键列1 [,主键列2,…]
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`bookNo` char(9) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`ISBN` char(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`location` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`bstatus` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`ctr_no` tinyint(4) NOT NULL,
PRIMARY KEY (`bookNo`) USING BTREE,
INDEX `ISBN`(`ISBN`) USING BTREE,
CONSTRAINT `books_ibfk_1` FOREIGN KEY (`ISBN`) REFERENCES `bookinfo` (`ISBN`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
2.外键用Navicat工具创建
3.外键约束 之CASCADE、SET NULL、RESTRICT、NO ACTION分析和作用
CASCADE:父表delete、update的时候,子表会delete、update掉关联记录;
SET NULL:父表delete、update的时候,子表会将关联记录的外键字段所在列设为null,所以注意在设计子表时外键不能设为not null;
RESTRICT:如果想要删除父表的记录时,而在子表中有关联该父表的记录,则不允许删除父表中的记录;
NO ACTION:同 RESTRICT,也是首先先检查外键;