数据完整性

1.设置外键

  alter table emp add constraint fk_deptno

   foreign key (deptno)

  references dept(deptno)

on delete cascade(on delete set null)

 

2.

 

 SQL> create table t(id int,name varchar2(10));
 Table created

 

SQL> alter table t add primary key(id) disable;
 
Table altered

 

SQL> insert into t values(0,'aaa');
 
1 row inserted
 
SQL> insert into t values(0,'aaa');
 
1 row inserted
 
SQL> commit;
 
Commit complete

 

SQL> alter table t enable novalidate primary key;
 
alter table t enable novalidate primary key
 
ORA-02437: 无法验证 (SCOTT.SYS_C005749) - 违反主键

 

SQL> select constraint_name,status,validated from user_constraints;
 
CONSTRAINT_NAME                STATUS   VALIDATED
------------------------------ -------- -------------
SYS_C005570                    ENABLED  VALIDATED
BIN$4CKUMzBwRX+B953AZVY4Cg==$0 ENABLED  VALIDATED
BIN$qE45XNIKSfqVGjL2aKMfHg==$0 ENABLED  VALIDATED
FK_DEPTNO                      ENABLED  VALIDATED
PK_DEPT                        ENABLED  VALIDATED
PK_EMP                         ENABLED  VALIDATED
BIN$TPL0tu4yR8yHZ8DCl8NC/g==$0 ENABLED  VALIDATED
PK_NN                          ENABLED  VALIDATED
BIN$ao1FsgCmRFqx3eIYxceqQA==$0 ENABLED  VALIDATED
BIN$agHX/iByTQaxz+QMkNz91g==$0 ENABLED  VALIDATED
SALARY_DETAILS_EMPCODE_PK      ENABLED  VALIDATED
SYS_C005749                    DISABLED NOT VALIDATED
 

12 rows selected

 

3.设定延迟约束

 SQL> select index_name,index_type,table_name from user_indexes where table_name
= 'T';

未选定行

 

alter table t add primary key (id) deferrable;

 

select index_name,uniqueness from user_indexes;

 

alter table t disable primary key;

 

insert into t values('0','kkk');

insert into t values('0','kkk');

commit;

 

alter table t disable validate  primary key;

报错

 alter table enable novalidate primary key;

 

select constraint_name,status,validated from user_constraints;

insert into t values('0','kkk');--报错

 

set constraints all deferred;

insert into t values('0','kkk'); --成功

commit ---报错

 

 

 4.alter table 表名 enable novalidate constraint 约束名

    alter table 表名 enable validate constraint 约束名

 

5.找出违反约束的数据并存入exceptions表中

  1.运行/rdbms/admin/utlexcept1.sql

 

2.SQL> create table t(id int,name varchar2(20));

表已创建。

SQL> alter table t add primary key(id) disable;

表已更改。

 

3.

SQL> insert into t values(1,'kkk');

已创建 1 行。

SQL> insert into t values(2,'kkk');

已创建 1 行。

SQL> insert into t values(2,'kkk');

已创建 1 行。

 

4.SQL> alter table t primary key enable validate;
alter table t primary key enable validate
              *
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项

SQL> alter table t enable validate primary key;
alter table t enable validate primary key
*
第 1 行出现错误:
ORA-02437: 无法验证 (SCOTT.SYS_C005774) - 违反主键

 

5.SQL> alter table t enable validate primary key exceptions into exceptions;
alter table t enable validate primary key exceptions into exceptions
*
第 1 行出现错误:
ORA-02437: 无法验证 (SCOTT.SYS_C005774) - 违反主键

 

6.SQL> select rowid,id,name from t where rowid in (select row_id from exceptions);

ROWID                      ID NAME
------------------ ---------- --------------------
AAANCpAAEAAAAJtAAB          2 kkk
AAANCpAAEAAAAJtAAC          2 kkk

 

7.update t set id = 3 where rowid = ' AAANCpAAEAAAAJtAAC'

 

8.SQL> alter table t enable validate primary key exceptions into exceptions;

 

6.数据字典

  DBA_CONSTRAINTS

  DBA_CONS_COLUMNS