sql数据库开发-13：约束

约束

 

约束的作用:

     .面临的问题:
       --  某列必须有值而且唯一
       --  某列的取值受到另一列取值的限制

     .数据库提供解决方法

       --限制无效的数据进入到表中
       --数据库层面的 "安检"

 

 

约束的类型:

 primart key              主键约束
 unique key               唯一键约束
 NOT NULL                 非空约束
 references foreing key   外键约束
 check                    检查约束

 

 

定义主键约束 (一)

     .列级约束

     create table parent(
        c1 number(2) constraint parent_c1_pk primary key,
        c2 number  
     );

 

定义主键约束 (二)

 
     .表级约束
 
     create table parent(
        c1 number(2),
        c2 number,
        constraint parent_c1_pk primary key(c1));
      );

 

定义主键约束 (三)

    . 给存在的表增加约束，语法形式跟表级约束一样。

    alter table parent add constraint parent_c1_pk primary key(c1);

 

项目案例:

    主键约束的应用场景和实现

 

 

 

主键约束解决的问题:

   . 不允许表中有null记录
 
   . 不允许表中有重复记录

 

非空约束 （NOT NULL）

   . 不允许将该列的值置为空
   . 只有列级约束的形式

 

      ..... colname datatype not null;

 

 

定义唯一键约束 (一)

    .列级约束

 
    create table test(
    c1 number(3) constraint test_c1_pk parmary key,
    c2 number(2) constraint test_c2_uk unique,
    c3 number(4) constraint test_c3_uk unique
    
    );

 

 

定义唯一键约束(二)

   . 表级约束

   create table test(
   c1 number(3) constraint test_c1_pk primary key,
   c2 number(2),
   c3 number(4),
     constraint test_c2_c3_uk unique(c2,c3)

   );

 

项目案例：

   唯一键约束的应用场景和实现

 

唯一性约束：

     .唯一键的列值不允许重复

     .唯一键上任意一列的取值允许为空

 

 

 

表中记录的数量关系

     一对多关系
         -- 一个客户可以申请多个业务
         -- 一个业务必须属于一个客户
   
      
     多对多关系

         -- 一个客户可以申请多种资费
         -- 一种资费可以被多个客户申请

     一对一关系

         -- 中国目前的婚姻法规定一夫一妻制

 

 

 

一对多关系的实现

     Service表中的id列 （业务账号id） 和account_id列(账务账号id) 表述业务和客户的关系，其中account_id 列的值可以重复，表示一个客户可以申请多个业务。

     Account_id列的值不能任意填，必须是一个有效客户的账务账号id 。

     一对多关系由两张表实现。
 
     为了保证一对多关系，数据库层面的实现通过主外键。

     为了保证一对多关系，应用系统层面也可通过代码实现。

 

多对多的实现：

    客户和资费是多对多关系，客户和资费的关系是通过服务实现的。

    Service表中有account_id列 (账务账号id) 和 cost_id 列(资费id),其中account_id和cost_id 都定义成外键，两列的值都可以重复,表达了多对多。

    多对多关系由三张表实现。

 

一对一关系

      .把夫和妻的信息定义成一张表。
    
      .把夫和妻的信息各定义成一张表，每张表的id列定义成pk列,其中一张表的pk列同时定义成fk列，实现了对应关系。

 

 

合表问题:

    一对多关系为什么不定义成一张表？

         -- 违反第三范式
         -- 第三范式：每个非主属性不能依赖于另一个非主属性

 

    多对多为什么不定义成一张表 ？

         -- 违反第二范式
         -- 第二范式: 每个非主属性必须完全依赖于主属性

 

 

定义外键约束 （一）

 
   .  列级约束

    create table child(
      c1 number(3) constraint child_c1_pk primary key,
      c2 number(2) constraint child_c2_fk references parent(1)

    );

 

定义外键约束 (二)

   .  表级约束

    create table child(
      c1 number(3) constraint child_c1_pk primart key,
      c2 number(2),
      constraint child_c2_fk foreign key(c2) references parent(c1)

    );

 

定义外键约束 （三）

   .表级约束

     create table child1(
       c1 number(3) constraint child1_c1_pk primary key,
 
       c2 number(2) constraint child1_c2_fk references parent(1)
             on delete cascade);

 

 

定义外键约束 (四)

 
    .表级约束

    
      create table child2(
         c1 number(3) constraint child2_c1_pk primary key,

         c2 number(2) constraint child2_c2_pk references parent(1)
            on delete set null
       );

 

 

项目案例

     外键约束的应用场景和实现

    

外键约束

     .保证一对多关系

     .通过外键(FK)可以与同一张表的主键(pk)或唯一键(UK)建立引用关系，也可以与不同表的主键(PK) 或唯一键 (UK) 建立引用关系。

     .外键的取值必须匹配父表中已有的值或空值。

 

 

外键约束 (FK) 关键字

  . foreing key
      --   用表级约束定义外键时使用该关键字

  .  refereneces
      --   表示引用父表中的某列

  .  on delete cascade
 
      --  级联删除，删除父表的记录前，先删除子表里的相关记录

  .  on delete set null

      --  删除父表的记录前，先将子表中外键列的相关值置空

 

 

 

UPDATE 语句: 语法

    用update 语句更新表中已经存在的记录，即修改记录的某列的值

   update tabname set colname = value [,colname1= value] [where condition];

 

DELETE语句 :语法

   用DELETE语句删除已存在的记录

       DELETE [FROM] tabname [WHERE condition];

 

E-R图

   . Entity (实体)
       --属性
       --强制属性
       --主属性

   .  Relationsip (关系)

       -- 一对多
       -- 多对多
       -- 一对一
     
   .   E-R图能设计出符合范式要求的表

 

 

 

定义检查约束 (一)

   .   列级约束

   create table test(
    c1 number(3) constraint test_c1_pk primary key,
    c2 number(3) constraint test_c2_ck check(c2 > 100)

    );

 

定义检查约束 (二)

   create table test(
      c1 number(3) constraint test_c1_pk primary key,
      c2 number(2),
      c3 number(2),
       constraint test_c2_ck check((c2+c3)>100)
   );

 

 

检查约束：

    .定义条件表达式，每个列值必须满足该条件

    .以下表达式不允许

       -- 伪劣: currval、nextval、level、rownum
       -- 函数: sysdate、uid、usr、userenv

       -- 引用其它记录的其他值

 

 

约束总结：

     .定义约束时用户给它命名，若用户未命名，系统自动命名形式为SYS_Cn格式

     . 何时创建约束

        -- create table 时定义约束
        -- 表已经存在，用alter table 追加约束

     . 定义约束有表级和列级两种形式