oracle笔记（十八）数据完整性约束

卓亨

数据完整性
   数据库不仅仅是存储数据,它也必须保证所保存的数据的正确性。如果数据不正确或不一致,那么该数据的完整性可能会遭到破坏，从而给数据库本身的可靠性带来意想不到的问题。
为了维护数据库中的数据完成性,在创建表时通常需要指定一些约束。通过表中的字段(列)定义约束，就可以防止非法数据的插入问题.对约束的定义可以再create table语句中进行,也可以在alter table语句中进行。
 
什么是约束呢？
   约束是表级的强制规定.根据约束的作用域,约束又可分为表级约束和列级约束两种。
列级约束是指是字段定义的一部分,只能够应用在一个列上.
表级约束是指独立于列的定义,可以应用于一个表中的多个列上。
在oracle系统中定义约束时,通过constraint关键字为约束命名，如果用户没有为约束指定名称,系统会自动建立默认的名称。
 
总结：
l       作用范围：列级约束只能作用在一个列上，而表约束可以作用在多个列上（当然表约束也可以作用在一个列上）。
l       定义方式：列约束必须跟在列的定义里后面，表约束不与列一起，而是单独定义。
l       非空（not null) 约束只能定义在列上
 
 
备注：在定义约束时，可以通过指定enable或disable关键字将约束的初始状态设置为激活或禁用状态,默认是激活状态。
 
定义约束的语法格式1：
CREATE TABLE [schema.]table
                    (column datatype [DEFAULT expr]
                                [column_constraint],
                                ...
                                [table_constraint][,...]);
列级约束：
  column [CONSTRAINT constraint_name] constraint_type,
表级约束
  column,...
  [CONSTRAINT constraint_name] constraint_type
  (column, ...),
约束的分类为：
·         NOT NULL
·         UNIQUE
·         PRIMARY KEY
·         FOREIGN KEY
·         CHECK
1、            Not null
特点如下：
        为字段定义not null 约束后，该字段不能为null值
        Not Null约束只能在列级定义
        在定义一个表中可以有多个not Null
2、            unique
特点如下：
        如果为字段定义了unique约束,该字段不能够包含重复值
        unique约束既可以在列级定义,也可以在表级定义。
        在oracle系统中被定义的unique约束会自动建立一个唯一的索引。
        同一个字段既可以在其上定义为not null 也可以建立unique约束,(为某个字段可以添加多个约束),注意：如果在一个字段仅定义了unique约束，则该字段可以包含多个null值
Unique主要的目的是：是在保证主键列外,其它列的唯一性。
3、            primary key
特点如下：
        定义为primary key的字段或字段组中不能包含重复值,并且不能为null值.（即满足唯一性,不能为空）
        在一个表中只能定义一个primary key的约束
        Oracle会自动为具有primarykey约束的字段添加一个唯一的索引,以及一个not null约束
4、            foreign key
特点如下：
           被定义了foreign key约束的字段的取值只能为相关表中引用的字段的值或null值。
        foreign key约束既可以在列级定义,也可以在表级定义。
        定义了foreign key约束的外键字段和被引用的主键字段可以在同一张表中,这种情况称为:’自引用’(构成了自关联关系)
        对于同一个字段可以同时定义为foreign key约束和not null约束。
 
注意：外键的类型、尺寸等一定要与主键完全一致。
备注：
   在oracle中级联删除有如下三值：
1、            no action 在删除一方的情况下,对多方不进行任何操作.（不能删,删的情况会出现, 违反完整约束条件的异常信息）
2、            cascade 在删除一方的时,会把多方所有与1方关联的数据删除掉。
3、            set null在删除一方的时,会把多方所有与1方关联的数据的字段值设置为null(注意：该字段必须允许为null 否则的话会出现无法更新***为 NULL)。
5、            check 
check约束是所有约束中最灵活的约束,也是最复杂的约束,check约束检查输入到表中的数据值来维护域的完整性,即检查输入的每一个数据，只有符合条件的数据才允许输入到表中.check约束有如下特点：
          在check约束的表达式中,必须引用表中一个或多个字段,并且表达式的运算结果必须是一个布尔值.
          Check约束即可以在列级定义,也可以在表级定义
          对于同一个字段可以定义多个check约束,而且对于同一个字段可以定义check约束和not null约束。
综合案例：
  学生信息表
      用户名,用户密码,用户电话,用户邮箱,用户地址.
      教师名称,教师性别,教师职务,教师年龄
      ——————————————————————————————
分析：满足1NF范式(属性不可再分)
      满足2NF范式(非主属性必须完全依赖于任一候选键)
     用户名->用户密码,用户电话,用户邮箱,用户地址.
stu（sname,stel,semail,saddress）
     用户名—> 教师名称
     教师名称—>用户名
  stear（sid,tname,sname）
教师名称—>教师性别,教师职务,教师年龄
Teacher（tname,tsex,ttitle,tage）
     满足3NF(非主属性都不传递依赖于任一候选键)
//创建表的语法同时也给表加约束||或者是创建表之后才用alter语句添加约束条件
//第一步切换到管理员权限
SQL> conn system/root;
//第二步创建用户
SQL> create user slj identified by root;
//第三步为用户分配角色权限
SQL> grant connect,resource to slj;//connect 连接 resources 创建表的权限 详细参考第二次oracle课程
//切换到相应的用户
SQL> conn slj/root;
//采用create语句创建表

SQL> create table stu(

  2    sname varchar2(20) primary key,

  3    stel varchar2(11) not null,

  4    semail varchar2(40),

  5    saddress varchar2(50)

  6  );

Table created

SQL> create table teacher(

  2     tname varchar2(20) primary key,

  3     tsex varchar2(2),

  4     ttitle varchar2(40),

  5     tage number(3) check(tage<100 and tage>20)

  6  );

Table created

SQL> create table stear(

  2     sid number(10) primary key,

  3     tname varchar2(20) references teacher(tname),

  4     sname varchar2(20) references stu(sname)

  5  );

Table created
SQL> commit;
//查询约束条件

SQL> select * from user_constraints;

//查询被约束的列

SQL> select * from user_cons_columns;

6、            约束的状态
在oracle系统中，约束具有如下两种状态：
1)       激活状态（enable）在激活状态下对表进行操作时，如果操作与约束规则冲突，则操作被取消
2)       禁用状态（disable）在禁用状态下对表进行操作时，即使操作与约束规则冲突，操作也会得到执行
A.       一般为了保证数据库的完整性，表的约束为激活状态，但是，有些特殊的操作（使用sql * loader从外部数据源向表中导入大量数据时，为了保证操作正常运行，常需要将一些约束改成禁止状态，而在操作后要将约束该成激活状态 ）
B.       在使用create table或alter table语句定义约束时，默认情况下约束状态为激活状态
C.       在使用disable关键字时约束的初始约束为紧张状态。
D.       在alter table语句中使用disable关键字可以将激活状态切换到禁止状态
E.       使用带modify子句的alter table语句可以改变约束状态