数据库关系模型（约束和触发器）

最新推荐文章于 2022-12-28 19:06:29 发布

冰冰糖yyy

最新推荐文章于 2022-12-28 19:06:29 发布

阅读量1.4k

点赞数 3

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_41679818/article/details/105662048

版权

数据库专栏收录该内容

14 篇文章 2 订阅

订阅专栏

一、基本概念

1.基本术语（重要，记住！！！）

• 关系：是用于描述数据的一张二维表，组成表的行称为元组，组成表的列称为属性。

• 域（ Domain ）：指列（或属性）的取值范围。

• 候选键 （ Candidate Key ）：也称为候选码。能唯一的标识关系中每一个元组的最小属性集。一个关系可能有多个候选键。

• 主键（ Priamary Key ， PK ）：也称为主码。一个唯一识别关系中元组的最小属性集合。可以从关系的候选键中，指定其中一个作为关系的主键。一个关系最多只能指定一个主键。要求作为主键的列不允许取 NULL 值。

• 主属性 ：候选键中所有的属性均称为主属性。

• 非主属性 ：不包含在任何候选键中的属性称为非主属性。

• 全码：关系中所有属性的组合是该关系的一个候选码，则该候选码称为全码。

• 外键（ Foreign Key ， FK ）：关系 R 中的某个属性 K 是另一个关系 S 中的主键，则称该属性 K 是关系 R 的外键。通过外键可以建立两表间的联系。

二、数据库完整性

数据库的完整性是指数据的正确性和相容性。

为了维护数据库的完整性，DBMS必须能够：

（1）提供定义完整性约束条件的机制

（2）提供完整性检查的方法

（3）违约处理

1.三类完整性规则

1.实体完整性规则

实体完整性给出了主键的取值的最低约束条件。

规则4.1 主键的各个属性都不能为空值。

2.参照完整性

参照完整性给出了在关系之间建立正确的联系的约束条件。

定义4.1 设F是关系R的一个或一组属性（但F不是R的主键），K是关系S的主键。如果F与K相对应，则称F是关系R的外键，并称关系R为参照关系，关系S为被参照关系。而关系R和关系S可以是同一个关系。

规则4.2 外键或者取空值（要求外键的每个属性均为空值），或者等于被参照关系中的主键的某个值。

3.用户定义的完整性

规则4.3 属性的取值应当满足用户定义的约束条件。

DBMS应该提供定义和检验这类完整性的机制（如约束Check、触发器Trigger等），以便用统一的方法来处理它们，而不应该由应用程序来承担这个功能。

2.MySQL提供的约束

1.主键（PRIMARY KEY）约束

主键约束主要是针对主键，以保证主键值的完整性。主键约束要求主键值必须满足两个条件：

（1）值唯一；

（2）不能为空值。

主键约束分列级和表级两种定义方式。列级针对表中一列，而表级则针对同一表中个一列或多列。

#创建表的同时，指定主键约束
DROP TABLE IF EXISTS employee;
CREATE TABLE employee
( empno DECIMAL(2),
  ename VARCHAR(8),
	age DECIMAL(3),
	deptno DECIMAL(2),
	CONSTRAINT pk_no PRIMARY KEY(empno,deptno)
);
DESC employee;
#删除主键约束
ALTER TABLE employee
DROP PRIMARY KEY;
DESC employee;
#添加主键约束
ALTER TABLE employee
ADD CONSTRAINT pk_no PRIMARY KEY(empno);
DESC employee;

2.唯一约束

唯一约束主要是针对于候选键，以保证候选键值的完整性。唯一约束要求候选键满足2个条件：

（1）值唯一；

（2）可有一个且仅有一个空值。

（1）建立employee表，在employee表中定义一个phone字段，并为phone字段定义指定名称的唯一约束。

CREATE  TABLE  employee
( empno   DECIMAL(2)  PRIMARY  KEY,
  name    VARCHAR(8),
  age     DECIMAL(3),
  phone   VARCHAR(12),
  deptno  DECIMAL(2),
  CONSTRAINT  emp_phone  UNIQUE(phone)
);

（2）删除唯一约束emp_phone。

ALTER TABLE employee

 DROP  INDEX  emp_phone;

（3）为已有表employee根据phone字段创建唯一约束，约束名为emp_phone。

ALTER  TABLE  employee

 ADD  CONSTRAINT  emp_phone   UNIQUE(phone);

3．检查约束

MySQL数据库中可以设置检查约束，但是检查约束在表中是不生效的，也就是说仍然可以插入不符合条件的数据。

DROP TABLE IF EXISTS employee;
CREATE TABLE  employee
( empno  DECIMAL(2) PRIMARY KEY,
 ename   VARCHAR(8),
 age    DECIMAL(3),
 phone  VARCHAR(12)  UNIQUE,
 deptno  DECIMAL(2),
 CONSTRAINT age_CK  CHECK (age>20 AND age<60)
);
DESC employee;
INSERT INTO employee(empno,ename,age)VALUES(10,'Jack',70);
 
SELECT * FROM employee;

