[SQL 25] 제약 조건 관리

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

제약 조건을 생성하는 방법은 테이블을 생성할 때 같이 생성하거나 나중에 추가하는 방법이 있다. 그리고 생성된 제약 조건은 작업 환경에 따라 활성화 되거나 비활성화 될 수 있다.

No. 01
제약 조건 추가

기본 구조

ALTER TABLE 테이블

ADD CONSTRAINT 제약_조건_이름 constraint_type (컬럼);

• NOT NULL을 제외한 제약 조건을 추가한다.
• constraint_type : 제약 조건 타입, 제약 조건별 설정 방법과 동일하다.
• PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
• NOT NULL 제약 조건은 MODIFY 명령을 이용해서 추가한다.

ALTER TABLE 테이블

MODIFY 컬럼 CONSTRAINT 제약_조건_이름 NOT NULL;

• NOT NULL 제약 조건을 추가한다.

NOT NULL의 경우 ADD를 이용하는 것이 아니라 MODIFY를 이용하는 특징이 있음을 기억한다.

◈ 예제

예제용 테이블에 입력된 데이터를 확인하고 제약 조건을 추가한다. 데이터가 입력되어 있는 테이블이 이미 있다고 가정한다.

▶ 예제용 테이블 생성

class(강좌)

Column Name	cno (강좌번호)	cname (강좌명)
Key Type	PK	UK
Nulls/Unique		UNIQUE
참조 테이블
참조 컬럼
CHECK
Data Type	VARCHAR2	VARCHAR2
Length	2	50

create table class(

cno varchar2(2),

cname varchar2(50)

);

Table CLASS이(가) 생성되었습니다.

insert into class values ('01', '데이터베이스');

1 행 이(가) 삽입되었습니다.

insert into class values('02', '프로그래밍');

1 행 이(가) 삽입되었습니다.

                                                         student(학생)

Column Name	sno (학번)	sname (이름)	cno (소속강좌)
Key Type	PK		FK
Nulls/Unique		NOT NULL
참조 테이블			class
참조 컬럼			cno
CHECK
Data Type	VARCHAR2	VARCHAR2	VARCHAR2
Length	4	50	2

create table student(

sno varchar2(4),

sname varchar2(50),

cno varchar2(2)

);

Table STUDENT이(가) 생성되었습니다.

insert into student values ('0414', '홍길동', '01');

1 행 이(가) 삽입되었습니다.

insert into student values ('0415', '임꺽정', '02');

1행 이(가) 삽입되었습니다.

insert into student values ('0416', '이순신', '03');

1행 이(가) 삽입되었습니다.

commit;

커밋 완료.

▶ 제약 조건 추가

alter table class

add constraint class_cno_pk primary key (cno);

Table CLASS이(가) 변경되었습니다.

alter table class

add constraint class_cname_uk unique (cname);

Table CLASS이(가) 변경되었습니다.

alter table student

add constraint student_sno_pk primary key (sno);

Table STUDENT이(가) 변경되었습니다.

alter table student

modify sname constraint student_sname_nn not null;

Table STUDENT이(가) 변경되었습니다.

alter table student

add constraint student_cno_fk foreign key (cno) references class (cno);

오류 보고 -

ORA-02298: cannot validate (HR.STUDENT_CNO_FK) - parent keys not found

STUDENT 테이블에서 참조하려는 CLASS 테이블의 cno 컬럼에 없는 강좌번호인 '03'이 STUDENT 테이블에 있기 때문에 참조 무결성이 위배되기 때문에 FK 제약 조건 추가가 실패했다.

테이블에 잘못된 데이터가 입력된 경우 제약 조건 추가가 실패할 수 있다.

update student set cno = '01'

where sno = '0416';

1 행 이(가) 업데이트되었습니다.

commit;

커밋 완료.

alter table student

add constraint student_cno_fk foreign key (cno) references class (cno);

Table STUDENT이(가) 변경되었습니다.

제약 조건에 맞게 데이터를 수정한 후에 제약 조건을 추가할 수 있다.

▶ 제약 조건 확인

select c.table_name, c.constraint_name, c.constraint_type, s.column_name

from user_constraints c, user_cons_columns s

where c.constraint_name = s.constraint_name

and c.table_name in ('CLASS', 'STUDENT')

order by c.table_name;

제약조건이 잘 생성되어 있는 것을 볼 수 있다.

No. 02
제약 조건 삭제

기본 구조

ALTER TABLE 테이블

DROP PRIMARY KEY | UNIQUE (컬럼) | CONSTRAINT 제약_조건_이름 [CASCADE];

• 제약 조건을 삭제한다.
• CASCADE : PK나 UK가 삭제될 경우 이를 참조하는 FK도 삭제된다.

◈ 예제

alter table class

drop constraint class_cno_pk;

오류 보고 -

ORA-02273: this unique/primary key is referenced by some foreign keys

FK에 의해 참조되는 PK나 UK는 삭제되지 않는다.

