设计数据完整性

1:什么是数据完整性
2:数据完整性的分类
3:用约束实施完整性
4:用主键实施完整性 
5:用unique约束实施完整性

A:什么是数据完整性
  数据库中的数据的正确性和相容性.设计数据库的完整性的目的是为了防止数据库中存在不符合语义的数据,防止错误信息的输入,主要包含以下几个方面的内容
    A:数据类型必须正确.比如:一个姓名决不可能是数值型
    B:数据值必须处于正确的范围内:如工人的年龄应该是20----50岁之间
    C:数据的存在必须能够确保同一表格数据之间的和谐关系
       例如:已存在一个职工编号为01的职工,则不允许出现另外一个01的职工
    D:数据的存在必须能够确保维护不同表格的数据之间的和谐关系
       例如:work表中有某个职工属于某个部门,则部门表也应该对这个部门作了记录

B:数据完整性的分类:
  1:实体完整性:实体完整性的基本思想是标识数据中存放的每一个实体.每一个实体都要求保持唯一性.
     例如:有work表:职工号,姓名,性别....在这个表中要使每个工人都不相同,可以设置职工号为“主键”
  2:值域完整性:表中的每个字段取值必须符合逻辑.
     例如:性别只能是男或女,年龄不能为负数,职工号决不能为null.
  3:引用完整性:引用完整性是在插入或删除数据时,维护表格间数据一致性的手段.
     例如:
          职工工资表                    职工住址表
     职工号  职称  基本工资       职工号  姓名  性别  地址
     01      经理   1000           01    张三   男   北京
     02      厂长   1500           02    李四   女   南京
在“职工工资表”中增加一个新的职工时,为了保证“职工工资表”与“职工住址表”的对应关系,一定也要在“职工住址表”中添加相应的记录.若要删除“职工工资表”中“01”的这个职工时,也要对应地在“职工住址表”中删除“01”这个职工.
  4:用户自定义完整性
     用户自定义完整性主要是体现实际运用的业务规则,例如:
     规定 基本工资>100并且<6000等.再如:规定姓名中不许出现有“张”姓等

sql server实施数据完整性的途径
sql server主要使用三种方式来实施数据完整性:使用约束,使用规则,使用默认值,使用标识列.

C:使用约束来实施数据完整性 
约束(constraint)的用途是限制输入到表中的值的范围.sql server根据不同的用途提供了多种约束
   1:primary key [主键]约束
   2:foreign key [外键]约束
   3:unique [唯一]约束
   4:check  [核查]约束
约束可以分为表级约束和列级约束两种.
列级约束:列级约束是行定义的一部分,只能应用于一列上.
表级约束:表级约束是独立于列的定义,可以应用在一个表中的多列上.

D:用主键实施完整性
  1:primary key约束利用表中的一列或多列数据来唯一地标识一个记录.在表中绝对不允许有主键相同的行存在,受主键约束的列决不能为null值,一个表只能有一个主键,对主键操作的方式有三种
    A:在创建表格时定义主键,主键是定义表格的一部分.
        格式:create table 表名
            (列名1 数据类型1,
             列名2 数据类型2,
            [constraint 约束名] primary key(列名1,列名2...列名n) 
             ...)
        比如:create table ww 
             (职工编号 varchar(10),
             姓名 varchar(10),
             primary key (职工编号))
        说明:可以省略了“constraint 约束名”,系统会自动加上去.但建议用户取一个名字.因为如果以后要删除这个约束时,将因不知道该约束的名字而没有办法删除.

      创建表级主键约束.
          create table 班级学生
                 (班级 varchar(10) not null,
                  学号 varchar(10) not null
                  姓名 varchar(10),
                  constraint a[约束名] primary key (班级,学号)) 
     B:在一个没有定义主键的表上添加一个主键
       格式:alter table 表名
            add [contraint 约束名] 
            primary key(列名1..列名n)
       比如:先定义一个表work 
            create table work
            (职工号 varchar(10) not null,
             姓名 varchar(10),
             性别 varchar(10))  
       然后再执行:       
            alter table work
            add [constraint 职工号]
            primary key(职工号)
       试定义一个表
            create table 班级学生
            (班级 varchar(10) not null,
             学号 varchar(10) not null,
             姓名 varchar(10)) 
       然后以班级和学号建一个表级主键:
            alter table 班级学生
            add [constraint 班级,学号] 
            primary key (班级,学号) 
       说明:当在一个已经存放了数据的表上增加主键时,sql server会自动对表中的数据进行检查,以确保这些数据能否满足主键约束.当向不符合主键要求的表中增加主键时,sql server会返回错误信息并拒绝加入主键.
       试一试:create table 职工管理
              (职工编号 varchar(10), 
               姓名 varchar(10),
               性别 varchar(10))
       再执行:alter table 职工管理 
              add constraint 职工号 primary key(职工号)

     C:修改或删除表上已建立的主键 
       说明:1:修改表上已建立的主键时,只能先删除原先的主键后再重新建立一个新的主键
            2:删除主键的格式:alter table 表名 drop 主键名
            3:当主键被别的外键作用时,不允许删除主键需先将引用主键的外键删除.
              把work1表与部门表作一个外键.
              把work1的职工号作为主键,把部门表的部门编号作为主键
                主键表    外键表
                部门表    work1
              试着去执行语句:alter table 部门 drop pk_部门 [看能否成功]
              通过企业管理器:添加,删除,修改主键

