SQL学习之foreign key约束

参考源

• 简单教程
  • https://www.twle.cn/l/yufei/sql/sql-basic-foreignkey.html
• 菜鸟教程
  • https://www.runoob.com/sql/sql-foreignkey.html

SQL foreign key 约束

什么是 foreign key ? 就是一个表中存储另一个表的主键 ( PRIMARY KEY )

CREATE TABLE lesson (
    id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(32) default '',
    views int(11) NOT NULL default '0',
    created_at DATETIME
);

CREATE TABLE lesson_views (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lession_name varchar(32) default '',
    lession_id int(11) default '0',
    date_at  int(11) NOT NULL default '0',
    views int(11) NOT NULL default '0'
);

lesson_views 中的lesson_id 就是 lesson 表中的主键id
很多人都会有疑问，那我们一般不都是这么建表的？ 难道添加的就是 lesson_id 就是 FOREIGN KEY
不是的。我们这么添加，是因为我们开发团队有共识，但是，这种共识数据库系统不认识啊
如果不给lesson_id 添加 FOREIGN KEY约束，数据库系统会以为 lesson_id只不过是一个普通的 int(11) 字段
——简单教程https://www.twle.cn/l/yufei/sql/sql-basic-foreignkey.html

FOREIGN KEY 作用

1. FOREIGN KEY 约束用于预防破坏表之间连接的行为

   当删除一个 FOREIGN KEY 指向的主表 (lession) 记录时，如果 FOREIGN KEY 所在的表 (lession_views) 存在记录，那么会删除失败

2. FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一

   当在 FOREIGN KEY 表 ( lession_views ) 插入或更新一条记录，如果 FOREIGN KEY 指向的主表 ( lession ) 不存在该记录，那么插入或者更新失败

create table 时的 SQL foreign key 约束

给一个表添加 foreign key 约束可以使用 foreign key 关键字

MySQL

CREATE TABLE lesson_views (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lession_name varchar(32) default '',
    lession_id int(11) default '0',
    date_at  int(11) NOT NULL default '0',
    views int(11) NOT NULL default '0',
    FOREIGN KEY (lesson_id) REFERENCES lesson(id)
);

SQL Server / Oracle / MS Access

CREATE TABLE lesson_views (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lession_name varchar(32) default '',
    lession_id int(11) FOREIGN KEY REFERENCES lesson(id),
    date_at  int(11) NOT NULL default '0',
    views int(11) NOT NULL default '0'
);

给 foreign key 命名

如果想要给 foreign key 约束命名，可以使用 constraint 关键字

CREATE TABLE lesson_views (
    uniq bigint(20) primary key NOT NULL default '0' ,
    lesson_name varchar(32) default '',
    lesson_id int(11) default '0',
    date_at  int(11) NOT NULL default '0',
    views int(11) NOT NULL default '0',
    CONSTRAINT fk_lesson_id FOREIGN KEY (lesson_id) REFERENCES lesson(id)
);

alter table 时的 SQL foreign key 约束

如果一个表已经被创建，我们仍然可以使用 alter table add foreign key 来添加外键约束

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE lesson_views ADD FOREIGN KEY (lesson_id) REFERENCES lesson(id);

如果还想给 foreign key 约束命名，则可以像下面这样使用

ALTER TABLE lesson_views ADD CONSTRAINT fk_lesson_id FOREIGN KEY (lesson_id) REFERENCES lesson(id);

删除 foreign key 约束

如果想要删除一个已经命名的 foreign key 约束，可以使用 drop关键字

MySQL

ALTER TABLE lesson_views DROP FOREIGN KEY fk_lesson_id;

SQL Server / Oracle / MS Access

ALTER TABLE lesson_views DROP CONSTRAINT fk_lesson_id;