오라클 데이터 무결성 제약조건

12. 데이터 무결성 제약조건

데이터 무결성 제약조건은 데이터의 정확성과 일관성을 보장하기 위해 테이블 생성시 각 칼럼에 대해 정의하는 규칙을 말한다.

데이터 무결성 제약조건의 종류

오라클에서 지원하는 데이터무결성제약조건은 기본키,참조,고유키,NOT NULL, CHECK 무결성 제약조건이 있다.

NOT NULL : 해당 칼럼 값은 NULL을 포함할 수 없음

고유 키 : 테이블 내에서 해당 칼럼 값은 항상 유일해야 함

기본키 : 해당 칼럼 값은 반드시 존재해야 하며, 유일해야 함. UNIQUE, NOT NULL 제약조건을 결합한 형태

참조 : 해당 칼럼 값은 참조되는 테이블의 칼럼 값 중의 하나와 일치하거나 NULL을 가짐

CHECK : 해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정

고유키 무결성 제약조건

한 테이블 내에서 칼럼이 동일한 값을 가질 수 없음을 정의하는 제약조건이다.

참조 무결성 제약조건

한 테이블의 칼럼 값이 자신 또는 다른 테이블의 칼럼 값중의 하나와 일치시키기 위한 제약조건이다. 참조 무결성 제약조건에서 다른 테이블의 칼럼 값을 참조하는 테이블을 자식테이블이라 하고, 부모 테이블의 칼럼 값을 참조하는 자식 테이블의 칼럼을 외래키(foreign key)라 한다. 그리고 다른 테이블에 의해 참조되는 테이블을 부모 테이블 이라 하고, 자식 테이블에서 참조하는 부모 테이블의 칼럼을 참조키(reference key)라 한다.

그리고 외래키는 부모 테이블의 기본키나 고유 키만 참조할 수 있다. 그 이유는 부모 테이블에서 중복된 칼럼 값을 자식테이블에서 참조하면 애매모호성이 발생할 수 있기 떄문이다.그리고 자식 테이블의 외래 키가 참조하는 부모 테이블의 칼럼 값은 삭제할 수 없다. 만일 부모 테이블의 데이터가 삭제될 경우 자식 테이블의 외래 키는 참조 대상이 없어지기 때문이다. 하지만 ON DELETE CASCADE 옵션을 이용하면 부모 테이블의 행이 삭제될 때 이를 참조한 자식 테이블의 행을 동시에 삭제할 수 있다.

CHECK 무결설 제약조건

CHECK 무결성 제약조건은 칼럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건이다. 하나의 칼럼에 대하여 여러개의 CHECK 무결성 제약조건을 지정할 수 있다.

(CURRVAL, NEXTVAL과 같은 가상 칼럼이나 SYSDATE, USER와 같은 함수는 사용할 수 없고 동일 테이블의 칼럼에서 다른 행을 참조할 수 있다)

(CHECK 무결성 제약조건은 데이터의 입력이나 수정시 실수로 부정확한 값을 입력하는 것을 예방할 수 있는 유용한 기능이다. 예를들어 학생 테이블의 학년 값을 1~4학년중 하나만 허용하는 CHECK 무결성 제약조건을 정의하면 실수로 잘못된 학년값이 입력되는것을 방지할 수 있다)

무결성 제약조건 생성

ON DELETE CASCADE : 부모 테이블에서 외래 키가 참조하는 참조 키를 포함한 행을 삭제할 경우 자섹 테이블의 외래 키를 포함하는 행도 함께 삭제

USING INDEX : 기본 키나 고유 키 무결성 제약조건 생성시 묵시적으로 생성되는 인덱스에 대한 스토리지 파라미터 정의

NOT DEFERRABLE : 하나의 DML 명령문이 처리될 때마다 제약조건 위반 여부를 검사, defer_spec을 명시하지 않을 경우에는 기본값

DEFERRABLE : 트랜잭션내의 모든 DML 명령문에 대한 제약조건 검사를 트랜잭션 종료시까지 연기

INITIALLY IMMEDIATE : DML 명령문이 종료될 때마다 제약조건 검사.

INITIALLY DEFERRED : 트랜잭션이 종료될 때만 제약조건 검사.

기존 테이블에 무결성 제약조건 추가방법

무결성 제약조건은 기존 테이블에 대해서도 추가로 생성할 수 있다. ALTER TABLE… ADD CONSTRAINT 명령문은 기존 테이블에 제약조건을 추가하기 위한 명령문이다. 하지만 ,NOT NULL 무결성 제약조건의 추가는 NULL허용 상태를 NULL 입력불가 상태로 변경하는 것을 의미하므로 ALTER TABLE… MODIFY명령문을 사용해야 한다.

사용법

ALTER TABLE table ADD [CONSTRAINT constraint_name] constraint_type (column);

기본키 제약조건 추가

ALTER TABLE student ADD CONSTRAINT stud_userid_pk PRIMARY KEY(studno);

고유키 제약조건 추가

ALTER TABLE student ADD CONSTRAINT stud_idnum_uk UNIQUE(idnum);

NOT NULL 제약조건 추가

ALTER TABLE student MODIFY (name CONSTRAINT stud_name_nn NOT NULL);

외래키 제약조건 추가

ALTER TABLE professor ADD CONSTRAINT prof_deptno_fk FOREIGN KEY(deptno) REFERENCES department(deptno);

무결성 제약조건 삭제

ALTER TABLE… DROP CONSTRAINT 명령문은 무결성제약조건을 삭제하기 위한 명령문이다. 부모 테이블의 기본키 무결성 제약조건을 삭제하는 경우에는 참조 무결성 제약조건을 먼저 삭제하거나 CASCADE 옵션을 사용하여 동시에 삭제하면 된다.

사용법

ALTER TABLE table DROP CONSTRAINT constraint_name [CASCADE];

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

강좌 테이블의 subject_term_ck 무결성 제약조건을 삭제

ALTER TABLE subject DROP CONSTRAINT subject_term_ck;

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

실무에서 대용량의 초기 데이터를 입력할 때 무결성 제약조건의 위반 여부를 검사하는 과정으로 인해 처리 시간이 오래 걸리는 경우가 발생할 수 있다. 이러한 경우에는 기존의 무결성 제약조건을 일시적으로 비활성화하여 데이터를 입력한 다음 비활성화된 무결성 제약조건은 다시 활성화하여 데이터 입력 시간을 단축할 수 있다. 무결성 제약조건을 활성화 또는 비활성화하기 위하여 ALTER TABLE 명령문에서 ENABLE 또는 DISABLE 절을 사용한다.

제약조건의 비활성화 사용법

ALTER TABLE table DISABLE CONSTRAINT constraint_name [cascade];

수강테이블의 sugang_pk 무결성 제약조건을 비활성화 하여라

ALTER TABLE sugang DISABLE CONSTRAINT sugang_pk;

제약조건의 활성화

ALTER TABLE table ENABLE [ NOVALIDATE] CONSTRAINT constraint_name [cascade];

수강테이블의 sugang_pk 무결성 제약조건을 활성화 하여라

ALTER TABLE sugang ENABLE CONSTRAINT sugang_pk;

무결성 제약조건 데이터 딕셔너리

사용자에 의해 정의된 무결성 제약조건을 조회하기 위한 데이터 딕셔너리 뷰는 USER_CONSTRAINTS, USER_CONS_COLUMNS 등이 있다. USER_CONSTRAINTS 는 제약조건이 정의된 테이블 이름, 무결성 제약조건 이름, 무결성 제약조건 종류 및 활성화 상태에 관한 정보를 저장한다. USER_CONS_COLUMNS는 제약조건이 정의된 칼럼과 제약조건 이름을 저장한다.

student, professor, department테이블에 정의된 모든 종류의 무겱성 제약조건을 조회하여라

SELECT table_name, constraint_name, constraint_type, status

FROM user_constraints

WHERE table_name IN (‘STUDENT’, ‘PROFESSOR’, ‘DEPARTMENT’);