结果：

4.外键约束

外键约束涉及到两个表，即主表和从表，从表是指外键所在的表，主表是指外键在另一张表中作为主键的表。

外键约束要求：被定义为外键的字段，其取值只能为主表中引用字段的值或NULL值。

Q:对主表主键进行 INSERT 、 DELETE 、 UPDATE 操作，会对从表有什么影响呢？

（1）插入（INSERT）

主表中主键值的插入，不会影响从表中的外键值。

（2）修改（UPDATE）

如果从表中的外键值与主表中的主键值一样的话，主表中主键值的修改要影响从表中的外键值。

（3）删除（DELETE）

主表中主键值的删除，可能会对从表中的外键值产生影响，除非主表中的主键值没有在从表中的外键值中出现。

Q:对从表外键的操作对完整性有什么影响呢？

（1）插入（INSERT）

插入从表的外键值时，要求插入的外键值应“参照”（REFERENCE）主表中的主键值。

（2）修改（UPDATE）

修改从表的外键值时，要求修改的外键值就“参照”主表中的主键值。

（3）删除（DELETE）

从表中元组的删除不需要参照主表中的主键值。

实现表间数据完整性的维护，可有以下两种方式。

（1）利用外键约束定义，即在表上定义外键约束，来完成主表和从表间两个方向的数据完整性。

（ 2 ）利用触发器完成两表间数据完整性的维护，即主表的触发器维护主表到从表方向的数据完整性，而从表的触发器维护从表到主表方向的参照完整性。

(1）建立employee和department表，实现两表间的外键约束。

DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS department;
CREATE  TABLE  department
( deptno     DECIMAL(5)  PRIMARY  KEY,
  dept_name  VARCHAR(16)
);
CREATE  TABLE  employee
( empno   DECIMAL(5)  PRIMARY  KEY,
  name    VARCHAR(10)  NOT  NULL,
  age     DECIMAL(3),
  deptno  DECIMAL(5),
CONSTRAINT  FK_ID  
FOREIGN  KEY(deptno)  REFERENCES  department(deptno) 
ON  UPDATE  CASCADE
);
DESC department;
DESC employee;

（2）删除employee表上的FK_ID约束。

ALTER  TABLE  employee
  DROP  FOREIGN  KEY  FK_ID;

级联删除

练习：创建一张表 render，render数据复制题目中借阅表数据，并添加外键（借阅证号）参考读者表（借阅证号），当读者表删除一条数据时，render 表对应的外键所在行信息也会删除。

CREATE TABLE render SELECT * FROM borrow;
ALTER TABLE render 
  ADD CONSTRAINT FK_ID FOREIGN KEY(borrnum)
	REFERENCES reader(borrnum)
	ON DELETE CASCADE;
DESC render;

ON UPDATE CASCADE 级联更新

ON DELETE CASCADE 级联删除

3.触发器

利用AFTER触发器维护表间的数据一致性。

具体做法是：主表和从表应分别建立各自的触发器，主表的触发器维护主表到从表方向的数据完整性，而从表的触发器维护从表到主表方向的参照完整性。

这里的主表和从表不需要定义外键，只需要有共同的列即可。

触发器创建的语法如下：

CREATE TRIGGER 触发器名

BEFORE | AFTER

INSERT | DELETE | UPDATE

ON 表名

FOR EACH ROW

< 触发体 >

【例4-7】创建触发器delete_trigger触发器，触发器将记录哪些用户删除了department表中的数据，以及删除的时间。

CREATE TABLE merch_log
(who VARCHAR(30),
oper_date  DATE
);
CREATE   TRIGGER  delete_trigger
  AFTER  DELETE
  ON  department
  FOR  EACH ROW
  INSERT  INTO  merch_log(who,oper_date) 
               VALUES(USER(),SYSDATE());

SELECT * FROM merch_log;

为了测试该触发器是否正常运行，在department表中删除10号部门的记录；并查询日志信息表merch_log。

DELETE  FROM  department  WHERE  deptno=11;

SELECT  *  FROM  merch_log;

练习：创建触发器tigger_db，该触发器实现当删除读者表中记录时，一并删除借阅表中关于该读者的数据。

DROP TRIGGER IF EXISTS trigger_db;
CREATE TRIGGER trigger_db
AFTER DELETE ON reader FOR EACH ROW 
DELETE FROM borrow WHERE borrnum=old.borrnum;
DELETE FROM reader WHERE borrnum=125;

冰冰糖yyy

关注

3
点赞
踩
8

收藏

觉得还不错? 一键收藏
2
评论
数据库关系模型（约束和触发器）

一、基本概念 1.基本术语（重要，记住！！！）• 关系：是用于描述数据的一张二维表，组成表的行称为元组，组成表的列称为属性。 • 域（Domain）：指列（或属性）的取值范围。 • 候选键（Candidate Key）：也称为候选码。能唯一的标识关系中每一个元组的最小属性集。一个关系可能有多个候选键。 • 主键（Priamary Key，PK）：也称为主码。一个唯一...
复制链接

扫一扫