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

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

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)
);

다. 무결성 제약조건 추가

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 명령문을 사용한다

라. 무결성 제약조건 삭제

alter table member2
drop constraint idnum_u;

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

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 : 제약조건이 정의된 컬럼, 제약조건 이름 등