제약조건

#핵심 요약

제약은 데이터베이스에 입력, 수정되는 데이터에 제한을 강제해 데이터 품질을 높임
처음부터 결측치가 들어오지 않게 설계하는 것이 가장 좋음

다섯 가지: primary key(중복과 NULL 금지), unique(중복 금지), not null(NULL 금지), check(지정한 값만 허용), foreign key(부모 테이블 값 참조)

unique 제약은 NULL 값을 허용함
unique와 primary key 제약을 걸면 인덱스가 자동으로 만들어짐

NOT NULL만 ADD가 아니라 MODIFY로 걺

위반 데이터가 있어도 제약을 다시 켜려면 ENABLE NOVALIDATE를 씀
기존 데이터는 검사하지 않고 새 DML만 검사함

#개념과 동작 원리

unique 제약은 같은 컬럼(또는 컬럼 조합)에 같은 값을 가진 행이 여럿 있는 것을 막지만 일부 값이 NULL인 것은 허용함

unique 제약을 걸면 Oracle은 unique 키에 인덱스를 암시적으로 만듦
unique나 primary key 제약을 활성화할 때 키에 인덱스가 없으면 unique 인덱스를 만들고, 인덱스 이름은 제약 이름과 같음

check 제약은 각 행이 조건을 TRUE 또는 UNKNOWN(NULL 때문에)으로 만들면 만족함
그래서 NULL은 check 제약에 걸리지 않음

ON DELETE 절이 없으면 자식 행이 있는 부모 키 값은 지울 수 없음
CASCADE는 자식 행을 함께 지우고, SET NULL은 자식의 외래 키 값을 NULL로 바꿈

ENABLE VALIDATE(기본)는 기존 데이터와 새 데이터 모두를 보장함
ENABLE NOVALIDATE는 새 DML만 검사하고 기존 데이터는 보장하지 않음

제약 이름을 주지 않으면 SYS_Cn 형태의 시스템 이름이 붙음

#필수 명령어 또는 SQL

#PRIMARY KEY

CREATE TABLE emp50
( empno NUMBER(10) CONSTRAINT emp50_empno_pk PRIMARY KEY,
  ename VARCHAR2(20),
  sal   NUMBER(10) );

ALTER TABLE emp ADD CONSTRAINT emp_empno_pk PRIMARY KEY (empno);

결과 해석:

실습에서는 같은 사원번호와 NULL 사원번호를 넣어 둘 다 거부되는 것을 확인했음

#UNIQUE, 제약 확인과 삭제

ALTER TABLE emp ADD CONSTRAINT emp_empno_un UNIQUE (empno);
ALTER TABLE dept ADD CONSTRAINT dept_loc_un UNIQUE (loc);

SELECT constraint_name, constraint_type, search_condition
  FROM user_constraints
 WHERE table_name = 'EMP';

SELECT index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'EMP';

ALTER TABLE dept DROP CONSTRAINT dept_loc_un;

결과 해석:

실습에서는 unique 제약을 건 empno에 NULL 행 두 개를 넣어 보게 했음
unique 제약만 보면 공식 문서 규칙대로 둘 다 들어가야 하지만, 공통 조건의 재생성 스크립트(SELF JOIN)는 emp.empno를 NOT NULL로 만들므로 이 테이블에서는 NOT NULL 제약 때문에 NULL 입력이 거부됨
unique의 NULL 허용을 확인하려면 NULL을 허용하는 컬럼이 있는 별도 테이블에서 해야 함
실습의 제약 삭제 예에 나온 시스템 제약 이름(SYS_C 번호)은 환경마다 다름

실습 업무 예: 기반 테이블에서 구조만 본떠 제약을 건 새 테이블을 만들고, 데이터를 옮기기 전에 중복(GROUP BY ... HAVING COUNT(*) >= 2)과 NULL(WHERE empno IS NULL)을 먼저 확인한 뒤 INSERT SELECT로 이행했음

#CHECK, NOT NULL

ALTER TABLE emp ADD CONSTRAINT emp_sal_ck CHECK (sal BETWEEN 0 AND 9000);
ALTER TABLE emp ADD CONSTRAINT emp_deptno_ck CHECK (deptno IN (10, 20, 30));
ALTER TABLE emp19 ADD CONSTRAINT emp19_email_ck CHECK (email LIKE '%@%.%');

ALTER TABLE emp MODIFY ename CONSTRAINT emp_ename_nn NOT NULL;
ALTER TABLE emp DROP CONSTRAINT emp_ename_nn;

결과 해석: 개별 실행 출력은 기재 없음

#제약 중지와 재활성화

실습 상황: 사장 KING은 월급을 9500으로 받아야 하는데 emp_sal_ck(0~9000)에 걸림

ALTER TABLE emp DISABLE CONSTRAINT emp_sal_ck;
UPDATE emp SET sal = 9500 WHERE ename = 'KING';
ALTER TABLE emp ENABLE NOVALIDATE CONSTRAINT emp_sal_ck;

결과 해석: KING의 9500은 남아 있고, 이후 새로 들어오거나 바뀌는 값은 다시 검사됨

#FOREIGN KEY와 ON DELETE

ALTER TABLE dept ADD CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno);
ALTER TABLE emp ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
  REFERENCES dept (deptno);

결과 해석: dept에 없는 부서번호 70을 emp에 넣으면 거부됨
dept에 70을 먼저 넣으면 들어감

ALTER TABLE dept DROP CONSTRAINT dept_deptno_pk CASCADE;

결과 해석:

설명: 부모 키를 지우면서 그 키를 참조하는 자식 외래 키 제약까지 함께 지움(제약만 지우고 데이터는 남음)

이 상태에서는 dept에 기본 키가 없어 아래 외래 키를 만들 수 없으므로, 부모 키를 먼저 다시 만듦

ALTER TABLE dept ADD CONSTRAINT dept_deptno_pk PRIMARY KEY (deptno);

ALTER TABLE emp ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
  REFERENCES dept (deptno) ON DELETE CASCADE;

DELETE FROM dept WHERE deptno = 20;

결과 해석: ON DELETE CASCADE면 20번 부서의 사원도 함께 지워지고, ON DELETE SET NULL이면 그 사원들의 deptno가 NULL이 됨
실습에서는 SET NULL 실습을 ROLLBACK으로 되돌렸음
연습 문제 설명은 10번을 지우라고 했지만 코드는 20번을 지움

#제약 검사를 COMMIT까지 미루기

설명: 부모(dept)에 50번이 먼저 들어가야 자식(emp)에 50번을 넣을 수 있음
데이터 이행 중에는 자식이 먼저 들어올 수 있어 실패하므로, 제약 검사를 INSERT 때가 아니라 COMMIT 때로 미룸
제약을 DEFERRABLE INITIALLY DEFERRED로 거는 형태로 보이나, 이 키워드의 공식 문서 문장은 확인 불가

ALTER TABLE emp ADD CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
  REFERENCES dept (deptno) DEFERRABLE INITIALLY DEFERRED;

결과 해석:

실습 기록: emp와 dept에 모두 50번이 있으므로 COMMIT 때 오류가 나지 않았음