#무결성 제약과 트랜잭션

(on) IMMEDIATE 제약 (기본값) - SQL 문을 처리한 후에 무결성 제약조건을 검사함

Table created.

SQL> insert into t values ( 1 );

1 row created.

SQL> insert into t values ( 2 );

1 row created.

SQL> commit;

Commit complete.

SQL> update t set x = x + 1;

2 rows updated.

|
h3. (on) DEFERRABLE 제약과 캐스케이드 수정
||Demo#2||
|{code:sql|borderStyle=solid}
-- 아래 데모는 좋은 예는 아님 (기본키 변경)
SQL> create table parent
  2  ( pk int primary key )
  3  /

Table created.

SQL> create table child
  2  ( fk constraint child_fk_parent
  3       references parent(pk)
  4       deferrable
  5       initially immediate
  6  )
  7  /

Table created.

SQL> insert into parent values (1);

1 row created.

SQL> insert into child values (1);

1 row created.

SQL> update parent set pk = 2;
update parent set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.CHILD_FK_PARENT) violated - child record
found


SQL> set constraint child_fk_parent deferred;

Constraint set.

SQL> update parent set pk = 2;

1 row updated.

SQL> set constraint child_fk_parent immediate;
set constraint child_fk_parent immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CHILD_FK_PARENT) violated - parent key
not found


SQL> update child set fk = 2;

1 row updated.

SQL> set constraint child_fk_parent immediate;

Constraint set.

SQL> commit;

Commit complete.

-- 제약조건에 미리 deferrable 옵션이 지정돼 있어야 하며, 그렇지 않을 경우 제약조건을 재생성 해야 함
-- deferrable initially immediate 옵션은 꼭 필요할 때만 써야 함 (이 옵션에 의해 데이터의 물리적 구조가 미묘하게 달라짐)

|

Table created.

SQL> select uniqueness from user_indexes where table_name = 'T1';

UNIQUENES

SQL> create table t2 ( pk int primary key deferrable initially immediate);

Table created.

SQL> select uniqueness from user_indexes where table_name = 'T2';

UNIQUENES

|
||Demo#4 (미묘한 예2 : NOT NULL 제약조건)||
|{code:sql|borderStyle=solid}
SQL> create table t
  2  ( x int constraint x_not_null not null deferrable,
  3    y int constraint y_not_null not null,
  4    z varchar2(30)
  5  );

Table created.

SQL> insert into t (x, y, z)
  2  select rownum, rownum, rpad( 'x', 30, 'x' )
  3    from all_users;

142 rows created.

SQL> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

SQL> create index t_idx on t (y);

Index created.

SQL> set autot trace;
SQL> select count(*) from t;


Execution Plan
----------------------------------------------------------

-------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)|
-------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     1   (0)|
|   1 |  SORT AGGREGATE  |       |     1 |            |
|   2 |   INDEX FULL SCAN| T_IDX |   142 |     1   (0)|
-------------------------------------------------------

SQL> drop index t_idx;

Index dropped.

SQL> create index t_idx on t (x);

Index created.

SQL> select count(*) from t;


Execution Plan
----------------------------------------------------------

--------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3   (0)|
|   1 |  SORT AGGREGATE    |      |     1 |            |
|   2 |   TABLE ACCESS FULL| T    |   142 |     3   (0)|
--------------------------------------------------------

SQL> alter table t drop constraint x_not_null;

Table altered.

SQL> alter table t modify x constraint x_not_null not null;

Table altered.

SQL> select count(*) from t;


Execution Plan
----------------------------------------------------------

-------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)|
-------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     1   (0)|
|   1 |  SORT AGGREGATE  |       |     1 |            |
|   2 |   INDEX FULL SCAN| T_IDX |   142 |     1   (0)|
-------------------------------------------------------

|