◆ 데이터 무결성 제약조건이란?
데이터의 정확성과 일관성을 보장하기 위해 테이블 생성시에 각 컬럼에 대해 정의하는 규칙을 의미.
◆ 데이터 무결성 제약조건의 종류
무결성 제약 조건 | 설명 |
NOT NULL | 해당 컬럼값은 널을 포함할 수 없음. |
UNIQUE | 테이블에서 해당 컬럼값은 반드시 유일해야 함. |
PRIMARY KEY | 해당 컬럼값은 반드시 존재해야 함며, 유일해야 함. NOT NULL + UNIQUE 제약조건이 결합된 형태. |
REFERENCE | 해당 컬럼값은 참조되는 테이블의 컬럼 값 중의 하나와 일치하거나 널을 가짐. |
CHECK | 해당 컬럼에 저장 가능한 데이터 값의 범위나 조건 지정. |
◆ 데이터 무결성 제약조건의 특징
- 테이블 생성 시 무결성 제약조건을 정의하여 프로그래밍 과정을 줄여준다.
- 데이터베이스 서버에 의해 무결성 제약조건이 관리되어 데이터 오류 발생 가능성을 줄여준다.
- 테이블에 대해 정의되고 데이터 딕셔너리에 저장된다.
- 일시적으로 활성화 또는 비활성화 할 수 있다.
◆ 기본키 무결성 제약 조건
기본키란 테이블 내에 저장된 행을 유일하게 식별할 수 있는 식별자를 의미.
하나 이상의 컬럼에 대해 테이블의 모든 행을 구별하기 위한 식별자를 정의.(Unique key + Not null 개념)
테이블의 컬럼에 Primary key나 Unique key가 걸리면 해당 컬럼에는 자동적으로 Unique Index가 만들어진다.
이 상태에서 해당 컬럼의 제약조건을 disable시키면 Unique Index가 지워진다.
이후 다시 해당 컬럼의 제약조건을 enable시키면 Unique Index를 다시 재생성하는데 이때 시간이 오래 걸린다.
◆ 참조 무결성 제약조건
한 테이블의 컬럼값이 자식 또는 다른 테이블의 컬럼값 중 하나와 일치시키기 위한 제약조건.
- 자식 테이블(Child Table) : 다른 테이블의 값을 참조하는 테이블
- 부모 테이블(Parent Table) : 다른 테이블에 의해 참조되는 테이블
- 외래키(Foreign Key) : 부모 테이블의 값을 참조하는 자식 테이블의 컬럼
- 참조키(Reference Key) : 자식 테이블에서 참조하는 부모 테이블의 컬럼
사원 테이블에 데이터가 1억건, 부서 테이블에 100만건이 있다고 가정하자.
사원 테이블에 부서번호 5000을 가진 데이터가 들어올 때 부서 테이블에서 부서번호 5000을 가진 데이터를 비교하는데 운이 조하서 첫 줄에 부서번호 5000을 찾았다고 했을 때 과연 거기서 스캔을 멈출까? 결과는 그렇지 않다. 5000번의 부서번호가 또 있는 줄 알고 끝까지 스캔을 한다.
◆ 무결성 제약조건 생성 방법
무결성 제약조건의 생성 방법에는 테이블을 생성할 때 제약조건을 주는 방식이 있고, 테이블을 생성 후 나중에 추가하는 방법이 있다.
◇ 테이블 생성시 무결성 제약조건 생성
SQL> create table 테이블명
(컬럼명 데이터타입 [default 기본값]
[constraint 제약조건명 제약조건타입]
[, ...]);
(컬럼명 데이터타입 [default 기본값]
[constraint 제약조건명 제약조건타입]
[, ...]);
정식 : no number(5) constraint subject_no_pk primary key
약식 : no number(5) primary key
정식과 약식의 차이점은 제약조건을 일시적으로 활성화 또는 비활성화 할 때 제약조건명이 필요한데 그때는 정식에서 사용한 제약조건명을 사용하면 편하다.약식 : no number(5) primary key
하지만 약식으로 하면 오라클이 자기 마음대로 제약조건명을 정하기 때문에 필요시 조회를 해야 하는 불편함이 있다. 그러나 제약조건을 걸 때는 간편하다.
name varchar2(20)
constraint subject_name_nn not null
constraint subject_name_uk unique,
테이블의 한 컬럼에 여러 개의 제약조건을 줄 때는 콤마 없이 기술해주면 된다.constraint subject_name_nn not null
constraint subject_name_uk unique,
◇ 기존 테이블에 무결성 제약 조건 추가
SQL> alter table 테이블명
add [constraint 제약조건명] 제약조건타입 (제약조건걸어줄컬럼명);
add [constraint 제약조건명] 제약조건타입 (제약조건걸어줄컬럼명);
단, Not Null 제약조건 추가는 Not Null 허용 상태를 Not Null 불가로 변경하는 것이므로 modify 명령문을 사용해야 한다.
SQL> alter table 테이블명
modify (제약조건걸어줄컬럼명 [constraint 제약조건명] NOT NULL);
modify (제약조건걸어줄컬럼명 [constraint 제약조건명] NOT NULL);
◆ 테이블 생성 후 참조 무결성 제약조건 추가
SQL> alter table 테이블명
add constraint 제약조건명 foreign key (제약조건걸어줄컬럼명) references 참조테이블명 (제약조건걸어줄컬럼명);
add constraint 제약조건명 foreign key (제약조건걸어줄컬럼명) references 참조테이블명 (제약조건걸어줄컬럼명);
◆ 무결성 제약조건 삭제
SQL> alter table 테이블명
drop constraint 제약조건명 [cascade];
drop constraint 제약조건명 [cascade];
◆ 무결성 제약조건 비활성화
SQL> alter table 테이블명
disable constraint 제약조건명 [cascade];
disable constraint 제약조건명 [cascade];
◆ 무결성 제약조건 활성화
SQL> alter table 테이블명
enable [novalidate] constraint 제약조건명 [cascade];
novalidate : 기존 데이터는 적용시키지 않고 새로 입력되거나 수정되는 데이터에만 적용.enable [novalidate] constraint 제약조건명 [cascade];
◆ 제약조건 관련 4가지 상태
◇ disable novalidate
- 기본값
- 그 제약조건을 완전히 없는 셈 친다. 없는 것과 동일함.
- 우리가 Primary key나 Unique key를 걸면 자동으로 Unique Index가 만들어지는데 disable해버리면 그 인덱스가 자동으로 지워진다. 후에 다시 enable하게 되면 자동으로 다시 만들게 되서 시간이 오래 걸림.
◇ disable validate
- 데이터를 더이상 변경 안되게 막는 것.
- 국민은행 테이블스페이스에 100개의 테이블이 있는데 그 중 고객 테이블만 읽기전용으로 하고 싶으면 disable validate 하면 된다.
◇ enable novalidate
- 기존에 들어있는 데이터는 검사 안하고, 새로 들어온 데이터부터 검사함.
◇ enable validate
- 기본값
- 기존에 들어있는 데이터를 검사하고, 새로 들어오는 데이터도 검사함.
◆ Exceptions Table
그래서 enable validate를 해준다면, 기존에 들어있던 1억1천건부터 제약조건 검사를 하는데, 중간에 위배값이 있다면 에러가 난다. 그러면 그 위배값을 찾아서 제대로 고쳐주고 다시 enable시켜야 한다.
그러나 사용자가 어떻게 무슨 값이 위배되었는지 그 많은 데이터들 중 찾을 수 있는 방법이 있을까?
그때는 Exceptions table을 만들어놓고, 위배값이 나오면 Exceptions table에 저장되도록 하여 후에 위배값을 수정하기 용이하도록 해주면 된다.
conn / as sysdba;
@?/rdbms/admin/utlexcpt.sql (9i는 utlexcptl.sql)
sysdba권한으로 Exceptions table을 생성시키는 스크립트를 수행한다.
create table scott.test1
(no number(5)
constraint test1_no_ck check (no in (10, 20, 30)));
테스트를 위해 무난히 check제약조건을 건 테스트용 테이블을 하나 생성한다.
insert into scott.test1 values (10);
정상적으로 들어간다.
insert into scott.test1 values (40);
제약조건에 위배되므로 에러를 띄운다.
alter table scott.test1 disable constraint test1_no_ck;
check제약조건을 disable시킨다.
insert into scott.test1 values (40);
제약조건을 disable시켜놨기 때문에 정상적으로 들어간다.
select * from scott.test1;
조회해보면 10, 40이 들어있는 것을 확인가능.
alter table scott.test1
enable validate constraint test1_no_ck
exceptions into sys.exceptions;
제약조건을 enable시키되 위배되는 로우가 있으면 Exceptions table에 집어넣는다.
select rowid, no
from scott.test1
where rowid in (select row_id from exceptions)
for update;
제약조건이 위배된 로우와 로우아이디를 확인함.
update scott.test1
set no=20
where rowid='위에서 구한 ROWID값';
제약조건이 위배된 로우아이디값을 가지고 제대로 된 값으로 수정함.
commit;
truncate table exceptions;
또다른 제약조건 위배가 있는지 확인하기 위해 Exceptions table을 절삭함.
alter table scott.test1
enable validate constraint test1_no_ck
exceptions into sys.exceptions;
다시 enable validate를 해보면 아무런 위배 없이 제약조건을 enable시켰음을 확인할 수 있다.
@?/rdbms/admin/utlexcpt.sql (9i는 utlexcptl.sql)
sysdba권한으로 Exceptions table을 생성시키는 스크립트를 수행한다.
create table scott.test1
(no number(5)
constraint test1_no_ck check (no in (10, 20, 30)));
테스트를 위해 무난히 check제약조건을 건 테스트용 테이블을 하나 생성한다.
insert into scott.test1 values (10);
정상적으로 들어간다.
insert into scott.test1 values (40);
제약조건에 위배되므로 에러를 띄운다.
alter table scott.test1 disable constraint test1_no_ck;
check제약조건을 disable시킨다.
insert into scott.test1 values (40);
제약조건을 disable시켜놨기 때문에 정상적으로 들어간다.
select * from scott.test1;
조회해보면 10, 40이 들어있는 것을 확인가능.
alter table scott.test1
enable validate constraint test1_no_ck
exceptions into sys.exceptions;
제약조건을 enable시키되 위배되는 로우가 있으면 Exceptions table에 집어넣는다.
select rowid, no
from scott.test1
where rowid in (select row_id from exceptions)
for update;
제약조건이 위배된 로우와 로우아이디를 확인함.
update scott.test1
set no=20
where rowid='위에서 구한 ROWID값';
제약조건이 위배된 로우아이디값을 가지고 제대로 된 값으로 수정함.
commit;
truncate table exceptions;
또다른 제약조건 위배가 있는지 확인하기 위해 Exceptions table을 절삭함.
alter table scott.test1
enable validate constraint test1_no_ck
exceptions into sys.exceptions;
다시 enable validate를 해보면 아무런 위배 없이 제약조건을 enable시켰음을 확인할 수 있다.
◆ Deferred와 Immediate
- Deferred : 커밋을 날리는 시점에서 제약조건을 검사함.
- Immediate : DML이 매 실행되는 시점에서 제약조건을 검사함.
create table test1
(no number(5)
constraint test1_no_ck check (no in (10, 20, 30))
deferrable initially deferred);
테이블을 하나 생성한다. 제약조건 테스트를 하기 위해 무난히 check 제약조건을 걸어준다.
alter session set constraint=deferred;
커밋 시점에서 제약조건 검사를 수행하도록 함.
insert into test1 values (40);
분명 잘못된 값인데 에러 안남.
insert into test1 values (10);
commit;
이 시점에서 제약조건 검사를 하기 때문에 커밋 안되고 에러 남. 데이터 안들어갔음.
alter session set constraint=immediate;
DML문장 실행시마다 제약조건 검사를 수행하도록 함.
insert into test1 values (40);
제약조건 위배로 인해 에러남.
insert into test1 values (10);
잘 들어감.
commit;
10데이터는 들어갔음.
(no number(5)
constraint test1_no_ck check (no in (10, 20, 30))
deferrable initially deferred);
테이블을 하나 생성한다. 제약조건 테스트를 하기 위해 무난히 check 제약조건을 걸어준다.
alter session set constraint=deferred;
커밋 시점에서 제약조건 검사를 수행하도록 함.
insert into test1 values (40);
분명 잘못된 값인데 에러 안남.
insert into test1 values (10);
commit;
이 시점에서 제약조건 검사를 하기 때문에 커밋 안되고 에러 남. 데이터 안들어갔음.
alter session set constraint=immediate;
DML문장 실행시마다 제약조건 검사를 수행하도록 함.
insert into test1 values (40);
제약조건 위배로 인해 에러남.
insert into test1 values (10);
잘 들어감.
commit;
10데이터는 들어갔음.
◆ 무결성 제약조건 관련 데이터 딕셔너리
사용자에 의해 정의된 무결성 제약조건을 조회하기 위한 데이터 디셔너리 뷰는 USER_CONSTRAINTS, USER_CONS_COLUMNS가 있다.
USER_CONSTRAINTS
- 제약 조건이 정의된 테이블 이름, 제약조건 이름, 제약조건 종류 및 활성화 상태 정보 저장
USER_CONS_COLUMNS
- 제약조건이 정의된 컬럼과 제약조건 이름 저장
- 제약 조건이 정의된 테이블 이름, 제약조건 이름, 제약조건 종류 및 활성화 상태 정보 저장
USER_CONS_COLUMNS
- 제약조건이 정의된 컬럼과 제약조건 이름 저장
'오라클 > Admin' 카테고리의 다른 글
[Admin] Dedicated Server와 Shared Server (0) | 2010.08.25 |
---|---|
[Admin] Oracle Primary Architecture (0) | 2010.08.25 |
[Admin] 쿼리 수행 절차 (UPDATE문) (0) | 2010.08.25 |
[Admin] 쿼리 수행 절차 (SELECT문) (0) | 2010.08.25 |
[Admin] User Process와 Server Process (0) | 2010.08.24 |