oracle学习日志04

■ 数据完整性

数据完整性(Data Integrity)是指数据的精确性(Accuracy)和可靠性(Reliability)，它是防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。例如：

数据类型

年龄使用什么类型？

格式是否正确

身份证号，可以是15位也可以是18位

范围

性别可选择“男”、“女”、“未知”

是否允许重复

银行卡号不允许重复

特定业务要求

■ 表约束

表约束的目的：确保表中数据的完整性

常用的约束类型：

主键约束(PRIMARY KEY)

唯一键约束(UNIQUE)

非空约束(NOT NULL)

默认约束(DEFAULT)

检查约束(CHECK)

外键约束(FOREIGNKEY)

■ 主键约束

要求主键列数据唯一，并且不允许为空

语法：

create  table test1(

     id int primary key, --这儿的primary key指定了id为主键

     name varchar(30)

   );

选择主键的原则

最少性

尽量选择单个键作为主键

必须保证唯一

稳定性

尽量选择数值更新少的列作为主键

一般选择没有意义，且用户不关心，但程序员关心的列做为表的主键

可以使用系统的sys_guid作主键的值

可以使用自动增长的integer列做为主键的值(序列)

特点：1.唯一、2.非空

■ 唯一约束

Unique是唯一约束，也可称为候选键

要求该列唯一，允许为空，但可以保存两个null

Unique创建的约束列上，不可以存在相同的值。

语法：

create  table test1(

     id int unique, --这儿的unique指定了id为唯一

     name varchar(30)

   );

■ 非空约束

当数据表中的某个字段上的内容不希望设置为null的时候，则可以使用NOT NULL进行指定

语法：

create  table test1(

     id int not null, --这儿的not null指定了id为非空

     name varchar(30)

   );

■ 默认值约束

是指用户没有给定此列的值时，则使用默认值

语法：

createtable test1(

   id int,

   age int default 0

);

■ CHECK约束

指定表中一列或多列可以接受的数据值格式

限制sex的取值

createtable t4(

 id int,

 sex char(1) constraint sex_chk check(sex in('1','0'))

);

通过check约区间

createtable t5(

  id int,

  age int

);

限制年龄在0~100

alter table t5  add constraint t5_chk check (age>=0 andage<=100);

添加一个是检查，只可以写入以M开始的数据

alter table person add constraint p_ck check(name like 'M%');

注意：constraint

非空和默认约束不适合

■ 主外键约束与范式

范式：

第一范式：对于表中的每一行，必须且仅仅有唯一的行值.在一行中的每一列仅有唯一的值并且具有原子性.

第二范式要求非主键列是主键的子集，非主键列活动必须完全依赖整个主键。主键必须有唯一性的元素,一个主键可以由一个或更多的组成唯一值的列组成。一旦创建，主键无法改变，外键关联一个表的主键。主外键关联意味着一对多的关系.

第三范式要求非主键列互不依赖.

第四范式禁止主键列和非主键列一对多关系不受约束

第五范式将表分割成尽可能小的块，为了排除在表中所有的冗余.

■ 主外键约束与范式

主键与外键

主键与外键是维护多表关系的有效手段。

注意事项

当主表中没有对应的记录时，不能将记录添加到子表

不能更改主表中的值而导致子表中的记录孤立

子表存在与主表对应的记录，不能从主表中删除该记录

删除主表前，先删子表

■ E-R模型

E-R模型的特点

Entities：实体

Attributes：属性

Relationships：关系

通常有许多表

通常是满足3NF的

主键/ 外键

1对多映射

建立E-R模型是数据库概念设计的重要内容，而概念设计是设计阶段的组成部分。同时建立E-R模型的工作，属于软件生命周期的设计阶段。

■ 外键约束

用于建立和加强两个表数据之间连接的一列或多列。通过将表中的主键列添加到另一个表中。可以创建两个表之间的连接。这个主键的列就称为第二个表的外键。外键约束就可以确保添加到外键表中的任何行都在主表中都存在相应的行

添加外键约束

创建主外键关系

  createtable person(

    id varchar（32),

    name varchar(30),

    constraint p_pk primary key(id)  );

创建外表

  createtable car(

    id varchar(32),

    name varchar(30),

    pid varchar(32),

    constraint c_pk primary key(id),

在创建外键约束时可以添加ON DELETE CASCADE选项，那么当主表的数据被删除时，子表对应的行同样也自动被删除。例如

创建主外键关系且设置级联删除

  createtable p2(

    id int,

    nm varchar2(30),

    constraint p2_pk primary key(id）

  );

下面的on delete cascade就是删除时级联

  createtable c2(

   id int,

   nm varchar2(30),

   pidint,

   constraint c2_pk primary key(id),

   constraint c2_fk foreign key(pid)references p2(id)on delete cascade);

在创建外键约束时可以添加ON DELETE SET NULL选项，那么当主表的数据被删除时，子表匹配的相关行的列会被设置为NULL值，而不是被删除

例如

设置外键

ALTERTABLE order_status2

ADDCONSTRAINT order_status2_modified_by_fk

modified_byREFERENCES employees(employee_id) ONDELETE SET NULL;

■ 约束修改

删除约束

通过ALTER TABLE的DROP CONSTRAINT选项删除约束

ALTERTABLE 表名

DROPCONSTRAINT 约束名;

停用约束

约束在创建完之后就生效了，也可以在创建时在CONSTRAINT关键字末尾加DISABLE关键字，默认停用它。

ALTERTABLE 表名

ADDCONSTRAINT 约束名 UNIQUE(status) DISABLE;

也可以停用已经启用的约束

ALTERTABLE 表名

DISABLECONSTRAINT约束名;

启用约束

可以使用ALTER TABLE的ENABLE CONSTRAINT选项将已存在的约束失效启用。例如

ALTERTABLE 表名

ENABLECONSTRAINT  约束名;

说明

在启用约束时，表中所有的数据行的数据必须要满足这个约束。

也可以通过设置ENABLE NOVALIDATE，对新的数据行增加约束

ALTERTABLE   表名

ENABLENOVALIDATE CONSTRAINT  约束名;

说明

默认是ENABLE VALIDATE，对存在的数据行也要求符合约束

延迟约束(Deferred Constraint)就是在事务提交的时候才生效

可以在约束中加DEFERRABLE选项

约束一旦创建之后就不能将其改为DEFERRABLE，必须删除重新创建

创建DEFERRABLE约束时，可以使用INITIALLY IMMEDIATE或INITIALLY DEFERRED

INITIALLY IMMEDIATE：表示添加、更新或删除时都进行数据的合法性验证(默认情况)

INITIALLY DEFERRED：表示只有事务提交时，才进行数据的合法性验证

■ 查看约束信息

可以通过user_constraints视图查看约束信息

查看T4表的约束信息

SELECTconstraint_name, constraint_type,status, deferrable, deferred  FROM user_constraints

WHEREtable_name ='student';

通过查看user_cons_columns视图，了解列的约束信息

视图相关列的说明

owner：约束的拥有者

constraint_name：约束名

table_name：约束所在的表

column_name：约束定义所在的列

SELECTowner,constraint_name,table_name,column_name

FROMuser_cons_columns

WHEREtable_name=‘student';