OracleSQL - 무결성 제약조건(Constraint)

데이터베이스의 무결성을 보장하기 위해서는 Oracle DBMS에서는 제약조건에 대한 기능을 제공해야 한다. 이제 그 기능을 하는 키워드들에 대해서 알아보도록 하겠다.

 

PRIMARY KEY(PK)

PRIMARY KEY 키워드는 테이블에 대한 기본키를 생성한다. 기본키는 각 행을 유일하게 식별하는 컬럼 또는 컬럼의 집합이다. 그렇기 때문에 기본키는 중복될 수 없고 테이블 당 최대 하나만 존재해야한다. 그렇다고 기본키가 반드시 하나의 컬럼으로만 구성되는 것은 아니다. 여러 컬럼이 합쳐져 기본키가 될 수 있다.

 

기본키는 NULL 일 수 없고, 이미 테이블에 존재하고 있는 데이터를 다시 입력할 수 없도록 처리한다. 내부적으로 UNIQUE INDEX가 자동으로 생성된다. (오라클에서 자체적으로 만든다)

 

기본 형식 및 구조는 다음과 같다.

 

--> [] 안 부분은 생략이 가능한 부분

1. 컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] PRIMARY KEY [ (컬럼명, ...) ]

2. 테이블 레벨의 형식
컬럼명 데이터 타입,
컬럼명 데이터 타입,
CONSTRAINT CONSTRAINT명 PRIMARY KEY(컬럼명, ...)

CONSTRAINT 추가 시 CONSTRAINT 명을 생략하는 경우 오라클 서버가 자동적으로 CONSTRAINT 명을 부여한다. 일반적으로 CONSTRAINT 명은 "테이블명_컬럼명_CONSTRAINT이니셜" 형식으로 기술한다.

 

PRIMARY KEY 제약조건 설정의 예는 다음과 같다.

 

1. 컬럼 레벨의 형식

CREATE TABLE TBL_TEST
( COL1    NUMBER(5)        PRIMARY KEY
, COL2    VARCHAR2(30)
);


2. 테이블 레벨의 형식

CREATE TABLE TBL_TEST1
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST1_COL1_PK PRIMARY KEY(COL1)    -- 제약조건 설정
);


3. 다중 컬럼 PK 지정

CREATE TABLE TBL_TEST1
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, CONSTRAINT TEST1_COL1_PK PRIMARY KEY(COL1, COL2) 
);


4. 테이블 생성 이후 제약조건 추가 설정

CREATE TABLE TBL_TEST
( COL1    NUMBER(5)
, COL2    VARCHAR2(30)
);

-- 제약조건 추가
ALTER TABLE TBL_TEST
ADD CONSTRAINT TEST_COL1_PK PRIMARY KEY(COL1);

--> ※ 이미 만들어져(생성되어) 있는 테이블에 부여하려는 제약조건을 위반한 데이터가 포함되어 있을 경우
         해당 테이블에 제걍조건을 추가하는 것은 불가능하다.

 

UNIQUE(UK : U)

UNIQUE 키워드는 테이블에서 지정한 컬럼의 데이터가 중복되지 안하고 유일할 수 있도록 설정하는 제약조건 기능을 한다. PRIMARY KEY와 유사한 제약조건이지만, NULL 을 허용한다는 차이점이 있다. PRIMARY KEY와 마찬가지로 내부적으로 UNIQUE INDEX가 자동으로 생성된다. 하나의 테이블 내에서 UNIQUE 제약조건을 여러 번 설정하는 것이 가능하다. 즉, 하나의 테이블에 UNIQUE 제약조건을 여러 개 만드는 것이 가능하다는 것이다. (PRIMARY KEY와 다르게 개수 제한이 없다)

 

기본 형식 및 구조는 다음과 같다.

 

1. 컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] UNIQUE 

2. 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 UNIQUE(컬럼명, ...)

UNIQUE 제약조건 설정의 예는 다음과 같다.

 

1. 컬럼 레벨

CREATE TABLE TBL_TEST
( COL1    NUMBER(5)        PRIMARY KEY
, COL2    VARCHAR2(30)    UNIQUE
);


2. 테이블 레벨

CREATE TABLE TBL_TEST
( COL1    NUMBER(5)        
, COL2    VARCHAR2(30)    
, CONSTRAINT TEST_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST_COL2_UK UNIQUE(COL2)
);


3. 테이블 생성 후 제약조건 추가
 
CREATE TABLE TBL_TEST
( COL1    NUMBER(5)        
, COL2    VARCHAR2(30)    
);

-- 제약조건 추가
ALTER TABLE TBL_TEST
ADD ( CONSTRAINT TEST_COL1_PK PRIMARY KEY(COL1)
       , CONSTRAINT TEST_COL2_UK UNIQUE(COL2) ); 

 

