数据库常见约束——外键

一、外键的定义

外键
用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束,用于引用主表中某列的值。比如学生表的专业编号,员工表的部门编号,员工表的工种编号。

二、为什么要使用外键

下面本文将从一个例子出发来阐述使用外键的原因。
如何将某京东用户Ryan的信息及其多个商品邮寄地址保存到数据库中呢?

方案一
用单张表来保存用户信息以及商品邮寄地址,如下所示:

create table user_info(//创建user_info表
    id int(10) primary key,
    user_name varchar(30) not null,
    password varchar(30) not null,
    real_name varchar(8),
    mobile char(11),
    address varchar(150)
);

//向user_info表中插入若干条数据
insert into user_info (id,user_name,password,real_name,mobile,address) 
values (1,'Ryan','123456','张三','18920120206','河南安阳');
insert into user_info (id,user_name,password,real_name,mobile,address) 
values (2,'Ryan','123456','李四','18617297545','北京海淀');
insert into user_info (id,user_name,password,real_name,mobile,address)
values (3,'Ryan','123456','王五','17694976949','山西大同');

结果如下
在这里插入图片描述
由于每个用户可以有多个收货地址,可以看出这种方法创建的表存在较为严重的字段冗余(user_name和password列),而随着该表中的个人信息字段越来越多,则这一问题表现的就越严重,所以不推荐使用。
为了解决字段冗余的问题我们可以创建两张表,一张用来保存个人信息,另外一张用来保存收货地址。

方案二
设计两张表:一张表保存用户信息,一张表保存商品邮寄信息,如下所示:
表一

create table user_info(//创建user_info表保存用户信息
    id int(10) primary key,
    user_name varchar(30) not null,
    password varchar(30) not null
);
//向user_info表中插入一条数据
insert into user_info (id,user_name,password) values (1,'Ryan','123456');

结果如下
在这里插入图片描述

表二

create table address(//创建address表保存商品邮寄信息
    id int(10) primary key,
    user_info_id char(36),
    real_name varchar(8) not null,
    mobile char(11) not null,
    address varchar(150) not null
);
//向address表中插入若干条数据
insert into address (id,user_info_id,real_name,mobile,address) 
values (1,1,'张三','18920120206','河南安阳');
insert into address (id,user_info_id,real_name,mobile,address) 
values (2,1,'李四','18617297545','北京海淀');
insert into address (id,user_info_id,real_name,mobile,address) 
values (3,1,'王五','17694976949','山西大同');

结果如下
在这里插入图片描述
这种方法虽然解决了字段的冗余问题,但由于只是逻辑上的“外键”关系,在插入数据的时候需要靠数据库操作者的自觉性,所以依然无法保证数据完整性,有可能会出现垃圾信息(没有用的信息)。
如果这里在向address表中添加一条数据,并且该数据没有与之对应的用户信息则这里添加的数据就是没有用的信息。

insert into address (id,user_info_id,real_name,mobile,address) values (4,6,'XiaoMing','18338970095','China');

在这里插入图片描述
如果这里如果把user_info表中的数据删除,但此时地址表中数据将不再完整,这些地址没有与之对应的用户。此时addres表中与之前用户对应的收货地址成为了垃圾信息。
在这里插入图片描述
在这里插入图片描述
这里只是逻辑上的“外键”关系,所以还需要手动删除address表中对应的数据。
显然逻辑上的"外键"关系需要依靠操作者的自觉性,依然无法保证数据的完整性。

方案三
只需在address表中添加外键约束即可。如下所示:

create table address(//创建address表保存商品邮寄信息
    id int(10) primary key,
    user_info_id char(36),
    real_name varchar(8) not null,
    mobile char(11) not null,
    address varchar(150) not null,
    constraint address_user_info_id_fk foreign key(user_info_id) references user_info(id)//添加外键约束
);

分析:这种方案为user_info_id添加了外键,指向user_info表的主键,该约束起到了保护数据完整性的作用:如果删除的用户信息id已经在address表中使用,则该条数据无法删除;也无法向address表中添加用户id不存在的地址信息。

三、外键使用时的注意事项

1、要求在从表设置外键关系;
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求;
3、主表的关联列必须是一个key(一般是主键或唯一);
4、插入数据时,先插入主表,再插入从表;删除数据时,先删除从表,再删除主表;
5、可以通过以下两种方式来删除主表的记录:
  方式一:级联删除:ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;
  方式二:级联置空:ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL。

  • 3
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库课程设计中的超市销售系统是一个常见的实践项目,旨在让学生了解并应用关系数据库理论和设计原则。这个系统通常包括以下几个关键部分: 1. **需求分析**:首先,你需要明确系统的功能需求,如商品管理(添加、删除、查询)、顾客管理、购物车、订单处理、库存跟踪等。 2. **概念模型设计**:使用E-R图(实体-关系图)来描绘实体(如商品、顾客、订单)、属性和它们之间的关系,如一对多、一对一或多对多。 3. **逻辑模型设计**:选择一种关系数据库管理系统(如MySQL、Oracle或SQL Server),将概念模型转换为适合该系统的表结构和关系。 4. **表设计**:创建具体的数据库表,每个表代表一个实体,包含字段以存储数据,如商品ID、名称、价格,顾客ID、姓名,订单ID、商品ID、数量等。 5. **关系约束和索引**:确保数据的一致性和完整性,添加外键约束和适当的索引来提高查询性能。 6. **存储过程和触发器**:可能的话,编写存储过程来执行复杂操作,如批量更新库存,或使用触发器来自动完成某些任务,比如在订单创建后更新库存。 7. **用户界面设计**:设计用户界面,让客户能够浏览商品、添加到购物车、结账、查看历史订单等。 8. **数据输入/输出**:考虑如何处理用户输入,如验证数据格式,以及如何生成报表或邮件通知。 9. **安全性**:确保系统对用户身份的验证和权限控制,保护敏感信息。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值