3.2 테이블 제약조건

오라클클럽 : http://www.oracleclub.com/lecture/1013

테이블 제약조건
제약조건(constraint)
-테이블에 부적절한 자료가 입력되는 것을 방지 ->여러가지 규칙 적용
-사용자가 지정
-모든 제약조건 -> 데이터 사전(DICTINARY)에 저장
-의미있는 이름 부여 -> CONSTRAINT 쉽게 참조
-표준 객체 명명법
-제약조건 생성/생성후(ALTER) 추가 가능
-NOT NULL 컬럼 레벨에서만 정의

 

NOT NULL
-컬럼 필수
CREATE TABLE emp3 (
ename VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL);

-제약조건 확인 USER_CONSTRAINTS
SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME - 'EMP3';

 

UNIQUE 조건
-데이터 유일성 보장, 자동 인덱스 생성
ALTER TABLE EMP3
ADD CONSTRAINT emp3_uk_deptno UNIQUE (deptno);

ALTER TABLE emp3
DROP CONSTRAINT emp3_uk_deptno;

 

CHECK조건
-컬럼 값 범위 제한
ALTER TABLE emp3
ADD CONSTRAINT emp2_ck_comm
CHECK (comm >=1 AND conn <= 100);

ALTER TABLE emp3
ADD CONSTRAINT emp2_ck_comm
CHECK comm IN (10000,20000,30000, 40000, 50000);

 

DEFAULT(컬럼 기본값) 지정
-데이터 입력이 없어도 기본값 입력
CREATE TABLE emp4(
..........
hiredate DATE DEFAULT SYSDATE );

 

PRIMARY KEY 지정
-UNIQUE + NOT NULL
-행을 대표하는 컬럼, 다른 테이블 외래키 참조(참조무결성)
-자동으로 인덱스(제약 조건 이름과 동일) 생성
-INDEX 검색키로 검색 속도 향상(UNIQUE,PRIMARY KEY 생성시 자동 생성)

CREATE TABLE emp5(
empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY);

ALTER TABLE emp2
ADD CONSTRAINT emp2_pk_empno PRIMARY KEY(empno);

--PRIMARY KEY 인덱스 테이블스페이스 별도 지정
ALTER_TABLE emp
ADD CONSTRAINT emp_pk PRIMARY KEY(empno)
USING INDEX TABLESPACE tablespace_name

 

FOREIGN KEY(외래 키)지정
-기본키를 참조하는 컬럼(컬럼들)
-외래 키 컬럼 데이터형 = 기본키 컬럼 데이터 형, 불일치시 참조무결성 제약에 의해 테이블 생성 불가
-외래 키에 참조되고 있는 기본키는 삭제 불가
-ON DELETE CASCADE 연산자와 정의된 외래키는 그 기본키가 삭제 될때 같이 삭제

ALTER TABLE emp2
ADD CONSTRAINT emp2_fk_deptno
FOREIGN KEY(deptno) REFERENCES dept(deptno);

 

제약 조건 확인
-UESR_CONS_COLUMNS : 컬럼에 할당된 제약 조건 조회
-USER_CONSTRAINTS : 유저가 소유한 모든 제약 조건 조회
SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
       DECODE(B.CONSTRAINT_TYPE,
       'P','PRIMARY KEY',
       'U','UNIQUE KEY',
       'C','CHECK OR NOT NULL',
       'R','FOREIGN KEY') CONSTRAINT_TYPE,
       A.CONSTRAINT_NAME
FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
WHERE A.TABLE_NAME = UPPER('&table_name')
  AND A.TABLE_NAME = B.TABLE_NAME
  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY 1;