-- 外键约束(foreign key)
-- 创建一个员工表employee
-- 员工编号id,员工的姓名name,部门名称dept_name,员工所在的地址address
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
address VARCHAR(20),
dept_name VARCHAR(20)
);
-- 给员工表中插入数据
INSERT INTO employee (NAME,address,dept_name)
VALUES('文章','北京','研发部'),
('柯发兴','西安','测试部'),
('高圆圆','上海','研发部'),
('张三','深圳','实施部'),
('李四','广州','实施部'),
('赵又廷','香港','测试部');
-- 查询员工表中的所有数据
SELECT * FROM employee ;
DROP TABLE employee;-- 查询 员工的姓名以及所在的部门名称
SELECT
NAME '姓名',
dept_name '部门名称'
FROM
employee ;
/*
问题:
id = 1 研发部
id = 3 研发部
id = 2 测试部
id = 6 测试部
...
字段冗余(重复的内容太多了),查询出来的数据不太直观
*/
-- 将上面这个问题进行改进:将员工表拆分成两个表
-- 员工表:记录员工的信息
-- 部门表:记录部门的编号以及部门的名称
DROP TABLE employee;-- 部门表:dept表:主键自增长id(部门编号) dept_name部门名称
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR (10)
) ;
DROP TABLE dept;
-- 插入数据
INSERT INTO dept (dept_name)
VALUES
('研发部'),
('测试部'),
('实施部') ;
SELECT * FROM dept;-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARBINARY(10), -- 员工姓名
address VARBINARY(20), -- 员工地址
dept_id INT -- 部门编号
);
INSERT INTO employee (NAME,address,dept_id)
VALUES('文章','北京',1),
('柯发兴','西安',2),
('高圆圆','上海',1),
('张三','深圳',3),
('李四','广州',3),
('赵又廷','香港',2);SELECT *FROM employee;
SELECT
NAME '姓名',
dept_id '部门编号'
FROM
employee ;-- 继续改进:想办法需要将部门表中部门编号主键id和员工表的部门表dept_id的关联起来
-- 解决方案:加入外键
-- 主表(部门表:dept),从表employee(外键所在的表)
-- constraint声明 外键名称(主表名称_从表名称_fk)
-- foreign key (从表的字段) reference 主表的名称(主键名称);
DROP TABLE employee;
-- 创建员工表并且添加外键约束
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,-- 员工编号
NAME VARBINARY(10), -- 员工姓名
address VARCHAR(20),-- 员工地址
dept_id INT,
CONSTRAINT -- 声明
dept_emp_fk FOREIGN KEY(dept_id)
REFERENCES dept(id)
);-- 给员工表中添加数据
INSERT INTO employee (NAME,address,dept_id)
VALUES('文章','北京','1'),
('柯发兴','西安','2'),
('高圆圆','上海','1'),
('张三','深圳','3'),
('李四','广州','3'),
('赵又廷','香港','2');SELECT *FROM dept;
SELECT*FROM employee;
-- 添加数据(部门编号必须存在,否则报错!)
INSERT INTO employee (NAME,address,dept_id) VALUES ('马伊琍','西安','4');
-- 由于存外键,不能直接添加或者修改从表数据(跟外键声明的字段相关)
-- 先操作主表---->然后才能操作从表
-- 添加的操作
-- 主表
INSERT INTO dept(dept_name)VALUES('销售部');-- 删除外键
-- alter table 从表名称 DROP FOREIGN KEY 外键名称
ALTER TABLE employee DROP FOREIGN KEY dept_emp_fk;-- 通过sql语句添加外键
ALTER TABLE employee
ADD CONSTRAINT -- 声明
dept_emp_fk FOREIGN KEY(dept_id)
REFERENCES dept(id);
mysql约束之_外键约束 foreign key
最新推荐文章于 2023-04-06 11:39:42 发布