SQL 문법(데이터 무결성 제약조건)

 

1. 무결성 종류
가. Not Null : Null이 아닌 값만 허용
나. Unique Key : 해당 컬럼에서 중복되지 않고 유일한 값만 허용
다. Primary Key : Not null 와 Unique key의 결합된 형태, 1개의 테이블에 1번만 사용가능
라. Foreign Key : 참조되는 테이블의 컬럼 값과 일치하거나 Null만 허용
마. Check : 저장 가능한 데이터 값의 범위나 조건안에서 허용

참고 : 제약조건은 primary key는 테이블에, 그 외는 컬럼에 걸린다.

2. 무결성 제약조건의 특징
가. 테이블에 정의, 데이터 딕셔너리에 저장
나. 일시적으로 활성화/비활성화 가능


3. 참조 무결성 제약조건
가. 참조 당하는 쪽 : 부모 테이블, 참조키(Reference Key)
나. 참조 하는 테이블 : 자식 테이블, 외래키(Foreign Key)

참고 : 참조 무결성 제약조건을 걸 경우

자식테이블에 insert 시, foreign key 검증을 위해 부모테이블을 full scan하며, 부모테이블에 delete 시 자식테이블에 reference key 여부확인을 위해 자식테이블을 full scan한다.
이로인한 성능저하를 막기위해 양쪽테이블에 인덱스를 생성해 주어야 한다.

4. 연습문제

가. 테이블 생성1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

create table member2 (userid varchar2(10) constraint userid_pk primary key, username varchar2(20), passwd varchar2(10) constraint passwd_nn not null, idnum varchar2(13) constraint idnum_nn not null, phone varchar2(13) constraint phone_nn not null, address varchar2(2), regdate date, email varchar2(15) constraint email_nn not null constraint email_u unique );

나. 테이블 생성2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

create table dept2 (deptno number(3) constraint deptno_pk primary key, dname varchar2(20) constraint dname_nn not null, upper number(3) ); create table sawon (sabun number(5), name varchar(20) constraint name_nn not null, tel number(15), deptno number(3), hiredate date default sysdate, constraint dept_fk foreign key(deptno) references dept2(deptno) );

다. 무결성 제약조건 추가

1 2 3 4 5 6 7 8 9 10 11 12 13 14

alter table sawon add constraint sabun_pk primary key(sabun); alter table sawon add constraint dept_fk2 foreign key(deptno) references dept2(deptno); alter table member2 add constraint idnum_u unique(idnum); alter table member2 add constraint regdate_ck check(regdate between '01-JAN-2012' and '31-DEC-2012'); alter table member2 modify (username constraint username_nn not null);

참고 : not null을 추가할 경우 modify 명령문을 사용한다

라. 무결성 제약조건 삭제

1 2	alter table member2 drop constraint idnum_u;

마. 무결성 제약조건 활성화/비활성화

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

alter table member2 disable constraint regdate_ck; select table_name, constraint_name, status from user_constraints where table_name='MEMBER2'; TABLE_NAME CONSTRAINT_NAME STATUS ------------------------------ ------------------------------ -------- MEMBER2 REGDATE_CK DISABLED alter table member2 enable constraint regdate_ck; select table_name, constraint_name, status from user_constraints where table_name='MEMBER2'; TABLE_NAME CONSTRAINT_NAME STATUS ------------------------------ ------------------------------ -------- MEMBER2 REGDATE_CK ENABLED

바. 무결성 제약조건 데이터 딕셔너리(Data Dictionary)
- USER_CONSTRAINTS : 제약조건이 정의된 테이블, 제약조건, 상태, 종류 등
- USER_CONS_COLUMNS : 제약조건이 정의된 컬럼, 제약조건 이름 등