[Oracle] Integrity Constraints 개념 및 실습

Published on May 18, 2021

목차

  1. Introduction
  2. NOT NULL
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. UNIQUE
  6. CHECK
  7. Constraint 관련 명령문
  8. constraint 확인
  9. constraint DISABLE


※ Oracle 기준

※ 이번 포스팅에서는 테이블을 직접 생성하면서 예제를 진행합니다.

※ DB 이론 관련 모든 포스팅 목록도 여기를 참고하세요


Introduction

Integrity Constraints (무결성 제약조건, 이하 I.C)은 데이터베이스의 테이블에 유효하지 않은 데이터가 들어가지 못하도록 합니다. I.C를 정의하여 데이터베이스 정보와 연결된 비즈니스 규칙을 적용할 수 있습니다. CREATE문과 ALTER문에 선택적으로 포함할 수 있습니다. 만약 DML문의 결과 중 하나라도 I.C를 위반하면 oracle은 이를 roll back1(#rollback)하고 error를 반환합니다.

Data Integrity의 예

EMP, DEPT 테이블과 각 테이블의 정보에 대한 비즈니스 규칙을 확인할 수 있습니다.


Constraint는 크게 두 가지로 나눌 수 있습니다.

Column level constraint

Column-level constraint는 단일 column을 참조합니다.

Tabel level constraint

Tabel-level constraint는 테이블에 있는 하나 이상2(예외)(#chk)의 column을 참조합니다. 바로 다음에 설명할 NOT NULL constraint는 tabel-level에서 정의할 수 없지만, 그 외의 다른 constraint는 모두 정의할 수 있습니다.

1: 현재 트랜잭션에서 수행된 작업을 실행 취소하거나 in-doubt distributed 트랜잭션에서 수행한 작업을 수동으로 취소하는 명령문입니다. 자세한 건 출처 4번 참고.

2: CHECK 제약 조건은 테이블에서 0개 이상의 열을 참조합니다.



NOT NULL

기본적으로, 모든 column은 null을 허용합니다. NOT NULL constraint를 추가하면 null을 허용하지 않게 됩니다. Table-level constraints 방식으로 constraints를 정의할 수 없습니다. 주의하세요!

Column datatype [CONSTRAINT constraint_name] constraint_type

예시

  1. Column-level constraint로 book1을 생성하고,
  2. Table-level로 book2 테이블 생성을 시도합니다.
  3. NOT NULL은 tabel-level에서 생성이 불가능합니다.



PRIMARY KEY

Primary key(기본 키)는 테이블에서 각 행을 유일하게 식별하는 column (또는 column 집합)을 정의합니다. 데이터베이스의 각 테이블은 최대 하나의 primary key constraint를 가질 수 있습니다. 이 constraint는 두 가지 효과를 가집니다.

  • 테이블의 각 row는 고유하게 식별할 수 있습니다. (UNIQUE)
  • primary key는 null을 허용하지 않습니다. (NOT NULL)

column-level statement

Column datatype [CONSTRAINT constraint_name] PRIMARY KEY (column1 [, column2, ..])

table-level statement

Column datatype, (중략)

[CONSTRAINT constraint_name] PRIMARY KEY (column1[, column2, ...])

예제

  1. Column-level constraint로 book1을,
  2. Table-level로 book2 테이블 생성합니다.
  3. primary key로 지정된 title column에 값이 없으면 error를 반환합니다.
  4. (다음 예제에 지장 없게 book2 테이블은 삭제했습니다.)



FOREIGN KEY

FOREIGN KEY(외래 키)는 다른 테이블의 primary key(이하 pk)를 참조하는 column (또는 column 집합)을 정의합니다. foreign key(이하 fk)은 다음의 특징을 가집니다.

  • fk를 가지는 column의 data는 fk가 참조하는 pk의 column의 data와 일치해야 합니다.
  • fk가 참조하는 pk는 삭제할 수 없습니다.
  • ON DELETE CASCADE 연산자 옵션을 포함하여 정의한 fk의 데이터는 그 pk가 삭제될 때 함께 삭제됩니다.

column-level statement

Column datatype [CONSTRAINT constraint_name]
REFERENCES table_name (column1 [, column2, ..] [ON DELETE CASCADE])

table-level statement

Column datatype, (중략)

[CONSTRAINT constraint_name] FOREIGN KEY (column1[, column2, ...])
REFERENCES table_name (column1 [, column2, ..] [ON DELETE CASCADE])

예시

  1. Column-level constraint로 review를,
  2. Table-level로 review2 테이블 생성합니다.
  3. fk로 지정된 rev_title의 값이 pk로 지정된 값(여기에선 hello world)와 일치하지 않으므로 error를 반환합니다.
  4. (다음 예제에 지장 없게 review2 테이블은 삭제했습니다.)


+) fk는 여러 테이블의 pk를 한꺼번에 가져올 수 없습니다!! 이런 경우 주의

+) 여기서 fk를 왜 사용해야 하는지 잘 설명한 것 같으니 참고하세요



