[Oracle] 무결성 제약 조건 생성 및 조회

데이터 무결성 제약

데이터의 정확성과 일관성을 보장한다.

 

무결성 제약조건 종류

무결성 제약조건	설명
NOT NULL	해당 칼럼 값은 NULL을 포함할 수 없다.
고유 키(Unique Key)	테이블내에서 해당 칼럼 값은 유일해야한다. 단, NULL 값은 허용함.
기본 키(Primary Key)	해당 칼럼 값은 반드시 존재해야하며, 유일해야 한다. 고유키, NOT NULL 제약조건을 결합한 형태.
참조	해당 칼럼 값은 참조되는 테이블의 칼럼 값중의 하나와 일치하거나 NULL을 가진다.
CHECK	해당 칼럼에 저장 가능한 데이터 값의 범위나 조건을 지정한다.

 

student 테이블

기본 키 : 학번(studno)

• 칼럼에는 값이 반드시 존재해야 함.
• 모든 값은 유일해야 함.

고유 키 : 사용자아이디(userid), 주민등록번호(idnum)

• 칼럼 값은 유일해야함.

NOT NULL : 이름(name)

• name 칼럼 값은 반드시 존재해야한다.

CHECK : 학년(grade)

• 학년은 1, 2, 3, 4 중 하나여야 한다.

참조 : 학과(deptno)

• 학생 테이블의 deptno는 부서 테이블의 deptno 칼럼의 값중 하나와 일치해야한다.

 

NOT NULL 무결성 제약조건

• 해당 칼럼이 NULL 값은 가질 수 없다는 것을 정의.
• 해당 칼럼에는 값을 반드시 입력해야하고, 생략하면 안된다.
• 기본 값을 NULL로 수정하는 경우에도 오류 발생.
• 학번(기본키), 이름(NOT NULL)은 NULL 입력이 불가능하다.
  • 학생 테이블에 데이터를 입력할때
  • 학번과 이름이 NULL이 아니라면 -> 입력 성공!
  • 학과 또는 이름이 NULL이라면 -> 입력 실패.

 

고유 키 무결성 제약조건

• 테이블 내 해당 칼럼은 동일한 값을 가질 수 없음을 정의.
• NULL은 고유 키 제약조건에 위반되지 않으니 입력 가능하다.
• 사용자 아이디(userid), 주민등록번호(idnum)은 고유 키 조건으로 값이 유일해야한다.
  • 학생 테이블에 데이터를 입력할때
  • userid, idnum이 중복되지 않고 입력 -> 입력 성공!
  • userid를 중복되지 않게 입력하고 idnum에는 NULL을 입력 -> 입력 성공!
  • userid 또는 idnum을 중복되는 값으로 입력 -> 입력 실패.

 

기본 키 무결성 제약조건

• 하나 이상의 칼럼에 의해 테이블의 모든 행을 구별 할 수 있는 식별자를 정의하는것이다.
• 고유 키 제약조건과 NOT NULL 제약조건을 결합한 개념이다.
• 학번(studno)는 기본 키로 칼럼의 값이 유일해야하고 NULL을 허용하지 않는다.
  • 학생 테이블에 데이터를 입력할때
  • 학번이 겹치지 않고 NULL이 아니라면 입력 성공!
  • 학번(studno)가 NULL이라면 입력 실패.
  • 학번이 이미 존재한다면 입력 실패.

 

참조 무결성 제약조건

• 칼럼 값이 다른 테이블의 칼럼 값 중에 하나와 일치시키기 위한 제약 조건이다.
• 학생 테이블의 학과(deptno)는 학과 테이블의 학과(deptno)와 일치하거나 NULL이여야 한다.
  • 학생 테이블에 입력할때.
  • 학과 deptno가 학과 테이블에 존재한다면 입력 성공!
  • 학과 deptno가 NULL이라면 입력 성공!
  • 학생 테이블에서 학과를 입력할때 학과 테이블에 없는 학과를 입력한다면 입력 실패한다.

 

갱신과 삭제에서 허용되는 DML 명령문

DML 명령문	부모 테이블	자식 테이블
INSERT	참조 키 값이 고유한 경우에만 가능	외래 키 값이 참조 키 값중 하나와 일치하거나 NULL인 경우에 가능
UPDATE	참조 키 값을 참조하는 테이블의 칼럼 값이 없는 경우에만 가능	수정되는 외래 키 값이 참조 키 값 중의 하나와 일치하면 가능
DELETE RESTRICT	참조 키 값을 참조하는 자식 테이블의 칼럼 값이 없는 경우에 삭제가 가능하다.	항상 가능.
DELETE CASCADE	항상 가능.	항상 가능.

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

 

CHECK 무결성 제약 조건

• 칼럼에 입력 가능한 데이터의 범위나 조건을 지정
• 잘못된 데이터의 입력과 수정을 방지한다.
• 하나의 칼럼에 여러개의 CHECK 조건을 지정 가능하다.
• 가상 칼럼(CURRVAL, NEXTVAL)과 SYSDATE, USER 사용 불가
• 데이터를 입력할때 학년 칼럼에 CHECK 조건으로 1, 2, 3, 4를 걸었다면
  
  • 학년에 1을 쓰면 -> 입력 성공
  • 학년이 NULL이라면 -> 입력 성공
  • 학년에 6을 쓰면 -> 입력 실패

 

