DB/Oracle Basic

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

soccerda 2012. 7. 6. 01:06
반응형
 
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 : 제약조건이 정의된 컬럼, 제약조건 이름 등
반응형