UNIQUE

UNIQUE constraint는 테이블의 row를 고유하게 식별하는 column (또는 column 집합)을 정의합니다. 중복되는 데이터가 존재할 수 없지만, NULL을 허용하므로 NULL일 때는 예외로 중복이 허용됩니다.

column-level statement

Column datatype [CONSTRAINT constraint_name] UNIQUE

table-level statement

Column datatype, (중략)

[CONSTRAINT constraint_name] UNIQUE (column1 [, column2, ..])

예시

  1. Column-level constraint로 review2을,
  2. Table-level로 review3 테이블 생성합니다.
  3. fk인 rev_title만 잘 생성한다면, rev_id는 unique이므로 null이어도 괜찮습니다.
  4. (다음 예제에 지장 없게 review3 테이블은 삭제했습니다.)



CHECK

Check constraint는 조건을 정의하여 column의 값을 어떤 특정 범위로 제한할 때 사용합니다. 조건은 boolean expr로 정의해야 합니다. 한 column 당 정의할 수 있는 check constraint의 수는 제한이 없습니다.

column-level statement

Column datatype [CONSTRAINT constraint_name] CHECK (condition)

table-level statement

Column datatype, (중략)

[CONSTRAINT constraint_name] CHECK (condition)

예시

  1. Column-level constraint로 review3을,
  2. Table-level로 review4 테이블 생성합니다.
  3. check constraint로 rev_id 범위를 지정했기 때문에 범위를 벗어나는 값이 들어가면 error를 반환합니다.
  4. (다음 예제에 지장 없게 review4 테이블은 삭제했습니다.)



Constraint 관련 명령문

Constraint 변경

기존의 테이블에 새로운 제약 조건 추가.

ALTER TABLE table_name
ADD [CONSTRAINT constraint_name] constraint_type (column_name);

Constraint 삭제

기존의 테이블의 제약 조건 삭제. CASCADE 옵션은 삭제하려는 제약 조건을 참조하는 모든 제약 조건들도 같이 삭제합니다.

ALTER TABLE table_name
DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint_name [CASCADE];

constraint 확인

Data Dictionary에 schema3(#schema) 정보가 저장되어 있습니다. 두 가지 테이블을 살펴봅시다.

user_constraints table의 주요 column

column 의미
owner 제약 조건을 소유하는 사용자
constraint_name 제약 조건 이름
constraint_type 제약 조건 유형 (U, C, P, R)
table_name 제약 조건이 속한 테이블
search_condition constraint_type이 C인 행에 대해 각각의 조건을 나타냄
r_constraint_name 제약 조건이 FK인 경우 이것이 참조하는 PK 표시


constraint 약자

제약 조건 약어 constraint_type column 값
NOT NULL _NK C
UNIQUE _UK U
PRIMARY KEY _PK P
FOREIGN KEY _FK R
CHECK _CK C


user_constraints 테이블에는 다음과 같은 column들이 있습니다. 그 중 몇몇 column만 출력해서 constraint 정보를 확인할 수 있습니다.

user_cons_columns

constraint DISABLE

constraint를 삭제하지 않고, 적용하거나 적용하지 않을 수 있습니다.

DISABLE

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;

ABLE

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;

예시

3: 데이터베이스에서 자료의 구조, 자료의 표현 방법, 자료 간의 관계를 형식 언어로 정의한 구조입니다. 자세한 건 출처 5번 참고.



출처

  1. data integrity oracle docs
  2. Constraint clause oracle docs
  3. ROLLBACK oracle docs
  4. 데이터베이스 스키마 위키백과