CHECK(CK : C)

CHECK 키워드는 컬럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건 기능을 한다. 컬럼에서 입력되는 데이터를 검사하여 조건에 맞는 데이터만 입력될 수 있도록 한다. 또한, 컬럼에서 수정되는 테이터를 검사하여 조건에 맞는 데이터로 수정되는 것만 허용하는 기능을 수행하게 된다.

 

기본 형식 및 구조는 다음과 같다.

 

1. 컬럼 레벨의 형식
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] CHECK(컬럼 조건)

2. 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 CHECK(컬럼 조건)

CHECK 제약조건 설정의 예는 다음과 같다.

 

1. 컬럼 레벨

CREATE TABLE TBL_TEST
( COL1    NUMBER(5)         PRIMARY KEY
, COL2    VARCHAR2(30)
, COL3    NUMBER(3)         CHECK(COL3 BETWEEN 0 AND 100)  -- 컬럼 데이터 제한 설정 (0 ~ 100)
);


2. 테이블 레벨

CREATE TABLE TBL_TEST
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, COL3 NUMBER(3)
, CONSTRAINT TEST_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST_COL3_CK CHECK(COL3 BETWEEN 0 AND 100)
);


3. 테이블 생성 이후 제약조건 추가

CREATE TABLE TBL_TEST
( COL1 NUMBER(5)
, COL2 VARCHAR2(30)
, COL3 NUMBER(3)
);

-- 제약조건 추가
ALTER TABLE TBL_TEST
ADD ( CONSTRAINT TEST_COL1_PK PRIMARY KEY(COL1)
       , CONSTRAINT TEST_COL3_CK CHECK(COL3 BETWEEN 0 AND 100) );

 

FOREIGN KEY(FK : F : R)

FOREIGN KEY (참조키 또는 외래키)는 두 테이블의 데이터 간 연결을 설정하고 강제 적용시키는데 사용되는 열이다. 한 테이블의 기본 키 값이 있는 열을 다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다. 이 때, 두 번째 테이블에 추가되는 열이 외래키가 된다.

 

외래키 설정을 위해서는 부모 테이블(참조 받는 컬럼이 포한된 테이블)이 먼저 생성된 후, 자식 테이블(참조 하는 컬럼이 포함된 테이블)이 생성되어야 한다. 이 때, 자식 테이블에 FOREIGN KEY 제약조건이 설정된다.

 

기본 형식 및 구조는 다음과 같다.

 

1. 컬럼 레벨
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명]
                        REFERENCES 참조테이블명(참조컬럼명)
                        [ON DELETE CASCADE | ON DELETE SET NULL]   --> 추가 옵션 (위험한 옵션)
                        (위험한 이유는 부모 테이블의 제약조건을 무시하고 막 지울 수 있기 때문)

2. 테이블 레벨
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 FOREIGN KEY(컬럼명)
                   REFERENCES 참조테이블명(참조컬럼명)
                   [ON DELETE CASCADE | ON DELETE SET NULL]   --> 추가 옵션

--> ※ FOREIGN KEY 제약조건 설정을 진행하기 위해서는 부모 테이블의 생성 작업을 먼저 수행해야 한다.
         그리고 이 때, 부모 테이블에는 반드시 PRIMARY KEY 또는 UNIQUE KEY 제약조건이 설정된 컬럼이 
         존재해야 한다.

FOREIGN KEY 제약조건 사용 예는 다음과 같다.

 

-- 부모 테이블 생성

CREATE TABLE TBL_JOBS
( JIKWI_ID         NUMBER
, JIKWI_NAME    VARCHAR2(30)
, CONSTRAINT JOBS_ID_PK PRIMARY KEY(JIKWI_ID)
);


1. 컬럼 레벨

CREATE TABLE TBL_EMP
( SID        NUMBER        PRIMARY KEY
, NAME     VARCHAR2(30)
, JIKWI_ID  NUMBER        REFERENCES TBL_JOBS(JIKWI_ID)    -- 참조키 생성
);


2. 테이블 레벨

CREATE TABLE TBL_EMP
( SID NUMBER
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER
, CONSTRAINT EMP_SID_PK PRIMARY KEY(SID)
, CONSTRAINT EMP_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID) REFERENCES TBL_JOBS(JIKWI_ID)
);


3. 테이블 생성 이후 제약조건 추가

CREATE TABLE TBL_EMP3
( SID NUMBER
, NAME VARCHAR2(30)
, JIKWI_ID NUMBER
);

-- 제약조건 추가
ALTER TABLE TBL_EMP 
ADD ( CONSTRAINT EMP_SID_PK PRIMARY KEY(SID)
       , CONSTRAINT EMP_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
                           REFERENCES TBL_JOBS(JIKWI_ID) );

