MySQL 外键约束

外键是表的一个特殊字段

作用是建立表与父表的练习

只能使用INNODB 引擎

CREATE TABLE department(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

name VARCHAR(20) NOT NULL UNIQUE

) ENGINE = INNODB;

INSERT department(name) VALUE('设计部');

INSERT department(name) VALUE('营销部');

INSERT department(name) VALUE('管理部');

CREATE TABLE employee(

id SMALLINT UNSIGNED AUTO_INCREMENT KEY,

name VARCHAR(20) NOT NULL UNIQUE,

depId TINYINT UNSIGNED,

[CONSTRAINT emp_fk_dep]   FOREIGN KEY(depId) REFERENCES department(id) 

)ENGINE = INNODB;

#外键名称 emp_fk_dep

INSERT employee(name, depId) VALUES('Kylie', 1);

INSERT employee(name, depId) VALUES('Dannii', 2);

INSERT employee(name, depId) VALUES('jimmy', 3);

SELECT e.id, e.name, d.name AS department 

FROM employee AS e

JOIN department AS d

ON e.depId = d.id

;

DELETE FROM department WHERE id=1; #FAIL 需要先删除KYLIE

INSERT employee(name, depId) VALUES('COCO', 4); #fail 没有部门4

删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;

注意 必须定义表示指定外键名称才可以删除

添加外键

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

注意 必须employee表具有完整性并没有垃圾数据才可以。