Oracle - DDL (Data Definition Language) 제약 조건 - Part 2

제약 조건(CONSTRAINTS)

제약 조건이란 테이블 작성 시 각 컬럼에 기록될 데이터에 대해 제약조건을 설정할 수 있는데 이는 데이터 무결성 보장을 주 목적으로 한다. 입력 데이터에 문제가 없는지에 대한 검사와 데이터의 수정/삭제 가능 여부 검사 등을 위해 사용한다.

제약조건에는 NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK가 있다.

 

제약 조건 확인은 여기서 가능하다.

테이블을 클릭 후 제약 조건탭으로 가서 확인할 수 있다.

 

 

NOT NULL

NOT NULL이란 데이터에 NULL을 허용하지 않는다. 

해당 컬럼에 반드시 값이 기록되어야 하는 경우에 사용하고 특정 컬럼에 값을 저장하거나 수정할 때

NULL 값을 허용하지 않도록 컬럼 레벨에서 제한한다.

 

[ NOT NULL 예시]

CREATE TABLE USER_NOTNULL(
    USER_NO NUMBER NOT NULL,
    USER_ID VARCHAR2(20) NOT NULL,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

테이블을 생성하면 이렇게 뜬다.

 

 

UNIQUE

UNIQUE란 중복된 값을 허용하지 않는다. 

컬럼 입력 값에 대해 중복을 제한하는 제약조건으로 컬럼 레벨과 테이블 레벨에 설정이 가능하다.

 

[ UNIQUE 예시 ]

컬럼 레벨에서 설정

CREATE TABLE USER_UNIQUE(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE, -- 컬럼 레벨
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

 

테이블 레벨에서 설정

CREATE TABLE USER_UNIQUE2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    UNIQUE (USER_ID) --테이블 레벨
);

 

두개의 컬럼 제약 조건 설정

CREATE TABLE USER_UNIQUE3(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    UNIQUE (USER_NO, USER_ID) --두 개의 컬럼을 묶어 하나의 UNIQUE제약조건 설정
);

 

 

 

 

PRIMARY KEY

PRIMARY KEY란 NULL과 중복 값을 허용하지 않음(컬럼의 고유 식별자로 사용하기 위해 사용한다.)

테이블에서 한 행의 정보를 구분하기 위한 고유 식별자(Identifier)의 역할을 한다.

NOT NULL의 의미와 UNIQUE의 의미를 둘 다 가지고 있으며 한 테이블 당 하나만 설정 가능하다.

컬럼 레벨과 테이블 레벨 둘 다 지정이 가능하다.

 

[ PRIMARY KEY 예제 ]

컬럼 레벨에서 설정

CREATE TABLE USER_PRIMARYKEY( 
    USER_NO NUMBER PRIMARY KEY, -- 컬럼 레벨 설정
    USER_ID VARCHAR2(20) UNIQUE, 
    USER_PWD VARCHAR2(30) NOT NULL, 
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10), 
    PHONE VARCHAR2(30), 
    EMAIL VARCHAR2(50) 
);

 

테이블 레벨에서 설정

CREATE TABLE USER_PRIMARYKEY( 
    USER_NO NUMBER, 
    USER_ID VARCHAR2(20) UNIQUE, 
    USER_PWD VARCHAR2(30) NOT NULL, 
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10), 
    PHONE VARCHAR2(30), 
    EMAIL VARCHAR2(50),
    PRIMARY KEY (USER_NO) -- 테이블 레벨 설정
);

 

두개의 컬럼을 묶어서 하나의 PRIMARY KEY 설정

CREATE TABLE USER_PRIMARYKEY2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    PRIMARY KEY (USER_NO, USER_ID) --두 개의 컬럼을 묶어서 하나의 PRIMARY KEY 제약조건 설정
);

 

 

FOREIGN KEY

FOREIGN KEY란 참조되는 테이블의 컬럼의 값이 존재하면 허용한다.

참조 무결성을 위한 제약조건으로 참조된 다른 테이블이 제공한 값만 사용하도록 제한을 거는 제약조건이다.

참조되는 컬럼과 참조된 컬럼을 통해 테이블 간에 관계가 형성 되는데 참조되는 값은 제공되는 값 외에 NULL을 사용 가능하고 참조할 테이블의 참조할 컬럼명을 생략할 경우 PRIMARY KEY로 설정된 컬럼이 자동으로 참조할 컬럼이 된다.

 

[ FOREIGN KEY 예시 ]

컬럼 레벨에서 설정

CREATE TABLE USER_FOREIGNKEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER,
    FOREIGN KEY (GRADE_CODE) REFERENCES USER_GRADE (GRADE_CODE) -- 컬럼 레벨 설정
);

 

테이블 레벨에서 설정

CREATE TABLE USER_FOREIGNKEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER REFERENCES USER_GRADE (GRADE_CODE) -- 테이블 레벨 설정
);

 

* 부모테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤 방식으로 처리할지에 대한 내용을 제약조건 설정 시 옵션으로 지정 가능하다.

기본 삭제 옵션은 ON DELETE RESTRICTED로 지정되어 있다.

 

FOREIGN KEY 옵션(ON DELETE SET NULL) 설정

CREATE TABLE USER_FOREIGNKEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER REFERENCES USER_GRADE (GRADE_CODE) ON DELETE SET NULL
    --  ON DELETE SET NULL을 설정한다.
);

DELETE FROM USER_GRADE WHERE GRADE_CODE = 10;

* 부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 NULL로 변경된다.

 

FOREIGN KEY 옵션(ON DELETE CASCADE) 설정

CREATE TABLE USER_FOREIGNKEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER REFERENCES USER_GRADE (GRADE_CODE) ON DELETE CASCADE -- CASCADE 설정
);
DELETE FROM USER_GRADE WHERE GRADE_CODE = 10;

* 부모 테이블의 데이터 삭제 시 참조하고 있는 테이블의 컬럼 값이 존재하던 행 전체 삭제한다.

 

 

CHECK

CHECK란 저장 가능한 데이터 값의 범위나 조건을 지정하여 설정한 값만 허용한다.

해당 컬럼에 입력 되거나 수정되는 값을 체크하여 설정된 값 이외의 값이면 에러 발생시킨다.

비교 연산자를 이용하여 조건을 설정하며 비교 값을 리터럴만 사용 가능하고 변하는 값이나 함수 사용은 불가능하다.

 

[ CHECK 예시 ]

CREATE TABLE USER_CHECK(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10) CHECK (GENDER IN (‘남’, ‘여‘)), -- CHECK 제약 조건 설정
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

 

 

SUBQUERY를 이용한 CREATE TABLE

서브 쿼리를 이용해서 SELECT의 조회 결과로 테이블을 생성하는 방법으로 컬럼명과 데이터 타입, 값이 복사 되고 제약 조건은 NOT NULL만 복사가 된다.

 

[ SUBQUERY 예시 ]

CREATE TABLE EMPLOYEE_COPY
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
LEFT JOIN JOB USING(JOB_CODE);