E:使用unique约束
   unique约束是用来确保不受主键约束列上的数据的唯一性.
   unique与primary key的区别在于:
    1:unique约束主要用于非主键的一列或多列上要求数据唯一.
    2:unique约束允许该列上存在NULL值,而主键决不允许出现.
    3:可以在一个表创建多个unique约束,而在一个表上只能够设置一个主键
       对unique的执行操作方式也有3种
         1:在创建表格时定义
           create table 表名
           (列名1  数据类型
            列名2  数据类型
              .
              .
            unique(列1,列2....列n))
        例如:create table 商品
            (商品编号 varchar(10),
             商品名 varchar(10),
             库存量 int,
             单价 float,
             constraint 商品名 unique(商品名),
             constraint 商品编号 primary key(商品编号))
           2:在已经有数据但没有重复值的列或列集合上添加unique
             格式:alter table 表名 
                  add constraint 约束名 unique(列1....列n)
             说明:当在一个已经存在的数据表上增加unique约束时,sql会自动对表中的数据进行检查,以确保这些数据能够满足unique约束的要求,即:除null外,所有的数据必须唯一,否则出错并拒绝创建.
           3:修改或删除表上已经定义的unique约束
             要修改一个unique约束与primary key一样,必须先删除该约束,然后再创建新的约束.
             删除的格式是:alter table 表名 drop 约束名
           通过企业管理器来管理unique
6:用check约束实施完整性
7:使用外键实施完整性

F:使用check约束
check(核查)约束通过检查输入表列的数据的值来维护值域的完整性,它就像一个门卫,依次检查每一个要进入数据库的数据,只有符合条件的数据才允许进入数据库.
  例如:设置职工表的年龄在20---60之间应该用[age>=20 and age<=60]来约束.
       同前两种约束一样,check约束也有三种核查约束的方式
    1:在创建表格时
       格式:create table 表名 
            (列名 数据类型,
                  .
                  .
            constraint 约束名 check (约束表达式))
       例如:create table 职工 
            (职工号 varchar(10),
             姓名 varchar(10),
             年龄 int,
             constraint 年龄 check (年龄>=20 and 年龄<=60))
    2:在已经创建的表上添加约束
当在一个已经存在的表上添加核查时,核查约束可以只应用于新的数据,也可以应用于新的和已经存在的数据.在默认情况下将同时应用于新的数据和已经存在的数据.
       格式:alter table 表名
           [with check|with nocheck]
            add constraint check 约束名 check (约束表达式)
说明:如果选择with check则表示要对已经存在的数据进行检验,如果选择with nocheck则表示不对以前的数据进行检验.如果选择 with check,如果已经存在的数据与约束不符合则新建约束会失败.
       例如:alter table work
            with nocheck 
            add constraint age check(age>20 and age<60)
            作一个年龄约束,不对现有数据进行核查.如果省略with check选项或加上with check则表示对现有数据进行核查,如果核查错误,则拒绝加入约束.

       A:试创建一个check约束:要求满足
          1:如果是本科,基本工资>1200
          2:如果是大专,基本工资>1000
       B:试创建两个条件相矛盾的约束,看一下sql server将如何处理.

    3:修改或删除已经定义的check约束.
      1:要修改一个核查约束必须先删除该约束,再创建一个同名的约束
        格式:alter table 表名 drop 约束名
      2:可以使用下面语句使所有的约束或指定的约束无效
在某些情况下,可以使用已经定义的核查约束无效(不是删除约束),这就好像依然保留着门卫的岗位,但门卫已不再对进入数据库的数据进行核查.
比如说你已经在年龄上限制了必须[>20 and <60]而现在你必须插入一个年龄60的职工.则你可以先使约束无效,然后再插入数据,最后把约束生效.
      alter table 表名 
      check|nocheck constraint all|指定约束名
  例如:alter table work
       nocheck constraint age [使work中已经存在的age约束无效]
  再如:alter table work 
       nocheck constraint all [使work表中所有的约束都无效]

      使用企业管理器进行约束的创建
         A:创建中检查现有数据:指出是否在创建约束时,对现有的数据进行检查.
         B:对insert,update强制约束:如果选择此项则用户创建约束之后,向表插入记录时,约束会检查用户所插入的记录是否符合约束,如果不符合则拒绝用户加入这些数据.
                 
