oracle中的约束

oracle中的约束

1.维护数据的完整性

   数据的完整性用于确保数据库数据遵从一定的商业或逻辑规则。

   oracle中，数据完整性可以使用三种方法来实现：
   约束、触发器和应用程序（过程、函数）
   约束易于维护，并且具有最好的性能，成为了维护数据完整性的首选。

2.约束包括五种：not null、unique、primary key、foreign key和check。

   not null（非空）
     当列上定义了not null，插入数据时，必须为列提供数据。

   unique（唯一）
     当定义唯一后，列的值不能重复，但是可以为null。

   primary key（主键）
     用于唯一标识表形的数据，当定义主键约束后，该列不能重复而且不能为null；
     说明：一张表最多只能有一个主键（可以有联合主键），但可以有多个unqiue约束。

   foreign key（外键）
     用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束，
     或是unique约束。当定义外键约束后，要求外键列数据必须在主表的外键列存在或为null；

   check
     用于强制数据必须满足的条件，如果不满足就会提示出错。

   例：现在有一个商店数据库，记录客户及购物情况，由以下三张表组成：

     商品goods（商品号goodsId，商品名goodsName，单价unitprice，
                 商品类型category，供应商provider）；
     客户customer（客户号customerId，姓名name，住址address，电子邮件email，
                    性别sex，身份证cardId）；
     购买purchase（客户号customerId，商品号goodsId，购买数量nums）；

     用sql语言完成以下功能：
     建表，在定义中要求说明：
      ①每个表的主外键
      ②客户的姓名不能为空值
      ③单价必须大于0，购买数量必须在10到30之间
      ④电子邮件不能重复
      ⑤客户的性别必须为男或者女，默认是男
     
      create table goods(goodsId char(8) primary key,goodsName varchar2(30),
                          unitprice number(10,2) check (unitprice > 0),
                          category varchar2(30),provider varchar2(30));

      create table customer(customerId char(8) primary key,name varchar2(30) not null,
                            address varachar2(50),email varchar2(30) unique,
                            sex char(2) default '男' check(sex in ('男','女'))，
                            cardId char(18) );

      create table purchase(customerId char(8) references customer(customerId)
                             goodsId char(8) references goods(goodsId),
                            nums number(10) check (nums between 10 and 30));

     如果建表时忘记建立约束，可以在建表后使用alter table命令为表增加约束。
     注意：增加not null约束时需使用modify选项，而增加其他四种约束使用add选项。

      ①每个表的主外键
      ②客户的姓名不能为空值    --增加商品名不能为空
      ③单价必须大于0，购买数量必须在10到30之间
      ④电子邮件不能重复   --增加身份证也不能重复
      ⑤客户的性别必须为男或者女，默认是男
      ⑥增加客户的住址只能是‘海淀’、‘朝阳’、‘东城’、
        ‘西城’、‘通州’和 ‘崇文’

      对②的修改：
       alter table good modify goodsName not null;
      对④的修改：
       alter table customer add constraint cardunique unigue(cardId);
       对⑥的修改
       alter table customer add constraint  addresscheck 
         check(address in ('海淀','朝阳','东城','西城','通州','崇文'));

3.删除约束

        alter table 表名 drop constraint 约束名称；

        说明：删除主键时，可能会有错误，比如：
           alter table 表名 drop primary key;
           这是因为如果在两张表存在总从关系，那么在删除主从的主键约束时，
           必须带上cascade选项，如：
           alter table 表名 drop primary key cascade;

4.显示约束信息
         
        通过查询数字视图user_constraints,可以显示当前用户所有的约束信息。

        select constraint_name,constraint_type,status,validated from
         user_constriants where table_name = '表名';

     
5.显示约束列
       
        通过查询数据字典视图user_cons_colums，可以显示约束所对应的表列信息。
        select column_name,position from user_cons_colums 
        where constraint_name = '约束名';

        更容易的方法是直接用pl/sql developer查看。

6.列级定义
        
        列级定义是在定义列的同时定义约束
        如在department表定义主键约束

        create table department4 (dept_id number(2) constraint pk_department primary key,
                                 name varchar2(12),loc varchar2(12) );

7.表级定义

        表级定义是指在定义了所有列后，在定义约束
        注意：not null约束只能在列级上定义
        如在建立employee2表时定义主键约束和外键约束为例：
        create table employee2 (emp_id number(4),name varchar2(15),
                                constraint pk_employee primary key (emp_id),
                                contraint fk_department foreign key (dept_id),
                                references department4(dept_id));