MySQL 数据完整性

怡红公子0526 2022-02-18

展开全文

数据库实验回顾

实体完整性

实体完整性即主码的属性不能为空。而主码就可保证元组是不重复的，即主码值是不能重复的。
参照完整性

参照完整性保证外码的值要么是被参照关系中的主码值，要么取空值。
用户自定义完整性

可以按系统的需求设计各种自定义的完整性检查。

一、实体完整性

1、主键约束

主键（promary key）用于唯一的标识表中的某一条记录，在两个表的关系中，主键用来在一个表中引用来自另一个表中的特定记录。一个表的主键可以由多个关键字共同组成，并且主键的列不能包含空值。主键的值能唯一标识表中的每一行，这就好比所有人都有身份证，每个人的身份证号是不同的，能唯一标识每一个人。

添加主键

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

设置主键

ALTER TABLE orders ADD PRIMARY KEY(列名);

在创建表的时候，设置主键

-- 单个字段的主键
CREATE TABLE 表名(
	字段名 数据类型 PRIMARY KEY
);

-- 多个字段组合的主键
CREATE TABLE 表名(
	字段名1 数据类型,
	字段名2 数据类型,
	.....
	PRIMARY KEY(字段名1, 字段名2, 字段名n)
);

2、唯一约束

唯一约束用于保证数据表中字段值的唯一性，在 MySQL 中使用 UNIQUE 关键字添加唯一约束。在创建表时为某个字段添加唯一约束的具体语法格式如下：

CREATE TABLE 表名(
	字段名 数据类型 UNIQUE,
	....
);

注意：被定义成唯一约束的字段，字段值不能相同。但是可以为 NULL。

唯一约束也可以添加到已经创建完成的表中，语法格式如下：

ALTER TABLE 表名 ADD UNIQUE(列名);

3、自动增长列

数据表中的 id 字段一般从1开始插入，不断增加，每次插入新数据时，都要添加一个 id 字段的值，当数据内容庞大时，容易出错。为了解决这个问题，可以将 id 字段的值设置为自动增加。在 MySQL 中使用 AUTO_INCREMENT 关键字设置表字段值自动增加。在创建表时将某个字段的值设置为自动增长，语法格式如下：

CREATE TABLE 表名(
	字段名 数据类型 AUTO_INCREMENT,
	....
);

此外，也可以为已经创建完成的表字段设置自动增长列，语法格式如下：

ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY AUTO_INCREMENTL;

二、参照完整性

MySQL参照完整性一般是通过MySQL外键(foreign key)实现的

删除参照约束

ALTER 表 DROP FOREIGN KEY fg_fk;

给现有表增加参照约束

ALTER TABLE`score`ADD CONSTRAINT`score_fk2`FOREIGN KEY (`sid`) REFERENCES`student`(`sid`);

数据库系统的外键值更新模式一般有3种：

默认的外键管理是Restict，即限制方式。前面实验我们已经见识了这种方式的作用。
CASCADE，即级联方式，可以理解为株连九族，即主键改变后，引用它的外键值自动改变成新主键值来保证参照完整性。
SET NULL，置空，即主键值改变后，引用它的外键值自动改为NULL来保证参照完整性。

alter table 表名
add constraint fk_js foreign key(任课教师编号) references teacher (工号) on update cascade;

三、用户自定义完整性

像MS SQL Server等数据库管理系统有CHECK约束可以很方便地实现用户自定义完整性约束。但MySQL没有提供真正的CHECK约束。但用户自定义约束的原理都差不多，通过触发器就可以实现。

例如：学生年龄不能取负值的约束

-- insert 触发器 年龄不能为负
delimiter ;
delimiter $$

create trigger st_ins_chk_age before insert
  on student
  for each row
begin
  if new.年龄 is not NULL and new.年龄 < 0 then
    signal sqlstate 'HY000'
    set message_text = "年龄不能为负";
  end if;
end$$

-- update 触发器 年龄不能为负
delimiter ;
delimiter $$

create trigger st_up_chk_age before update
  on student
  for each row
begin
  if new.年龄 is not NULL and new.年龄 < 0 then
    signal sqlstate 'HY000'
    set message_text = "年龄不能为负";
  end if;
end$$

delimiter ;

附加

不用外键，而利用触发器实现socre的课号要级联参照course中的课号？

score 表

-- 触发器 实现 score 外键 学号 参照 student 学号 -- score
delimiter ;
delimiter $$

create trigger sc_fk_ins_xh before insert
  on score
  for each row
begin
  if (select count(*) from student where 学号=new.学号)=0 and new.学号 is not NULL then
    signal sqlstate 'HY000'
    set message_text = "Cannot add or update a child row: a foreign key constraint ...";
  end if;
end$$

create trigger sc_fk_up_xh before update
  on score
  for each row
begin
  if (select count(*) from student where 学号=new.学号)=0 and new.学号 is not NULL then
    signal sqlstate 'HY000'
    set message_text = "Cannot add or update a child row: a foreign key constraint ...";
  end if;
end$$

delimiter ;

注意：score 中是 new ，student 中是 old

student 表

-- 触发器 实现 score 外键 学号 参照 student 学号 -- student
delimiter ;
delimiter $$

create trigger st_del_xh before delete
  on student
  for each row
begin
  if (select count(*) from score where 学号=old.学号)>0 then
    signal sqlstate 'HY000'
    set message_text = "Cannot delete or update a child row: a foreign key constraint ...";
  end if;
end$$

create trigger st_up_xh before update
  on student
  for each row
begin
  if (select count(*) from score where 学号=old.学号)>0 then
    signal sqlstate 'HY000'
    set message_text = "Cannot delete or update a child row: a foreign key constraint ...";
  end if;
end$$

delimiter ;