[SQL-오라클] 데이터 무결성 제약조건

데이터 무결성 제약조건

 

장점

- 데이터의 정확성과 일관성을 보장

- 테이블 생성 시 무결성 제약조건을 정의 가능

- 테이블에 대해 정의, 데이터 딕셔너리에 저장되므로 응용 프로그램에서 입력된 모든 데이터에 대해 동일하게 적용

- 제약조건을 활성화, 비활성화 할 수 있는 융통성

 

종류

무결성 제약조건	설명
NOT NULL	해당 칼럼 값은 NULL을 포함할 수 없음
고유키	테이블 내에서 해당 칼럼 값은 항상 유일해야 함  NULL값을 허용
기본키	해당 칼럼 값은 반드시 존재해야 하며, 유일해야 함 NOT NULL + 고유키 제약조건
참조	해당 칼럼 값은 참조되는 테이블의 칼럼 값 중 하나와 일치하거나 NULL을 가짐
CHECK	해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정 (도메인 제약조건)

 

 

NOT NULL 

- 해당 칼럼이 NULL 값을 가질 수 없다는 것을 정의

- 해당 칼럼에는 반드시 값을 입력해야 하고, 생략하면 오류 발생

- 기존 데이터를 NULL로 수정하는 경우에도 오류 발생

 

고유키

- 한 테이블 내 해당 칼럼이 동일한 값을 가질 수 없음을 정의

- NULL은 고유키 제약조건에 위반되지 않음으로 입력 가능

 

기본키

- 하나 이상의 칼럼에 의해 테이블의 모든 행을 구별하기 위한 식별자를 정의

- 고유키 제약조건과 NOT NULL 제약조건을 결합한 개념

 

참조

- 한 테이블의 칼럼 값이 자신, 다른 테이블의 칼럼 값 중에 하나와 일치시키기 위한 제약조건

• 자식 테이블 : 다른 테이블의 칼럼 값을 참조하는 테이블
• 부모 테이블 : 다른 테이블에 의해 참조되는 테이블
• 외래키 : 부모 테이블의 칼럼 값을 참조하는 자식 테이블의 칼럼
• 참조키 : 자식 테이블에서 참조하는 부모 테이블의 칼럼

DML 명령문	부모 테이블	자식 테이블
INSERT 삽입	참조 키 값이 고유한 경우에만 가능	외래 키 값이 참조 키 값 중의 하나와 일치하거나 NULL인 경우에 가능
UPDATE 수정	참조 키 값을 참조하는 자식 테이블의 칼럼 값이 없는 경우에만 간으	수정되는 외래키 값이 참조 키 값 중의 하나와 일치할 경우에 가능
DELETE 삭제	참조 키 값을 참조하는 자식 테이블의 칼럼 값이 없는 경우에만 가능 1. 거절  2. 연쇄  3. NULL  4. DEFAULT	항상 가능
DELETE CASCADE	항상 가능	항상 가능

- cascade : 삭제되는 칼럼을 참조하는 참조 무결성 제약조건도 함께 삭제 

 

 

CHECK

- 칼럼에서 허용 가능한 데이터의 범위나 조건을 지정

데이터 입력이나 수정 시 실수로 부정확한 값을 입력 예방

- 하나의 칼럼에 여러 개의 CHECK 무결성 제약조건 지정 가능

- 가상 칼럼, 함수 사용 불가능

 

 

---

 

무결성 제약조건 생성

- 테이블 생성과 동시에 정의 가능

- 테이블 생성 이후에 추가, 삭제 가능

- 제약조건명을 지정하지 않으면 SYS_Cn 형태로 자동생성

create table 테이블명
( 칼럼명 데이터타입(길이) [ 기본값 ]
[ 칼럼 제약조건 ],
[ 테이블 제약조건 ], [, ...]);

 

> 제약 조건명 지정 시