G:使用外键(foreign key)实施完整性
外键约束主要用来维护两个表之间一致性的关系.外键的建立主要是通过将一个表中的主键所在的列包含在另一个表中.这些列就是另外一个表的外键.
  比如:职工表[外键表][职工号,仓库号,工资][以职工号作为主键]
       仓库表[主键表][仓库号,城市,面积][以仓库号作为主键]
则“职工表”中的“仓库号”就是“仓库表”的外键,我们把职工表称为外键表,仓库表称为主键表[切记].要创建外键必须满足:“主键表”与“外键表”的列名和类型长度,都必须相同.

在创建表格时,创建外键
    格式:create table 外键表名
         (列名 类型
          ......
          constraint 外键名 foreign key(指定列名) references 主键表名(建立外键的字段))
    例如:假设已经存在“仓库表”主键表,现在要创建一个外键表“职工表”并设置外键
          create table 职工
          (职工号 varchar(10),
           仓库号 varchar(10), 
           工资 int,
           constraint 职工号 primary key(职工号),
           constraint 仓库号 foreign key(仓库号) references 仓库(仓库号)) 
    想一想为什么要加上: constraint 职工号 primary key (职工号):这一代码.省略行不行？

  作业:创建一个职工销售表与商品表,要求如下:
         职工销售表(职工号,商品编号,数据)
         商品表(商品编号,商品名,单价,库存量)
         现在用transact--SQL语句来设置外键,该怎么样设置.

  检验foreign key约束
     外键设置之后,能够对维护表有什么作用呢？下面详细分析
     1:对外键表的限制
       当一行新的数据被加入到表格中,或者对表格中已经存在的“外键”的数据进行修改时,新的数据或者为NULL或者必须存于主键表中.即不能插入或修改“主键”表中没有的信息.比如:在职工表与仓库表中,职工表为外键表而仓库表为主键表.这样当我们在职工表中的仓库号列中插入数据时,该仓库号必须存在于仓库表中.
     2:对主键表的限制
       当主键所在表的数据被“外键表”所引用时,用户将无法对主键里的数据进行修改或删除.
       比如:如果外键表职工表中引用了主键表仓库表中“WH3“这个仓库号,则不能在主键表仓库表里来修改或删除WH3这个字段值
     3:可以强制当主键所在表的数据被“外键”所使用时,用户对主键的数据进行修改或删除时使“外键”表的数据一起发生变化
       可以在创建外键时加上:
        A:on delete cascade|no action
          说明:cascade:表示在删除与外键相应主键所在的行时,级联删除外键所在行的数据, 
               no action:表示不做任何操作,是默认值,即允许删除
        B:on update cascade|no action 
          表示在对主键表进行更新时是否级联删除外键所在行.
        例如:constraint 仓库号 foreign key(仓库号) references 仓库(仓库号) on update cascade
     
    往已经存在的表中添加外键
当将外键约束添加到另一个已经存在数据的列上时,在默认情况下,sql server将会自动检查表中已经存在的数据,以确保所有的数据都与主键保持一致,或者为null,但是也可以根据需要设置sql server不对现有的数据进行外键约束检查.
    格式:alter table 表名 with check|nocheck 
         add constraint 外键名 foreign key(指定列名) references 主键表名(创建外键的列名) 
         [on delete cascade|no action]
         [on update cascade|no action]
      如在职工表中添加一个外键,仓库号
        alter table 职工 with check 
        add constraint 仓库号 foreign key(仓库号) references 仓库(仓库号)
        on delete cascade
        on update cascade

    修改或删除外键
修改外键,必须把一个键删除之后再重新建一个外键.删除外键的格式为:alter table 表名 drop 外键名
8:使用规则 
9:使用默认值
10:使用identity标识列

H:使用规则(rule)
规则是一组使用transact--SQL书写的条件语句,它可和列捆绑在一起,当向绑定有规则的数据列上插入或修改值时,规则会检测修改数据的完整性.

创建规则
   格式:create rule 规则名 as 规则描述
        规则描述:规则描述的条件表达式中可以包含数学运算符,关系运算符,以及诸如:in,like,between等关键字
   比如:create rule w 
        as @k>=20 and @k<=50 [创建一个规则让k的取值在20----50之间]
   说明:1:规则描述用来定义规则的确切含义,规则中不能引用表中的数据列以及别的数据库对象.
        2:规则应该包括一个局部变量,而且必须只能够包含一个变量必须以@开头.
   再如:create rule sex                    create rule te 
        as @sex in(/'男/',/'女/')              as @te like /'027-%/'