EX) grade 칼럼에 1, 2, 3, 4중 하나만 입력

CREATE TABLE test(
grade VARCHAR2(1),
CONSTRAINT test_grade_ck
    CHECK (grade IN ('1', '2', '3', '4'))
);

INSERT INTO test
VALUES ('1');

INSERT INTO test
VALUES (NULL);

-- 5 입력시 에러 발생
INSERT INTO test
VALUES ('5');

CHECK 조건이 있더라도 NULL은 위배되지 않는다.

단, 그 외 데이터를 입력시 조건에 맞춰야함.

 

무결성 제약조건 생성 방법

• 테이블 생성과 동시에 정의가 가능하다.
• 테이블을 생성한 이후에 추가, 삭제 가능하다.
• 제약조건명을 지정하지 않는다면 조회시 SYS_Cn 형태로 자동 생성되어 파악이 힘듦

 

무결성 제약조건 키워드

• ON DELETE CASCADE
  • 부모 테이블에서 외래 키가 참조하는 기본 키나 고유 키를 포함한 행을 삭제할 때 자식 테이블의 외래 키를 포함하는 행도 함께 삭제
• NOT DEFERRABLE
  • 하나의 DML 명령문이 처리될 때마다 제약조건 위반 여부를 검사
• DEFERRABLE
  • 트랜재션내의 모든 DML 명령문에 대한 제약조건 검사를 트랜잭션 종료시까지 연기
• INITIALLY IMMEDIATE
  • DML 명령문이 종료될 때마다 제약조건 검사
• INITIALLY DEFERRED
  • 트랜잭션이 끝날때만 제약조건 검사

 

무결성 제약조건의 검사 시점

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

• DML 명령문을 실행할 때마다 무결성 제약조건의 위반 여부를 검사한다.
• 제약조건을 위반한 경우 DML 명령문을 ROLLBACAK 시키고 오류 메시지 출력
• 'NOT DEFERRED', 'DEFERRABLE INITIALLY IMMEDATE'로 지정된 경우에 해당

 

- 지연 제약조건(deferred constraint) 검사

• 트랜잭션의 COMMIT 시점에 무결성 제약조건의 위반 여부를 검사한다.
• 트랜잭션 내 제약 조건을 위반한 DML 명령문이 있다면 해당 트랜잭션 모두를 ROLLBACK 시킨다.
• 'DEFERRABLE INITIALLY DEFERRED'로 지정된 경우에 해당

 

테이블 생성 예

1) 강좌(subject) 테이블 인스턴스

칼럼 이름	데이터 타입	Key Type	NN / Unique	FK table	FK column	설명
subno	NUMBER(5)	PK	NN, U			강좌 번호
subname	VARCHAR2(20)		NN			강좌 이름
term	VARCHAR2(1)					학기( '1', '2'만 입력)
type	VARCHAR2(4)					필수/선택 구분

CREATE TABLE subject(
subno NUMBER(5)
    CONSTRAINT subject_no_pk PRIMARY KEY
    DEFERRABLE INITIALLY DEFERRED,
subname VARCHAR2(20)
    CONSTRAINT subject_name_nn NOT NULL,
term VARCHAR2(1)
    CONSTRAINT subject_term_ck CHECK (term IN ('1', '2')),
type VARCHAR2(4)
);

 

2) 수강(sugang) 테이블 인스턴스

칼럼 이름	데이터 타입	Key Type	NN / Unique	FK table	FK column	설명
studno	NUMBER(5)	PK1, FK	NN, U	student	studno	학번
subno	NUMBER(5)	PK2, FK	NN, U	subject	subno	강좌 번호
regdate	DATE					등록일
result	NUMBER(3)					평가 결과

CREATE TABLE sugang(
studno NUMBER(5)
    CONSTRAINT sugang_studno_fk REFERENCES student(studno),
subno NUMBER(5)
    CONSTRAINT sugang_subno_fk REFERENCES subject(subno),
regdate DATE,
result NUMBER(3),
    CONSTRAINT sugang_pk PRIMARY KEY(studno, subno)
);

제약 조건이 하나 이상의 칼럼을 참조하거나 지정할때 테이블 레벨로 생성한다.

 

1. 칼럼 레벨 제약 조건

• sugang_studno_fk 제약조건
• sugang_subno_fk 제약조건

2. 테이블 레벨 제약 조건

• sugang_pk 제약조건

 

무결성 제약조건 조회

• USER_CONSTRAINTS
  • 제약조건이 설정된 테이블 이름, 무결성 제약조건의 이름, 종류 및 활성화 상태정보 조회
• USER_CONS_COLUMNS
  • 제약조건이 설정된 칼럼 이름을 조회

 

USER_CONSTRAINTS 데이터 딕셔너리에서 무결성 제약조건 조회

SELECT table_name, constraint_name, constraint_type
FROM user_constraints
WHERE table_name IN ('SUBJECT', 'SUGANG');

C: CHECK 또는 NOT NULL

P: PRIMARY KEY

U: UNIQUE KEY

R: FOREGIN KEY

 

USER_CONS_COLUMNS

SELECT table_name, column_name, constraint_name
FROM user_cons_columns
WHERE table_name IN ('SUBJECT', 'SUGANG');