当前位置:Gxlcms > 数据库问题 > MySQL 数据完整性

MySQL 数据完整性

时间:2021-07-01 10:21:17 帮助过:18人阅读

数据库实验回顾

  1. 实体完整性

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

  2. 参照完整性

    参照完整性保证外码的值要么是被参照关系中的主码值,要么取空值。

  3. 用户自定义完整性

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

一、实体完整性

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来保证参照完整性。

eg

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 ;

技术图片

技术图片

MySQL 数据完整性

标签:text   where   完整   check   lan   null   图片   mysq   rom   

人气教程排行