示例:
-- 创建部门表
drop table if exists tb_dept;
create table tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 创建员工表
drop table if exists tb_emp;
create table tb_emp(
id int primary key auto_increment,
name varchar(10),
age int,
dep_id int,
-- 添加外键 dep_id关联tb_dept的id主键
constraint fk_emp_dept foreign key(dep_id) references tb_dept(id)
);
效果如下:
示例:
/*
多对多案例:商品和订单
建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
*/
-- 创建订单表
drop table if exists tb_order;
create table tb_order(
id int primary key auto_increment,
payment double(7,2),
payment_type varchar(20),
status tinyint
);
-- 创建商品表
drop table if exists tb_goods;
create table tb_goods(
id int primary key auto_increment,
title varchar(30),
price double(7, 2)
)
-- 创建订单商品中间表
drop table if exists tb_order_goods;
create table tb_order_goods(
id int primary key auto_increment,
order_id int,
goods_id int,
count int
);
-- 建完表后添加外键
alter table tb_order_goods add constraint fk_order_id foreign key(order_id) references tb_order(id);
alter table tb_order_goods add constraint fk_goods_id foreign key(goods_id) references tb_goods(id);
-- 删除外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
alter table tb_order_goods drop foreign key fk_order_id;
alter table tb_order_goods drop foreign key fk_goods_id;
效果如下:
-- 创建一对一的表
-- 在任意一方加入外键,关联另一方主键,并且设置外键为唯一(UNIQUE)
-- 创建tb_user用户表
drop table if exists tb_user;
create table tb_user(
id int primary key auto_increment,
photo varchar(20),
nickname varchar(30),
age int,
gender char(1),
desc_id int UNIQUE
);
-- 创建tb_user_desc用户详情表
drop table if exists tb_user_desc;
create table tb_user_desc(
id int primary key auto_increment,
city varchar(10),
edu varchar(10),
income double(7, 2),
statusType varchar(10),
descType varchar(30)
);
-- 添加外键
alter table tb_user add constraint fk_tb_user_desc foreign key(desc_id) references tb_user_desc(id);
效果如下: