KH_ORACLE_10_데이터 무결성을 위한 제약 조건

wn1331 2022. 12. 21. 17:16

데이터 무결성 제약 조건이란?

- 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러 가지 규칙을 말한다.

무결한 데이터의 5가지 제약 조건

무결성 제약 조건	역할
NOT NULL	NULL을 허용하지 않는다.
UNIQUE	중복된 값을 허용하지 않는다. 항상 유일한 값을 갖도록 한다.
PRIMARY KEY	NULL을 허용하지 않고 중복된 값을 허용하지 않는다. NOT NULL 조건과 UNIQUE 조건을 결합한 형태이다.
FOREIGN KEY	참조되는 테이블의 칼럼의 값이 존재하면 허용한다.
CHECK	저장 가능한 데이터의 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

제약 조건 확인하기

- CONSTRAINT_NAME은 제약 조건 명이다.

- CONSTRAINT_TYPE은 제약 조건 유형을 저장하는 컬럼이다.

INSERT INTO DEPT
VALUES(10, 'TEST', 'SEOUL');
-- 오류발생: 무결성 제약 조건에 위배됩니다.
DESC DEPT
SELECT * FROM DEPT;
DESC USER_CONSTRAINTS;

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='DEPT';

필수 입력을 위한 NOT NULL 제약 조건

- NOT NULL 제한 조건은 해당 컬럼에 데이터를 추가하거나 수정할 때 NULL 값이 저장되지 않게 제약을 걸어주는 것으로서 사원번호와 사원명과 같이 자료가 꼭 입력되게 하고 싶을 때 사용한다.

컬럼 레벨로 제약 조건명을 명시하여 제약 조건 설정하기

- 사용자가 제약조건을 주면 오라클은 SYS_ 다음에 숫자를 나열하여 제약 조건 명을 자동 부여한다.

사용자가 직접 제약 조건 명을 설정하려면 CONSTRAINT 라는 키워드를 사용한다.

-- 형식
column_name data_type CONSTRAINT constraint_name constraint_type
-- 예: 컬럼 레벨로 제약 조건명 명시하기

DROP TABLE EMP04;

CREATE TABLE EMP04(
EMPNO NUMBER(4) CONSTRAINT EMP04_EMPNO_UK UNIQUE,
ENAME VARCHAR2(10));

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP04';

데이터 구분을 위한 PRIMARY KEY 제약 조건

- UNIQUE 제약 조건과 NOT NULL 제약 조건을 모두 갖는 것이 기본 키(PRIMARY KEY) 제약 조건이다.

- UNIQUE 제약 조건을 지정한 칼럼은 중복된 데이터를 저장하지는 못하지만 NULL 값을 저장하는 것은 허용한다. 이와 같이 동명이인이 입사를 했다면 이를 구분할 수 있는 유일한 키가 있어야 하는데 사원번호에 NULL 값이 저장되는 바람에 이들을 구분할 수 없게 된다.

-- PRIMARY KEY 제약 조건 설정하기
DROP TABLE EMP05;

CREATE TABLE EMP05(
EMPNO NUMBER(4) CONSTRAINT EMP05_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(10) CONSTRAINT EMP05_ENAME_NN NOT NULL,
JOB VARCHAR2(9),
DEPTNO NUMBER(2)
);

INSERT INTO EMP05 VALUES(7499,'ALLEN','SALESMAN',30);

SELECT * FROM EMP05;

INSERT INTO EMP05 VALUES(7499,'JONES','MANAGER',20); -- SQL Error: ORA-00001: unique constraint
(SCOTT.EMP05_EMPNO_PK) violated
INSERT INTO EMP05 VALUES(NULL,'JONES','MANAGER',20); -- SQL Error: ORA-01400: cannot insert NULL into
("SCOTT"."EMP05"."EMPNO")

참조 무결성을 위한 FOREIGN KEY 제약 조건

- 참조의 무결성은 테이블 사이의 관계에서 발생하는 개념이므로 사원 테이블과 부서 테이블의 관계를 예를 들어 설명하면

사원은 회사 내에 존재하는 부서에 소속되어 있어야 한다.

- 테이블을 생성하기에 앞서 데이터베이스 모델링 과정에서 업무를 분석한 후 얻어낸 테이블의 관계 (개체와 관계)를 다이어그램으로 나타낸 것이다. (ERD, Entity Relationship Diagram)

외래 키(FOREIGN KEY) 제약조건은 사원 테이블의 부서 번호는 반드시 부서 테이블에 존재하는 부서 번호를 참조 가능하도록 하는 것이다. (참조의 무결성)

ERD 표기 방법

논리적 설계

ERD 작성 (새발표기법)

물리적 설계(DDL 작성)

CREATE TABLE Publisher(
sendDate DATE,
passwordOwner NUMBER,
idPublisher NUMBER,
ReaderNum NUMBER NOT NULL
);
DROP INDEX XPKPublisher;
CREATE UNIQUE INDEX XPKPublisher ON Publisher (
ReaderNum ASC
);
ALTER TABLE Publisher
ADD PRIMARY KEY(ReaderNum);
...

