posted by I유령I
2010. 3. 5. 03:30
■ 제약조건 테스트
[oracle@ghost]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 5 02:12:40 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> conn hr/hr
Connected.
SQL> create table testing(no number(5) check (no in (10, 20, 30))
2 deferrable initially deferred);
Table created.
#commit 시 제약조건 검사
SQL> alter session set constraint = deferred;
Session altered.
SQL> insert into testing values(100);
1 row created.
SQL> insert into testing values(20);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HR.SYS_C005393) violated
SQL> select * from testing;
no rows selected
#DML 작업시 제약조건 즉시 검사
SQL> alter session set constraint = immediate;
Session altered.
SQL> insert into testing values(100);
insert into testing values(100)
*
ERROR at line 1:
ORA-02290: check constraint (HR.SYS_C005393) violated
SQL> insert into testing values(10);
1 row created.
SQL> commit;
Commit complete.
SQL>
■ 제약조건 활성화 / 비활성화 테스트
SQL> desc user_constraints;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER NOT NULL 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> col search_condition format a50;
SQL> select constraint_name, search_condition from user_constraints
2 where table_name = 'TESTING';
CONSTRAINT_NAME SEARCH_CONDITION
------------------------------ --------------------------------------------------
SYS_C005393 no in (10, 20, 30)
#제약조건 비활성화
SQL> alter table testing modify constraint SYS_C005393 disable;
Table altered.
#제약조건 no in (10, 20, 30)에 만족하지 않지만 비활성화 상태이기 때문에 DML이 가능하다.
SQL> insert into testing values(50);
1 row created.
SQL> insert into testing values(30);
1 row created.
SQL> commit;
Commit complete.
#제약조건 활성화
SQL> alter table testing modify constraint SYS_C005393 enable novalidate;
Table altered.
#제약조건 no in (10, 20, 30)이 활성화 상태이기 때문에 DML 작업시 에러가 발생한다.
SQL> insert into testing values(50);
insert into testing values(50)
*
ERROR at line 1:
ORA-02290: check constraint (HR.SYS_C005393) violated
SQL> insert into testing values(20);
1 row created.
SQL> commit;
Commit complete.
SQL>
■ EXCEPTIONS Table 사용법
SQL> @?/rdbms/admin/utlexcpt
Table created.
SQL> desc exceptions;
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
SQL>
'Oracle 10g > 10g - 실습' 카테고리의 다른 글
| Profile(프로파일) 생성과 관리 (0) | 2010.03.05 |
|---|---|
| User(유저) 생성과 관리 (0) | 2010.03.05 |
| Table : Partitioned Table (0) | 2010.03.04 |
| Table : 테이블의 공간 할당과 해제 (0) | 2010.03.04 |
| Table : Row Migration과 Chaining (0) | 2010.03.04 |