create table 테이블명
( 칼럼명 데이터타입(길이) constraint 제약조건이름 primary key,
칼럼명 데이터타입(길이) constraint 제약조건이름 NOT NULL,
칼럼명 데이터타입(길이) constraint 제약조건이름 references 테이블명(칼럼명),
칼럼명 데이터타입(길이) constraint 제약조건이름 CHECK( 테이블명 IN ( 조건 / 범위 ));

 

- 칼럼 레벨 생성

• 칼럼 정의 시 해당 칼럼별로 지정 가능
• NOT NULL  무결성 제약조건은 칼럼 레벨에서만 정의 가능

column_constraint : ==
{ [NOT] NULL
| primary key
| unique
| references 테이블명 [( 칼럼명 [, 칼럼명] ...)]
| check ( 조건)};

 

 

- 테이블 레벨 생성

• 제약조건이 하나 이상의 칼럼을 참조하거나 지정하는 경우 사용
• 하나의 칼럼에 두 개 이상의 제약조건을 지정하는 경우 사용

talbe_constraint : ==
{{ unique | primary key} ( 칼럼명 [ , 칼럼명 ] ...)
| primary key
| unique
| foreign key ( 칼럼명 [ , 칼럼명 ] ...) references 테이블명 [( 칼럼명 [, 칼럼명] ...)]
| check (조건)};

 

 

---

 

무결성 제약조건 추가

ALTER TABLE ... ADD CONSTRAINT

- 기존 테이블에 대해서도 무결성 제약조건을 추가로 생성 가능

- NULL 무결성 제약조건은 ALTER ... ADD 명령문으로 추가 불가

alter table 테이블명
add [ constraint 제약조건이름 ] 제약조건타입(칼럼명);

 

참조 무결성 제약조건 추가

alter table 테이블명
add constraint 제약조건이름 foreign key ( 칼럼명 ) references 참조하는테이블명 ( 칼럼명);

 

NULL 무결성 제약조건 추가

ALTER TABLE ... MODIFY

- 'NULL 허용' 상태를 'NULL 입력 불가' 상태로 변경하는 것을 의미

- ALTER TABLE ... MODIFY 명령문 사용하여 NULL 상태 변경 가능

alter table 테이블명
modify ( 칼럼명 constraint 제약조건이름 NOT NULL);

 

---

 

무결성 제약조건 삭제

ALTER TABLE ... DROP CONSTRAINT

- 부모 테이블의 기본키 무결성 제약조건을 삭제하는 경우

1. 참조 무결성 제약조건을 먼저 삭제한 후 삭제하거나 2. CASCADE 옵션 사용

alter table 테이블명
drop constraint 제약조건이름 [cascade];

- cascade : 삭제되는 칼럼을 참조하는 참조 무결성 제약조건도 함께 삭제

 

---

 

무결성 제약조건 활성화 및 비활성화

- ALTER TABLE 명령문에서 ENABLE (활성화) 또는 DISABLE (비활성화) 절 사용

 

비활성화 

ALTER TABLE ... DISABLE 

alter table 테이블명
disable constraint 제약조건이름 [cascade];

 

활성화

ALTER TABLE ... ENABLE

alter table 테이블명
enable [novalidate] constraint 제약조건이름 [cascade];

- novalidate : 기존 데이터에 대해서는 제약조건을 적용하지 않고,

                  새로 입력되는 데이터나 수정하는 데이터에 대해서만 제약조건을 검사하기 위한 옵션

 

 

무결성 제약조건 검사 시점

즉시 제약조건(immediate constraint) 검사

- DML 명령문을 실행할 때마다 무결성 제약조건의 위반 여부를 검사

- 제약조건을 위반한 경우 DML 명령문을 ROLLBACK 시키고 오류 메시지 출력

- 제약조건 생성 시 'NOT DEFERRED' 'DEFERRABLE INITIALLY IMMEDATE'로 지정된 경우 즉시 제약조건

 

지연 제약조건(deffered constraint) 검사

- 트랜잭션의 COMMIT 시점에 무결성 제약조건의 위반 여부를 검사

- 트랜잭션 내에서 제약조건을 위반한 DML 명령문이 있는 경우 해당 트랜잭션을 모두 ROLLBACK 시킴

- 제약조건 생성 시 'DEFERRABLE INITIALLY DEFERRED'로 지정된 경우 지연 제약조건 

 

defer_spec :==
[ not deferrable | deferrable [initially {immediate | deferred}] ]
[ disable | enable [validate | novalidate] ]

 

 

---

 

무결성 제약조건 조회

USER_CONSTRAINTS

- 제약조건이 설정된 테이블 이름, 무결성 제약조건 이름, 무결성 제약조건 종류, 활성화 상태정보 저장

select table_name, constraint_name, constraint_type, status
from user_constraints
where table_name = ' 대문자 테이블명'
                  [in('대문자 테이블명', '대문자 테이블명', ...)];

USER_CONS_COLUMNS

- 제약조건이 설정된 칼럼 이름을 저장

select table_name, column_name, constraint_name
from user_cons_colums
where table_name = ' 대문자 테이블명'
                  [in('대문자 테이블명', '대문자 테이블명', ...)];

 

 

 

---

참고도서 : 오라클 중심의 SQL배움터, 우용태 외 공저, 생능출판사, 2007년