[Oracle] 오라클 DB 제약조건 설정

 

제약조건(무결성)

-데이터를 추가, 삭제, 수정이 이루어질 때 DB의 무결성을 유지

CREATE TABLE emp01(
    empno NUMBER,
    ename VARCHAR2(20),
    job VARCHAR2(20),
    deptno NUMBER
)

INSERT INTO emp01 VALUES(NULL, NULL, 'IT', 30); => NOT NULL 설정 필요

CREATE TABLE emp02(
    empno NUMBER NOT NULL,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER
)

INSERT INTO emp02 VALUES(NULL, NULL, 'IT', 30);
INSERT INTO emp02 VALUES(100,'kim', 'IT', 30);
INSERT INTO emp02 VALUES(100,'park', 'IT', 30);  => empno 중복 문제발생


CREATE TABLE emp03(
    empno NUMBER UNIQUE,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER
)

INSERT INTO emp03 VALUES(100,'kim', 'IT', 30);
INSERT INTO emp03 VALUES(100,'park', 'IT', 30);
 =>"unique constraint (%s.%s) violated"
중복값이 들어갈 수 없다.

PRIMARY KEY 제약조건

-> UNIQUE + NOT NULL

CREATE TABLE emp04(
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER
)

INSERT INTO emp04 VALUES(100,'park', 'IT', 30000);  => 30000이라는 부서번호 X
FOREIGN KEY 필요.

FOREIGN KEY

CREATE TABLE emp05(
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER REFERENCES departments(department_id)
)

INSERT INTO emp05 VALUES(100,'park', 'IT', 30000); 
=> parent key not found

 

테이블레벨 방식 -> 제약조건 이름을 명시

CREATE TABLE emp06(
    empno NUMBER,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER, 

    CONSTRAINT emp06_empno_pk PRIMARY KEY(empno),
    CONSTRAINT emp06_deptno_fk 
                          FOREIGN KEY(deptno)
                          REFERENCES departments(department_id)
)

테이블 수정 방식 -> 테이블을 먼저 생성 후 추가해주는 방식

CREATE TABLE emp07(
    empno NUMBER,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER
)

ALTER TABLE emp07
            ADD CONSTRAINT emp07_empno_pk PRIMARY KEY(empno);
            
ALTER TABLE emp07
            ADD CONSTRAINT emp07_deptno_fk 
            FOREIGN KEY(deptno)
            REFERENCES departments(department_id);

CHECK 제약조건(의도된 데이터만 입력 받기 위해)

CREATE TABLE emp08(
    empno NUMBER,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER,
    gender char(1) CHECK(gender IN('M', 'F'))     // gender => M, F만 가능
)

INSERT INTO emp08 VALUES(100,'park', 'IT', 30, 'A'); 
"check constraint (%s.%s) violated"

DEFAULT 제약조건

CREATE TABLE emp09(
    empno NUMBER,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER,
    loc VARCHAR(20) DEFAULT 'Seoul'
)

INSERT INTO emp09(empno, ename, job, deptno) VALUES(100, 'park', 'IT', 30); 
=> loc 컬럼을 넣지 않았다. DEFAULT 제약조건으로 Seoul 데이터가 들어가 있다.

 

2개 이상 주식별자 설정

CREATE TABLE emp10(
    empno NUMBER,
    ename VARCHAR2(20) NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER
)

ALTER TABLE emp10
            ADD CONSTRAINT emp10_empno_pk PRIMARY KEY(empno, ename);

INSERT INTO emp10 VALUES(100,'park', 'IT', 30); 
INSERT INTO emp10 VALUES(100,'kim', 'IT', 30);  
=> empno이 같더라도 데이터가 insert 된다.
primary key 2개가 모두 겹쳐야 제약조건에 걸리게 된다.

제약조건 삭제

ALTER TABLE 테이블명
        DROP CONSTRAINT 제약조건이름

CREATE TABLE 테이블명(
            deptno NUMBER REFERENCES 참조테이블(참조컬럼명)
            ON DELETE CASCADE

EX) 부모테이블에서 특정번호(30번 부서)를 삭제시 자식테이블에서 30번 부서에 근무하는 사원들도 함께 삭제

---