Oracle Database ：玩转Oracle学习笔记之（3）：维护数据的完整性

1、数据完整性：
1)、介绍：数据的完整性用于确保数据库遵从一定的商业和逻辑规则，在Oracle中，数据完整性可以使用约束，触发器，应用程序（过程，函数）三种方法来实现，在这三种方法中，因为约束易于维护，并且具有最好的性能，所以作为维护数据库完整性的首选；

2）、约束：
约束用于确保数据库数据满足特定的商业规则。在Oracle中约束包括：not null  ， unique，primary key，foreign key 和check五种；
not null：如果在列上定义了not null时候，那么当插入数据时候，必须为列提供数据；
unique：当定义了唯一约束后，该列的值是不能重复的，但是可以为null；
primary key：用于唯一的表示行的数据，当定义主键约束后，该列不但不能重复而且还不能为null；一张表可以有多个unique约束，但是只能有一个primary key约束；

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

check:用于强制行数据必须满足的条件；

SQL> create table goods(goodsId char(8) primary key ,--primarykey
  2  goodsName varchar2(30) ,
  3  unitPrice number(10,2) check(unitPrice > 0) ,
  4  category varchar2(8),
  5  provider varchar2(30));

Table created.


SQL> create table customer(customerId char(8) primary key,--主键
  2  name varchar2(50) not null, --不为空
  3  address varchar2(50) ,
  4  email varchar2(50) unique,
  5  sex char(4) default '男' check (sex in ('男','女')),
  6  cardId char(18));

Table created.

SQL> create table purchase(customerId char(8) references customer(customerId),
  2  goodsId char(8) references goods(goodsId) ,
  3  nums number(5) check ( nums between 1 and 30));

Table created.

3）、如果在建立表的时候忘记建立必要的约束，则可以在建立好后用alter table命令来为表增加约束；但是要注意的是；增加not null约束时要使用modify命令，而增加其他四种约束直接使用add就可以了；

SQL>  alter table goods modify goodsName not null;

Table altered.

SQL> alter table customer add constraint cardUnique unique(cardId);

Table altered.

SQL> alter table customer add constraint addresscheck check(address in('东城','西城','朝阳','海淀','通州'));

Table altered.

4）、删除约束：

当不再需要某个约束的时候，可以删除；
alter table tableName drop constraint contraintName；
在删除主键约束的时候可能会出现错误，比如：
alter table tableName drop primary key;
这是因为如果两张表存在主从关系，那么在删除主表的主键约束时候，必须带上cascade选项，比如：
alter table tableName drop primary key cascade;
5)、显示约束信息：
1、通过查询数据字典视图user_constraints，可以显示当前用户素uoyoude约束信息；

SQL> desc user_constraints;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)

SQL> select * from user_constraints where table_name='customer';

no rows selected

SQL> select constraint_name , constraint_type , status,validated from user_constraints where table_name='CUSTOMER';

CONSTRAINT_NAME                C STATUS   VALIDATED
------------------------------ - -------- -------------
SYS_C0011055                   C ENABLED  VALIDATED
SYS_C0011056                   C ENABLED  VALIDATED
SYS_C0011057                   P ENABLED  VALIDATED
SYS_C0011058                   U ENABLED  VALIDATED
CARDUNIQUE                     U ENABLED  VALIDATED
ADDRESSCHECK                   C ENABLED  VALIDATED

6 rows selected.

2、显示约束列；通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息；
SQL> select column_name , position from user_cons_columns where constraint_name='约束名';

3、直接用pl/sql developer查看即可；


6）、表级定义和列级定义；

列级定义是在定义列的同时定义约束；
例如：

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

Table created.

表级定义指在定义了所有列后，然后再去定义约束，主力需要注意约束not null只能定义在列级定义上；

例如：

SQL> create table employee2(emp_id number(4) ,
  2  name varchar2(15) ,
  3  dept_id number(2) ,
  4  constraint pk_employee primary key(emp_id),
  5  constraint fk_department foreign key (dept_id) references department4(dept_id));

Table created.

注意表级定义和列级定义在定义外键是后的不同点；

SQL> create table goods(goodsId char(8) primary key ,--primarykey
  2  goodsName varchar2(30) ,
  3  unitPrice number(10,2) check(unitPrice > 0) ,
  4  category varchar2(8),
  5  provider varchar2(30));

Table created.

SQL> create table employee2(emp_id number(4) ,
  2  name varchar2(15) ,
  3  dept_id number(2) ,
  4  constraint pk_employee primary key(emp_id),
  5  constraint fk_department foreign key (dept_id) references department4(dept_id));

Table created.