제약조건(Constraint)이란 테이블에 올바른 데이터만 입력 받고 잘못된 데이터는 들어오지 못하도록 컬럼마다 정하는 규칙을 의미한다.
데이터의 정확성은 더 높아진다.
1. 제약조건의 종류
조건 이름 의미
NOT NULL NULL 입력불가
UNIQUE 중복 값 입력 불가 (자동 INDEX 생성)
PRIMARY KEY NOT NULL + UNIQUE , 데이터들끼리의 유일성을 보장하는 컬럼에 설정할 수 있으며, 테이블당 1개만 설정 가능
FOREIGN KEY 다른 테이블의 컬럼을 참조해서 무결성 검사 (2개의 테이블에 생성 Reference Key)
CHECK 설정된 값만 입력 허용
2. 각 제약조건의 설정 방법
(1) 테이블 생성시에 동시에 설정하기
CREATE TABLE emp3
(
no NUMBER(4) CONSTRAINT emp3_no_pk PRIMARY KEY,
name VARCHAR2(10) CONSTRAINT emp3_name_nn NOT NULL,
jumin VARCHAR2(13) CONSTRAINT emp3_jumin_nn NOT NULL
CONSTRAINT emp3_jumin_uk UNIQUE,
area NUMBER(1) CONSTRAINT emp3_area_ck CHECK (area < 5),
deptno VARCHAR2(6) CONSTRAINT emp3_deptno_fk REFERENCES dept2(dcode)
) ;
이름을 지정하지 않고 생성할 수도 있다.
CREATE TABLE emp4
(
no NUMBER(4) PRIMARY KEY,
name VARCHAR2(10) NOT NULL,
jumin VARCHAR2(13) NOT NULL UNIQUE,
area NUMBER(1) CHECK (area < 5),
deptno VARCHAR2(6) REFERENCES dept2(dcode)
) ;
제약조건을 비활성화 하거나 활성화 하거나 삭제하는 등의 관리 작업을 하려면 해당 제약 조건의 이름을 알고 있어야 한다.
그래서 제약조건은 이름을 직접 지정하는 것을 권장한다.
(2) 테이블 생성 후 추가하기
CREATE TABLE emp4
(
no NUMBER(4),
name VARCHAR2(10),
jumin VARCHAR2(13),
area NUMBER(1),
deptno VARCHAR2(6)
) ;
emp4 테이블의 name 컬럼에 UNIQUE 제약조건 추가하기
ALTER TABLE emp4
ADD CONSTRAINT emp4_name_uk UNIQUE (name) ;
emp4 테이블의 area 컬럼에 NOT NULL 제약조건 추가하기
ALTER TABLE emp4
ADD CONSTRAINT emp4_area_nn NOT NULL (area) ;
에러가 난다.
ALTER TABLE emp4
MODIFY (area CONSTRAINT emp4_area_nn NOT NULL) ;
Step 1. 연습용 테이블을 생성합니다.
Step 3. on delete cascade 테스트를 수행합니다.
insert into c_test1 values (4,'peach',40);
insert into c_test1 values (4,'peach',40)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CTEST1_DEPTNO_FK) violated - parent key
not found
부모테이블에 no컬럼에 40이 없기 때문에 입력이 되지 않는다.
select * from c_test1;
NO NAME DEPTNO
---------- ------------ ----------
1 apple 10
2 banana 20
3 cherry 30
delete from c_test2 where no=10 ;
1 row deleted.
select * from c_test1;
NO NAME DEPTNO
---------- ------------- ----------
2 banana 20
3 cherry 30
위 결과에서 1 번 항목이 지워진 것이 확인됩니다.
Step 4. on delete set null 테스트를 수행합니다.
alter table c_test1 drop constraint ctest1_deptno_fk;
alter table c_test1
add constraint ctest1_deptno_fk foreign key(deptno)
references c_test2(no)
on delete set null;
select * from c_test1;
NO NAME DEPTNO
---------- ----------- ----------
2 banana 20
3 cherry 30
select * from c_test2;
NO NAME
----------- ----------
20 BBBB
30 CCCC
delete from c_test2 where no=20 ;
1 row deleted.
select * from c_test1;
NO NAME DEPTNO
---------- --------------- ----------
2 banana
3 cherry 30
자식테이블의 deptno 가 null 로 변경되는 것이 확인됩니다.
그런데 만약 자식테이블의 deptno 컬럼에 not null 속성이 설정되어 있는 상태에서
Foreign Key 가 on delete set null 로 생성되면 어떻게 될까? 아래의 테스트로 확인해 보겠다.
Step 1. 자식테이블의 deptno 에 not null 속성을 설정합니다.
alter table c_test1
modify (deptno constraint ctest1_deptno_nn not null);
modify (deptno constraint ctest1_deptno_nn not null)
*
ERROR at line 2:
ORA-02296: cannot enable (SCOTT.CTEST1_DEPTNO_NN) -null values found
위 에러는 기존에 null 값이 있어서 변경할 수 없다는 뜻입니다.
select * from c_test1;
NO NAME DEPTNO
---------- ------------- ----------
2 banana
3 cherry 30
update c_test1 set deptno=30
where no = 2;
commit;
select * from c_test1;
NO NAME DEPTNO
----------- -------------- ----------
2 banana 30
3 cherry 30
alter table c_test1
modify (deptno constraint ctest1_deptno_nn not null);
Table altered.
정상적으로 자식 테이블의 deptno 컬럼에 not null 이 설정되었습니다.
select * from c_test2;
NO NAME
---------- ----------
30 CCCC
delete from c_test2;
delete from c_test2
*
ERROR at line 1:
ORA-01407: cannot update ("SCOTT"."C_TEST1"."DEPTNO") to NULL
부모 테이블을 지우려고 시도했더니 자식 테이블(c_test1) 을 null 로 update 할 수 없다고 에러가 나고 작업이 수행되지 않음이 확인됩니다.
select * from c_test2;
NO NAME
------------ ----------
30 CCCC
이 두 가지 옵션은 의도하지 않은 많은 문제가 생길 수 있으므로 사용시 주의해야 합니다.
3. 제약조건 관리하기
테이블의 컬럼에 설정되는 각 제약 조건들은 어떤 필요에 의해서 일시적으로 DISABLE / ENABLE 할 수 있다.
예를 들어 이미 검증된 대량의 데이터를 테이블에 입력할 경우 데이터를 입력할 때 제약조건을 다시 검사할 필요가 없을 것이다.
(1) 제약조건 DISABLE 하기
DISABLE 하는 옵션은 NOVALIDATE 와 VALIDATE 두 가지가 있다.
NOVALIDATE 옵션은 해당 제약조건이 없어서 데이터가 전부 들어온다는 뜻이다.
1) DISABLE NOVALIDATE 사용하기
INSERT INTO test_novalidate VALUES(1, 'DDD') ;
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0010950) violated
ALTER TABLE test_novalidate
DISABLE NOVALIDATE CONSTRAINT SYS_C0010950 ;
INSERT INTO test_novalidate VALUES(1, 'DDD') ;
정상적으로 입력된다.
DISABLE NOVALIDATE 로 제약조건을 DISABLE 하게 되면 CONSTRAINT 가 없는 것과 동일하게 작동한다.
2) DISABLE VALIDATE 사용하기
INSERT INTO test_validate VALUES(4, NULL) ;
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_VALIDATE"."NAME")
INSERT INTO test_validate VALUES(4, NULL) ;
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.TV_NAME_NN)
disabled and validated
여전히 되지 않는다. 해당 컬럼은 insert/update/delet 할 수 없다는 내용이다.
즉 이 옵션은 해당 컬럼의 데이터를 변경할 수 없게 하는 옵션이다.
3) DISABLE VALIDATE 옵션 설정 후 다른 컬럼 내용 변경하기
test_validate 테이블에 name 컬럼에 설정되어 있던 NOT NULL 제약조건이 DISABLE VALIDATE 되어 있는
상태에서 다른 컬럼에 데이터를 입력하겠다.
INSERT INTO test_validate VALUES (4,'DDD') ;
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.TV_NAME_NN)
disabled and validated
NULL 값 입력하기
INSERT INTO test_validate VALUES (4,NULL) ;
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.TV_NAME_NN)
disabled and validated
다른 컬럼에만 데이터 입력하기
INSERT INTO test_validate(no) VALUES(4) ;
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.TV_NAME_NN)
disabled and validated
위 테스트 결과 DISABLE VALIDATE 옵션은 결과적으로 테이블의 내용을 변경할 수 없도록 함을 알 수 있다.
UPDATE 나 DELETE 또한 동일한 결과를 볼 수 있다.
DISABLE 의 기본 옵션은 NOVALIDATE 이다.
PRIMARY KEY 나 UNIQUE 제약조건을 DISABLE 할 경우 생성되어 있던 UNIQUE INDEX 가 자동으로 삭제된다는 점도 꼭 기억하기 바란다.
(2) 제약조건 ENABLE 하기
DISABLE 되어 있던 제약조건을 ENALBE 하는 방법도 NOVALIDATE / VALIDATE 두 가지가 있다.
기본값이 ENABLE VALIDATE 입니다.
ENABLE NOVALIDATE 는 제약조건을 ENABLE 하는 시점까지 테이블에 들어있는 데이터는 검사하지 않고,
ENABLE 한 시점 이후부터 입력되는 데이터만 제약조건을 적용하여 검사하는 옵션이다.
ENABLE VALIDATE 는 제약조건을 ENABLE 하는 시점까지 테이블에 입력되어 있던 모든 데이터를 전부 검사하며
신규로 입력되는 데이터도 전부 검사하는 옵션이다.
그렇기에 해당 제약조건을 ENABLE VALIDATE 하게 되면 오라클이 해당 테이블에 데이터가 변경되지 못하도록
(기존 데이터를 검사해야 하므로) LOCK 을 설정하게 된다.
검사 도중 제약조건을 위반하는 값이 발견되면 에러를 발생하면서 제약조건 ENABLE 작업을 취소한다.
그렇게 되면 DBA나 사용자가 그 위반되는 데이터를 찾아서 적절하게 조치 후 다시 ENABLE 작업을 반복해서 시도해야 한다.
문제는 그 위반되는 데이터를 일일이 찾아서 조치를 해줘야 한다는 것이다.
데이터가 많은 테이블은 데이터 이전 후 검증하는 데 시간이 아주 오래 걸릴 수 있다.
이런 문제를 조금이라도 해결하기 위해 ENABLE VALIDATE 일 경우 사용할 수 있는 EXCEPTIONS 라는 테이블이 존재한다.
이 테이블에 위반 사항을 저장하게 설정하면 한결 쉽게 ENABLE 작업을 할 수 있다.
아래 실습으로 ENABLE NOVALIDATE 와 ENABLE VALIDATE 작업을 해 보겠다.
테스트용 테이블 test_enable 에 데이터를 입력하겠다.
INSERT INTO test_enable VALUES(1,'AAA');
INSERT INTO test_enable VALUES(2,'BBB');
INSERT INTO test_enable VALUES(3,NULL);
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ENABLE"."NAME")
위 테스트의 3번째 데이터가 NOT NULL 제약조건에 걸려서 입력이 안된다.
해당 제약조건을 DISABLE 로 변경 후 다시 해보겠다.
ALTER TABLE test_enable
DISABLE CONSTRAINT te_name_nn ;
INSERT INTO test_enable VALUES(3,NULL);
잘 입력된다.
위 테스트 결과로 test_enable 테이블에는 잘못된 데이터가 들어가 있다.
이 상태에서 제약조건을 EANBLE 시키도록 하겠다.
1) ENABLE NOVALIDATE 로 name 컬럼 제약조건 ENABLE 하기
ALTER TABLE test_enable
ENABLE NOVALIDATE CONSTRAINT te_name_nn ;
SELECT * FROM test_enable ;
NO NAME
---------- ----------
1 AAA
2 BBB
3 ← 이 행이 잘못된 데이터 이다.
INSERT INTO test_enable VALUES(4,NULL);
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ENABLE"."NAME")
2) ENABLE VALIDATE로 name 컬럼 제약조건 ENABLE 하기
테스트용 테이블 test_enable 의 name 컬럼에 설정되어 있는 NOT NULL 제약 조건을 DISABLE
시킨 후 다시 ENABLE VALIDATE 로 ENABLE 시키도록 하겠습니다.
ALTER TABLE test_enable
DISABLE CONSTRAINT te_name_nn ;
ALTER TABLE test_enable
ENABLE VALIDATE CONSTRAINT te_name_nn ;
ERROR at line 2:
ORA-02293: cannot validate (SCOTT.TE_NAME_NN) - CHECK constraint violated
위 결과처럼 테이블에 잘못된 데이터가 들어가 있기 때문에 ENABLE VALIDATE 를 할 수 없다.
이럴 때 문제가 되는 행을 찾아서 수정해야 하는데 데이터가 많을 경우 일일이 그 행을 찾기 힘들 때가 많다 .
이렇게 ENABLE VALIDATE 할 때 문제가 되는 행을 별도의 테이블에 저장해서 문제 행을 쉽게 찾을 수 있도록 해 주는 기능이
EXCEPTIONS 테이블 이다. 이 기능은 사용자가 별도로 생성하고 설정해야 한다.
아래 실습 3번을 참고.
(3) EXCEPTIONS 테이블을 사용하여 ENABLE VALIDATE 하기
STEP1 SYS 계정으로 exception table 생성 (꼭 SYS 계정으로 할 필요는 없다.)
@?/rdbms/admin/utlexcpt.sql → ?는 $ORACLE_HOME 디렉토리를 의미함.
STEP2 테스트용 테이블 scott.tt550을 not null 제약조건을 가지도록 생성
CREATE TABLE scott.tt550
(no NUMBER CONSTRAINT tt550_nn NOT NULL);
STEP3 테스트용 테이블의 NOT NULL 속성을 사용 안함 모드로 변경
ALTER TABLE scott.tt550 DISABLE CONSTRAINT tt550_nn ;
STEP4 테스트용 데이터를 입력하는데 NULL값도 함께 입력
INSERT INTO scott.tt550 VALUES (1);
INSERT INTO scott.tt550 VALUES (null);
null 값 입력함. 이상 없이 들어감
INSERT INTO scott.tt550 VALUES (2);
COMMIT ;
SELECT * FROM scott.tt550;
NO
----------
1
← 문제가 될 NULL 부분
2
STEP5 NOT NULL 제약 조건을 사용함으로 변경하면서 exception table 을 사용하게 설정
ALTER TABLE scott.tt550 ENABLE VALIDATE CONSTRAINT tt550_nn
EXCEPTIONS INTO scott.exceptions ;
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.TT550_NN) - check constraint violated
에러 발생
STEP6 exceptions table 을 조회하여 에러 내역을 확인
SELECT rowid, no
FROM scott.tt550
WHERE rowid in (SELECT row_id FROM exceptions) ;
ROWID NO
------------------ ----------
AAASNwAAEAAApFfAAB ← NO 의 값이 NULL 임이 확인된다.
STEP7 테스트용 테이블 scott.tt550 에서 문제가 되는 부분을 정상적인 값으로 업데이트
UPDATE scott.tt550
SET no=3
WHERE rowid ='AAASNwAAEAAApFfAAB' ;
COMMIT ;
TRUNCATE TABLE scott.exceptions;
수정 완료한 에러 내역을 삭제하기 위해 TRUNCATE 한다.
ALTER TABLE scott.tt550 ENABLE VALIDATE CONSTRAINT tt550_nn
EXCEPTIONS INTO scott.exceptions ;
SELECT * FROM scott.tt550;
NO
----------
1
3 ← null이었던 값이 정상적인 데이터로 변경되었다.
2
STEP8 NOT NULL이 사용함인 상태에서 일부러 NULL값을 입력하여 NOT NULL이 작동함을 확인
INSERT INTO scott.tt550 VALUES (null);
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TT550"."NO")
null 데이터가 입력되지 않는다.
(4) 제약조건 조회하기
테이블에 제약조건을 설정하면 그 내용이 딕셔너리에 저장되어 있다.
딕셔너리의 내용을 조회하여 테이블에 설정되어 있는 제약조건을 확인하겠다.
사용하는 USER_CONSTRAINTS 와 USER_CONS_COLUMNS 이며
데이터베이스 전체의 제약조건을 조회하려며 DBA_CONSTRAINTS 와 DBA_CONS_COLUMNS 를 사용하면 된다.
사용 예1: emp4 테이블에 설정되어 있는 제약조건 조회하기
SELECT owner, constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name='EMP4';
constraint_type 의 타입 값 중 P: Primary Key , U:Unique , C: CHECK , R:외래키 를 의미합니다.
사용 예 2: FOREIGN KEY 조회하기
COL child_table FOR a15
COL child_column FOR a15
COL child_cons_name FOR a15
COL parent_table FOR a15
COL parent_cons_name FOR a15
COL parent_column FOR a15
SELECT a.table_name "Child_Table",
c.column_name "Child_Column",
a.constraint_name "Child_Cons_name" ,
b.table_name "Parent_Table" ,
a.r_constraint_name "Parent_Cons_na me" ,
d.column_name "Parent_Column"
FROM user_constraints a , user_constraints b , user_cons_columns c,
(SELECT constraint_name, column_name, table_name
FROM user_cons_columns) d
WHERE a.r_constraint_name=b.constraint_name
AND a.constraint_name=c.constraint_name
AND a.r_constraint_name=d.constraint_name
AND a.constraint_type='R' ;
(5) 제약조건 삭제하기
ALTER TABLE emp4
DROP CONSTRAINT emp4_name_fk ;
이상으로 제약조건에 대해 살펴보았다.
제약조건을 잘 설정하고 관리해야 데이터의 무결성과 작업의 효율성 등이 높아지므로 꼭 잘 숙지하시고 사용하시기 바란다.
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저