sql server数据库完整性 sql完整性规则

转载

mob6454cc6172e5 2024-05-26 11:55:38

文章标签 sql server数据库完整性外键主键字段 文章分类 SQL Server 数据库

不管你用的是SQL Server 还是MySQL 它们都是关系型数据库，那么既然是关系型数据库就要遵守“关系型数据库的完整性规则”

关系型数据库提供了三类完整性规则，实体完整性规则，参照完整性规则，用户自定义完成性规则；

在这三类完成性规则中呢其中实体完整性规则和参照性完整性规则是关系模型必须满足的完整性的约束条件，称为关系完整性规则；

一：实体完整性规则
实体完整性规则是指关系的主属性（就是俗称主键的一些字段，主键的组成部分）不能为空值，我们知道现实生活中的每一个实体都具有唯一性，即使是两台一摸一样的计算机呢，都会有相应的生产日期来表示它们的唯一性，现实之中的实体是可以区分的，它们具有某种唯一性标识，相应的关系模型中以主键作为唯一性标识，主键中的属性即主属性不能是空值（“不知道”或“无意义”）的值，如果主属性为空值，就说明存在不可标识的实体，即存在不可区分的实体，这与现实的环境相矛盾，因此这个实体一定不是完整的实体；

可能你看这段教条很枯燥，甚至有些不明白，这样理解就简单了：咱们设计表的时候不是每条记录前面都有个自己的ID吗，并且每个ID不一样，它其实就是为了区分每条记录的不一样，尽管你的其他字段的值都一样，只要ID不一样那么就是两条不一样的记录，就区分开了，就满足了实体完整性规则；

二：参照完整性规则

指如果关系的外键R1和关系R2的主键相符，那么外键的每个值必须在关系R2中主键的值中可以找到或者是空值；

其实就是如果说在两个有关联的数据表中，一个数据表的外键（Foreigon Key）一定在另一个数据表中的主键（Primary Key）中可以找到；咱们先看图说话大家先看一下图一和图二，一张是角色表（Role），一张是用户表（User），你们会发现在用户User表里的每一个字段Role_ID的值都可以在Role表中的Role_ID字段中找到对吧；在看第三张图也就是图三，图三的最后一条记录中Role_ID的值为“7”在图一的Role表里的Role_ID里是找不到的，所以说图三不符合参照完整性规则；

三：用户自定义的完整性规则

是指某一具体的实际数据库的约束条件，由应用环境所决定，反映某一具体应用所涉及的数据必须满足的要求（比如说一个人的年龄必须要大于零），根据现实生活中的一个实际情况用户定义的一个用户自定义完整性；

比如你在设计表的时候有个年龄字段，那么这个字段里的值不能小于0吧，不能大于150吧，还没人能活到150岁对吧。

强制用于复制
指示在将表复制到另一个数据库中时是否强制该约束。

强制外键约束
指示如果对关系中列数据的更改将破坏外键关系的完整性，是否允许进行这些更改。如果不允许进行这些更改，则选择“是”，如果允许进行这些更改，则选择“否”。

● 外键
　　　　○ 定义
　　　　○ 作用
　　　　○ 主表和从表
　　　　○ 建键原则
　　　　○ 事件触发限制

　　● 外键简单实例

　　● 触发器实现
　　　　○ 建表语句
　　　　○ 关系说明
　　　　○ 实现级联删除

　　● 更改设置实现

　　● 总结

　　文首

　　今天给考试系统添加学生信息失败，原因是student与classes表有级联关系，作为从表的student表不能随意添加，这些都学过却还没怎么用过，借这次机会学习一下。

　　外键

　　说到级联删除不得不先说一下外键，外键的定义：“如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键；换而言之，如果关系模式R中的某属性集不是R的主键，而是另一个关系R1的主键则该属性集是关系模式R的外键”。

　　建立外键的sql语句：

foreign key(idB) references A(idA)

　　从语法分析也可以看出：外键是一种表之间字段值的引用关系。

　　主表和从表