alter table class

drop constraint class_cno_pk cascade;

Table CLASS이(가) 변경되었습니다.

cascade 옵션을 기술한 경우 FK도 같이 삭제된다.

alter table class

drop unique (cname);

Table CLASS이(가) 변경되었습니다.

alter table student

drop constraint student_sname_nn;

Table STUDENT이(가) 변경되었습니다.

alter table student

drop primary key;

Table STUDENT이(가) 변경되었습니다.

PK나 UK같은 경우는 제약_조건_이름을 기술하지 않아도 간단히 삭제할 수 있다. PK, UK도 다른 제약조건과 마찬가지로 제약_조건_이름을 기술하고 삭제도 할 수 있다.

No. 03
제약 조건 활성화/비활성화

제약 조건 활성화

ALTER TABLE 테이블

ENABLE CONSTRAINT 제약_조건_이름;

• 비 활성화된 제약 조건을 활성화 한다.
• PK나 UK 제약 조건이 활성화 되면 인덱스가 만들어진다.

제약 조건 비활성화

ALTER TABLE 테이블

DISABLE CONSTRAINT 제약_조건_이름 [CASCADE];

• 제약 조건을 비활성화 한다.
• PK나 UK인 경우 인덱스가 삭제된다.
• 비활성화 된 제약조건은 제약조건을 검사하지 않는다.

◈ 예제

alter table class

add constraint class_cno_pk primary key (cno);

Table CLASS이(가) 변경되었습니다.

alter table student

add constraint student_cno_fk foreign key (cno) references class (cno);

Table STUDENT이(가) 변경되었습니다.

select table_name, constraint_name, status

from user_constraints

where table_name in ('CLASS', 'STUDENT');

insert into student values ('0417', '장보고', '03');

CLASS 테이블에 03반이 없으므로 입력되 에러가 발생한다.

제약 조건이 활성화 된 경우 반드시 부모 테이블로부터 데이터가 있어야 한다.

alter table student

disable constraint student_cno_fk;

Table STUDENT이(가) 변경되었습니다.

select table_name, constraint_name, status

from user_constraints

where table_name in ('CLASS', 'STUDENT');

insert into student values ('0417', '장보고', '03');

1 행 이(가) 삽입되었습니다.

FK를 임시로 비활성화 하면 제약 조건과 무관하게 데이터를 입력할 수 있다. 하지만, 위처럼 제약 조건에 위배되는 데이터를 삽입 후 제약 조건을 다시 활성화 시키는 것은 오류가 발생한다.

insert into class values ('03', '소프트웨어');

1 행 이(가) 삽입되었습니다.

alter table student

enable constraint student_cno_fk;

Table STUDENT이(가) 변경되었습니다.

drop table student cascade constraint;

Table STUDENT이(가) 삭제되었습니다.

drop table class cascade constraint;

Table CLASS이(가) 삭제되었습니다.

제약 조건은 데이터베이스의 무결성을 보장하는 기능이지만 작업의 순서를 반드시 제약 조건에 따라 계획해야 하는 문제가 있다. 위 예제의 경우 제약 조건을 비활성화 하고 데이터를 삽입하고 다시 제약 조건을 활성화 하는 작업을 했다. 사실 CLASS 테이블에 ('03', '소프트웨어') 데이터를 먼저 삽입하고 STUDENT 테이블에 ('0417', '장보고', '03') 데이터를 삽입하면 제약 조건을 활성화/비활성화 하는 귀찬은 작업은 하지 않아도 된다. 그럼에도 불구하고 제약 조건 활성화/비활성화를 사용하여야 할 때가 있다.

만일 입력 정보가 이미 무결성이 확인된 정보라면 이렇게 작업을 계획하는 과정은 매우 불편한 과정임에 틀림없다. 이런 경우 관련 제약 조건을 모두 비 활성화환 이후에 작업을 수행하면 테이블의 상하 관계와는 무관하게 작업 진행이 가능할 뿐 아니라 데이터 량이 많아 질수록 작업 시간을 더 많이 절약 할 수 있다. 특히, 여러 사람이 여러 테이블에 데이터를 삽입하는 작업을 할 때 유용하게 사용된다. 제약 조건이 활성화 돼 있으면 순서대로 데이터를 삽입해야 하므로 여러 사람이 같이 작업을 하는 의미가 없다.

---

 TIP    대규모 배치 작업을 효율적으로 수행하는 방법

1. 다이렉트 로드를 이용 작업을 수행한다.

2. 입력 테이블과 관련된 모둔 제약 조건을 비 활성화 한다.

● 제약 조건을 비 활성화 하면 작업의 순서를 지키지 않아도 되는 것 뿐 아니라 데이터를 삽입할 때 제약 조건을 검사하지 않고 삽입하기 때문에 입력 속도가 더 빠르다.

● 제약 조건에 맞지 않은 데이터는 입력이 끝난 이후 EXCEPTION 기능을 이용하여 걸러낼 수 있다.

---