MySQL的外键约束FOREIGN KEY

一、建立外键约束

[CONSTRAINT [symbol]] FOREIGN KEY
 [index_name] (col_name, ...)
 REFERENCES tbl_name (col_name,...)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]
reference_option:
 RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

1.CONSTRAINT [symbol]]指定约束名字，不写则系统自动生成外键约束名；
2.MySQL强制外键约束的列上必须要有索引， [index_name]指定索引名，不写则系统自动生成索引名，如果已经存在可以用于支持外键约束的索引，那么指定的索引名则无效，采用已有的索引；
3.[ON DELETE reference_option]和[ON UPDATE reference_option]指定主表update和delete操作时，子表对应的数据怎么响应；
  • cascade:从父表删除或者更新且自动删除或更新子表中匹配的行
  • set null:从父表删除或更新行，并设置子表中的外键行为null,如果使用该选项，必须保证子表列没有指定not null
  • restrict：拒绝对父表的删除或更新操作。
  • no action:标准SQL的关键字，在mysql中于restrict相同

二、添加外键约束

ALTER TABLE tbl_name
 ADD [CONSTRAINT [symbol]] FOREIGN KEY
 [index_name] (col_name, ...)
 REFERENCES tbl_name (col_name,...)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]

添加外键约束的两种方式
Alter table 表名 add constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(主键字段名)；
-- 添加外键约束
CREATE TABLE stu(
    sid INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL
);
-- 添加外键约束方式一
CREATE TABLE score1(
    score DOUBLE,
    sid INT,
    CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid)
);
-- 添加外键约束方式二(若表已存在，可用这种)
CREATE TABLE score1(
    score DOUBLE,
    sid INT
);

三、删除或解除外键约束

1.查看数据库表创建的sql语句
show create table vip
2.查看外键的约束名
CREATE TABLE `vip` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_code` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK1C81D1738DA76` (`user_id`),
  CONSTRAINT `FK1C81D1738DA76` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
3.解除外键约束
alter table vip drop foreign key FK1C81D1738DA76
4.删除外键
alter table vip drop user_id

 

四、外键约束注意事项

  • 外键上无法插入主表没有的数据，也无法更新为主表没有的数据；
  • 外键上必须要有索引，主表上对应的列最好也创建索引，因为当子表外键插入数据时，也是需要到主表对应的列上去检查数据是否存在，有索引可以提高效率；
  • 如果一个表被其他表的外键引用，则这个表无法被删除；如果通过set foreign_key_checks=0强制删除主表，则子表无法插入任何数据；
  • 外键的字段类型，字符集必须要跟引用的字段一致，unsigned属性也要一致，否则无法创建外键约束；
  • 被引用的列上必须要有索引，否则无法创建外键约束。