만약 부모 테이블의 데이터를 자유롭게(?) 삭제하기 위해서는 "ON DELETE CASCADE" 옵션 지정이 필요하다. 

 

-- ON DELETE CASCADE 옵션이 포함된 내용으로 제약조건 다시 지정

ALTER TABLE TBL_EMP
ADD CONSTRAINT EMP_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID)
                          REFERENCES TBL_JOBS(JIKWI_ID)
                          ON DELETE CASCADE;

CASCADE 옵션을 지정한 후에는 참조받고 있는 부모 테이블의 데이터를 언제든지 자유롭게 삭제하는 것이 가능하다. 단 부모 테이블의 데이터가 삭제될 경우 해당 데이터만 삭제되는 것이 아니라 이를 참조하는 자식 테이블의 데이터도 모두 함께 삭제된다.

 

하지만 부모 테이블을 삭제하는 경우 CASCADE 옵션이 설정되어 있다고 하더라도 자식 테이블에 참조하는 데이터가 남아있는 경우 부모 테이블의 삭제가 불가능하다.

 

NOT NULL(NN : CK : C)

NOT NULL 키워드는 테이블에서 지정한 컬럼의 데이터가 NULL인 상태를 갖지 못하도록 하는 제약조건 기능을 한다.

 

기본 형식 및 구조는 다음과 같다.

 

1. 컬럼 레벨
컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] NOT NULL

2. 테이블 레벨의 형식
컬럼명 데이터타입,
컬럼명 데이터타입,
CONSTRAINT CONSTRAINT명 CHECK(컬럼명 IS NOT NULL)    --> 컬럼 레벨과 형식이 다른점 주의! 

기존에 생성되어 있던 테이블에 NOT NULL 제약조건을 추가할 경우 ADD 보다 MODIFY 절이 더 많이 사용된다. 또한 기존 테이블에 데이터가 들어있지 않은 컬럼(NULL 인 상태의 컬럼)을 NOT NULL 제약조건을 갖게끔 수정하는 경우에는 에러가 발생한다.

 

NOT NULL 제약조건의 사용 예는 다음과 같다.

 

1. 컬럼 레벨

CREATE TABLE TBL_TEST
( COL1    NUMBER(5)        CONSTRAINT TEST_COL1_PK PRIMARY KEY    -- 제약조건명 생략 없이 사용한 경우
, COL2    VARCHAR2(30)    CONSTRAINT TEST_COL2_NN NOT NULL
);


2. 테이블 레벨의 형식

CREATE TABLE TBL_TEST
( COL1     NUMBER(5)
, COL2     VARCHAR2(30)
, CONSTRAINT TEST_COL1_PK PRIMARY KEY(COL1)
, CONSTRAINT TEST_COL2_NN CHECK(COL2 IS NOT NULL)
);


3. 테이블 생성 이후 제약조건 추가 

CREATE TABLE TBL_TEST
( COL1    NUMBER(5)
, COL2    VARCHAR2(30)
);

-- 제약조건 추가
ALTER TABLE TBL_TEST
ADD ( CONSTRAINT TEST_COL1_PK PRIMARY KEY(COL1)
       , CONSTRAINT TEST_COL2_NN CHECK(COL2 IS NOT NULL) );

-- 다음과 같은 방법을 사용하는 것도 가능하다.
ALTER TABLE TBL_TEST
MODIFY COL2 NOT NULL;

 

DEFAULT

DEFAULT 키워드는 INSERT 와 UDPATE 문에서 특정 값이 아닌 기본 값을 입력하도록 할 수 있는 기능을 한다. 

 

기본 형식 및 구조는 다음과 같다.

 

컬럼명 데이터타입 DEFAULT 기본값

-- 테이블 생성 이후 DEFAULT 표현식 추가 / 변경
ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT 기본값;

-- 기존의 DEFAULT 표현식 제거
ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT NULL;

INSERT  명령 시 해당 컬럼에 입력될 값을 할당하지 않거나 DEFAULT 키워드를 활용하여 기본으로 설정된 값을 입력하도록 할 수 있다. 

 

만약 DEFAULT 키워드와 다른 제약(NOT NULL 등) 표기가 함께 사용되어야 하는 경우에 DEFAULT 키워드를 먼저 표기(작성)할 것을 권장한다.

 

CREATE TABLE TBL_BBS
( SID            NUMBER         PRIMARY KEY
, NAME         VARCHAR2(20) 
, CONTENTS  VARCHAR2(200)
, WRITEDAY   DATE              DEFAULT SYSDATE    -- 기본 값으로 현재 날짜 설정
, COUNTS      NUMBER         DEFAULT 0              -- 기본 값으로 0 설정
, COMMENTS NUMBER         DEFAULT 0              -- 기본 값으로 0 설정
);