　　主表和从表：以另一个关系的外键作主关键字的表被称为从表，具有此外键的表被称为主表。说白了就是：主表是被引用的表，外表是引用其他表的表。

　　那么外键有什么作用？外键作用可以说是：保持数据一致性，完整性，关联性，主要目的是控制存储在外键表中的数据。就是当你对一个表的数据进行操作，和他有关联的一个或更多表的数据能够同时发生改变，避免无效或是无用的改变和孤立的数据。

　　建键原则：

　　1、外键字段为主键

　　2、所有的键都必须唯一

　　3、避免使用复合键

　　4、外键总是关联唯一的键字段

　　外键约束事件触发。既然有外键约束，当违反约束时会发生什么？我们把它的反应看作事件，一共有四种：

　　1、级联：当主表更改时，从表跟从更改。

　　2、不执行任何操作。

　　3、设置null。

　　4、设置默认值。

　　我的看法

　　这些听着挺玄，我说说自己的看法：主表、从表和外键的定义：我把我的笔记本借给你用，可以把“我”当作是主表、“笔记本”当作外键、“你”当作从表，就是说“你”在使用（引用）“我”的“笔记本”；外键的作用就是：现在你想把我的笔记本卖了扔了送人（增加更新删除等），得看看我允许不允许，我想搬家走人，你不把笔记本还给我，我不能搬；建键原则：借之前首先咱得说好了，我只有这么一台笔记本，你也最好是也只借了我这台笔记本，避免你也借了别人的笔记本，不知道哪个是我的了。事件触发：我说你赶紧把笔记本还给我，你可以选择还给我，可以选择不搭理我等。总而言之，由于这笔记本，“你”和“我”关联起来，这台笔记本怎么样，不能一个人说了算。

简单实例

　　说了这么多，咱先说个简单的实例：建立两个表t_main和t_branch，并设置外键。语句的意思是t_main中的id是t_branch 的外键。

create table t_main ( id int primary key, [content] varchar(100) not null ) create table t_branch ( mainId int Primary key , [content] varchar(100) not null, foreign key(mainId) references t_main(id) )

　　执行语句，现在我想直接在t_branch中插入一条数据insert into t_branch values(3,'分支表')，提示如下：INSERT 语句与 FOREIGN KEY 约束"FK__t_branch__mainId__4F7CD00D"冲突。该冲突发生于数据库"beidaqingniao"，表"dbo.t_main", column 'id'。

　　我想删除t_main语句drop table t_main，提示如下：无法删除对象 't_main'，因为该对象正由一个 FOREIGN KEY 约束引用。

　　在这两个表中，该外键的作用是，t_main中没有的外键值t_branch不能插入，而t_branch 正在引用t_main所以也不能只先删除t_main，否则t_branch中的数据会被孤立。我觉得，外键对主表的作用更倾向于不能删除，而对从表的作用更倾向于不能随便添加。

　　级联删除

　　但是如果想级联删除怎么办？总结出三种办法。

　　sql语句修改设置：因为默认的外键触发是“不执行任何操作”，所以我们可以使用sql语句更改设置：

FOREIGN KEY(id)REFERENCES tabley(id) on delete cascade on update cascade

　　意思是从表会跟随主表的改变而改变。

　　可视化修改：不想写代码，也可以可视化操作，新建数据库关系图，选择主表和从表添加，其关系也会自动添加。选择主表或从表---右键---关系---insert和update规范---级联。如图：

　　当然也可以使用触发器，触发器的基础知识，欢迎访问我的博客《详解sql中的触发器》

-- ============================================= -- Author: 李达 -- Create date: 2012年6月10日 18:11:46 -- Description: 简单的级联删除 -- ============================================= ALTER TRIGGER [dbo].[trigCascadeDelete] --触发器名 ON [dbo].[main] ---作用于哪张表 instead of delete ---什么动作触发 AS BEGIN declare @id int --声明@id变量 select @id=id from deleted --取出要删除的id delete from branch where mainId =@id --先删除从表中的数据行 delete from main where id =@id ---再删除主表中的数据行 END