通过在企业管理器来创建规则

规则(rule)与约束(check)的区别
1:CHECK约束比规则更简明,一个列只能应用一个规则,但是却可以应用多个CHECK约束.
     举例:先后在一个表中的一个列中绑定两个规则,看一下是否能在一个列中绑定多个规则.
          先后在一个表中的一个列中创建多个约束,看一下一个列是否可以有多个约束.
2:CHECK约束作为CREATE TABLE语句的一部分进行指定,而规则必须单独的创建,然后绑定到列上.

使用规则:
   A:要使规则生效,必须将它与数据库的某个对象绑定,否则规则毫无用处.
   B:数据库中的对象只有用户自定义数据类型和表中的列可以.其它任何对象都不能邦定.
   C:如果对一个表中某个列绑定了规则,则该列必须遵守规则的定义,如果把一个用户自定义类型绑定了规则,则所有引用该类型的列将都受规则约束.

     1:使用企业管理器让规则与用户自定义数据类型和表中的某个列绑定
     2:使用sql语句[sp_bindrule 规则名,/'表名.列名/']
       比如:sp_bindrule sex ,/'work.性别/' [让work的性别列与规则sex绑定]
       说明:1:可以直接使用sp_bindrule将某个规则应用到一个列上,如果该列原先绑定了一个规则,则新的规则会覆盖原来的规则       
              [证明在一个列上只能绑定一个规则]
            2:规则不应用于已经存在的该列中的数据,只有当用户修改列数据时规则才会生效
            3:一个规则可以绑定一个表的多列或者绑定到多个表的列.
            4:如果该列已经使用了check约束,则所有的约束条件都会被应用.
            5:规则不能绑定到text,image,datatime等类型数据
   
解除绑定
当我们不需要对一个列应用绑定时,可以为该列解除绑定,注意并不是删除规则.
  格式:sp_unbindrule 表名.列名|用户自定义类型名
值得注意的是解除绑定时不需要指明规则名.

通过企业管理器
1:查看相关性,来查看该规则被那些表中的哪些列绑定了.
2:属性

删除规则:
说明:要删除某个规则,必须确保该规则不与任何列绑定否则拒绝删除.
格式:drop rule 规则名

通过企业管理器来删除规则

I:使用默认值(default)
  当用户不输入值时,可以给某个列指定一个默认值.默认值的性质与规则很相似,也需要绑定.(与创建表时的默认值比较一下)
创建默认值:1:企业管理器
          2:sql语句:create default 列名 as 数据类型常量
比如:create default sex as /'男/'

绑定默认值:
1:企业管理器
2:sql语句:sp_bindefault 列名,/'表名.列名/'|用户自定义数据类型
   比如:sp_bindefault sex /'work.性别/'
   与规则一样:1:一个默认值可以绑定一个表的多列或者绑定到多个表的列.
             2:可以直接使用sp_bindefault将某个默认值应用到一个列上,如果该列原先绑定了一个默认值,则新的默认值会覆盖原来的默认值[证明在一个列上只能创建一个默认值]
             3:如果默认值与规则发生冲突,在输入时默认值为失效.  
   比如:work.age的[规则要求>30]而[默认值要求30]试验一下:
     4:默认值必须与绑定的列是相同的字段类型,否则插入时会出错
       比如:默认值为/'a/'而把它绑定到一个整型类型的列上
     5:如果把一个用户自定义类型绑定了默认值,则所有引用该类型的列将都有默认值.
  
解除默认值的绑定:sp_unbindefault 表名.列名|用户自定义数据类型
  
删除默认值:drop default 列名
           说明:与规则一样,当要删除的默认值与某个列绑定时,则不能被删除.

J:使用identity[自动编号]
使用identity特性来定义一个列,标识identity属性的列,不必在插入新行时为其赋值,因为sql server会自动为设置为indentity的列设置一个唯一的行序号

创建identity[一般在创建表时指定]
  例如:create table work
       (职工编号 int identity,
        姓名 char(10),
        ....)
  说明:1:在每个表里最多只能有一个identity列,而且该列不允许为空.
       2:只有数据类型含有(int)的列可以被设置成为identity列.
       3:默认情况下,identity的开始值为1,以1增量值,当然也可以设置开始值和递增值.
       4:设置开始值和增量值的方式是在括号里面写入开始值和增量值.
         例如:create table test
              (列名 int identity(3,2))  [开始值为3,递增值为2]
       5:当表中含有identity的列时,可以用关键字identitycol来引用该列
         例如:select identitycol from work
       6:在一般情况下,不允许人为向identity列插入值