CHECK와 DEFAULT의 제약 조건

- CHECK 제약 조건은 입력되는 값을 체크하여 설정된 값 이외의 값이 들어오면 오류 메시지와 함께 명령이 수행되지 못하게 하는 것이다.

- 디폴트는 아무런 값을 입력 하지 않았을 때 디폴트제약의 값이 입력이 된다.

테이블 레벨 방식으로 제약 조건 지정하기

컬럼 레벨 제약 조건

- CREATE TABLE로 테이블을 생성하면서 컬럼을 정의하게 되는데 하나의 컬럼 정의가 다 마 무리되기 전에 컬럼 명 다음에 타입을 지정하고 그 뒤에 연이어서 제약 조건을 지정하는 방식이다.

테이블 레벨의 제약 조건

- 칼럼을 모두 정의하고 나서 테이블 정의를 마무리 짓기 전에 따로 생성된 칼럼들에 대한 제약 조건을 한꺼번에 지정하는 것이다.

복합키로 기본키를 지정할 경우

- 지금까지는 한 개의 컬럼으로 기본키를 지정했다. 하지만, 경우에 따라서는 2개 이상의 컬럼이 하나의 기본키를 구성하는 경우가 있는데 이를 복합키라고 한다. 복합키 형태로 제약조건을 지정할 경우에는 컬럼 레벨 형식으로는 불가능하고 반드시 테이블 레벨 방식 을 사용해야 한다.

ALTER TABLE로 제약 조건을 추가할 때

- 테이블의 정의가 완료되어서 이미 테이블의 구조가 결정된 후에 나중에 테이블에 제약 조건을 추가하고 할 때에는 테이블 레벨 방식으로 제약 조건을 지정해야 한다.

제약 조건 변경하기

제약 조건 추가하기

- 테이블 구조를 결정하는 DDL을 학습하면서 테이블이 이미 생성된 이후에 테이블의 구조를 변경하기 위한 명령어로 ALTER TABLE을 사용한다는 것을 이미 학습하였다.

- 제약조건 역시 이미 테이블을 생성하면서 지정해주는 것이었기에 테이블 생성이 끝난 후에 제약조건을 추가해주기 위해서는 ALTER TABLE로 추가해 주어야 한다.

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

MODIFY로 NOT NULL 제약 조건 추가하기

- 이미 존재하는 테이블에 무결성 제약 조건을 추가로 생성하기 위해서 ALTER TABLE... ADD... 명령문을 사용하였다.

- 하지만 NOT NULL 제약 조건은 ADD 대신 MODIFY 명령문을 사용하므로 사용에 주의해야 한다.

이는 'NULL을 허용하는 상태' 에서 'NULL을 허용하지 않는 상태'로 변경하겠다는 의미로 이해하기 바란다.

제약 조건 제거하기

- 제약 조건을 제거하기 위해서 DROP CONSTRAINT 다음에 제거하고자 하는 제약 조건 명을 명시해야 한다.

-- 형식
ALTER TABLE table_name
DROP [CONSTRAINT constraint_name];

제약 조건의 비활성화와 CASCADE

제약 조건에 의해 작업이 어려운 경우

- 제약 조건이 설정되면 항상 그 규칙에 따라 데이터 무결성이 보장된다.

- 특별한 업무를 수행하는 과정에서 이러한 제약 조건 때문에 작업이 진행되지 못하는 경우가 생긴다.

- 그렇다고 제약 조건을 삭제해 버리면 데이터 무결성을 보장받지 못하게 된다.

- 그렇기 때문에 오라클에서는 제약 조건을 비활성화시킴으로서 제약 조건을 삭제하지 않고도 제약 조건 사용을 잠시 보류할 수 있는 방법을 제공해준다.

- 이렇게 비활성화 된 제약 조건은 원하는 작업을 한 후에는 다시 활성화 상태로 만들어 주어야 한다.

제약 조건의 비활성화

- 테이블에서 제약 조건을 삭제하지 않고 일시적으로 적용시키지 않도록 하는 방법으로 제약 조건을 비활성화하는 방법이 있다.

-- 형식
ALTER TABLE table_name
DISABLE [CONSTRAINT constraint_name];

제약 조건의 활성화

- 제약 조건을 비활성화 해 보았으므로 이번에는 제약 조건을 활성화하는 예제를 짜 보자.

-- 형식
ALTER TABLE table_name
ENABLE [CONSTRAINT constraint_name];

CASCADE 옵션

- CASCADE 옵션은 부모 테이블과 자식 테이블 간의 참조 설정이 되어 있을 때 부모 테이블의 제약 조건을 비활성화하면 이를 참조하고 있는 자식 테이블의 제약 조건까지 같이 비활성화 시켜 주는 옵션이다.

- 또한 제약 조건의 비활성화뿐만 아니라 제약 조건이 삭제에도 활용되며, 역시 같은 이치로 부모 테이블의 제약 조건을 삭제하면 이를 참조하고 있는 자식 테이블의 제약 조건도 같이 삭제된다.