[Oracle] 26.무결성 제약조건(2)

테이블 레벨에서 제약조건 지정

저번 장에서는 다음과 같이 속성레벨에서 제약조건을 지정해주었습니다.

-- 속성레벨에서 제약조건 지정

CREATE TABLE EMP01(
    empno NUMBER(4) PRIMARY KEY,
    ename VARCHAR2(10) NOT NULL,
    job VARCHAR2(9),
    deptno NUMBER(2) REFERENCES DEPT01(DEPTNO)
);

그러나 다음과 같이 테이블 레벨에서도 제약조건을 지정해줄 수 있습니다.

-- 테이블레벨에서 제약조건 지정

CREATE TABLE emp01 (
    empno    NUMBER(4),
    ename    VARCHAR2(10) NOT NULL,
    job      VARCHAR2(9),
    deptno   NUMBER(2),
    PRIMARY KEY ( empno ),
    UNIQUE ( job ),
    FOREIGN KEY ( deptno ) REFERENCES departments ( department_id )
);

주의할 점은 NOT NULL 제약조건은 속성 레벨에서만 지정할 수 있습니다.

제약 조건 명을 부여할 때는 다음과 같이 합니다.

CREATE TABLE emp01 (
    empno    NUMBER(4),
    ename    VARCHAR2(10)
        CONSTRAINT emp03_ename_nn NOT NULL,
    job      VARCHAR2(9),
    deptno   NUMBER(2),
    CONSTRAINT emp01_empno_pk PRIMARY KEY ( empno ),
    CONSTRAINT emp01_job_uk UNIQUE ( job ),
    CONSTRAINT emp01_deptno_fk FOREIGN KEY ( deptno ) REFERENCES departments ( department_id )
);

복합키를 기본키로 설정

테이블 레벨에서 제약조건을 지정하면 복합키를 기본키로 설정하거나, 여러 속성에 한꺼번에 제약조건을 지정할 수 있습니다!

복합키를 기본키로 설정하는 방법은 다음과 같습니다.

CREATE TABLE emp02 (
    name      VARCHAR2(10),
    address   VARCHAR2(30),
    hphone    VARCHAR2(16),
    CONSTRAINT emp02_combo_pk PRIMARY KEY ( name, phone )
);

제약조건 추가하기

이번에는 이번에는 이미 생성된 테이블에 제약조건을 추가하는 방법에 대해 알아보겠습니다.

--제약조건 미지정

CREATE TABLE emp01 (
    empno    NUMBER(4),
    ename    VARCHAR2(10),
    job      VARCHAR2(9),
    deptno   NUMBER(2)
);

제약조건을 추가할 때는 ALTER 문을 이용합니다~

ALTER TABLE emp01 ADD CONSTRAINT emp01_empno_pk PRIMARY KEY(empno);

ALTER TABLE emp01 ADD CONSTRAINT emp01_deptno_fk FOREIGN KEY(deptno) REFERENCES dept01(deptno);

이 때 주의할 점은 NOT NULL 제약조건은 ADD로 추가불가하므로 MODIFY로 변경해야 합니다.

ALTER TABLE emp01 MODIFY ename CONSTRAINT emp01_ename_nn NOT NULL;

제약조건 제거

제약조건을 제거하려면 DROP 문을 이용합니다.

ALTER TABLE emp01 DROP CONSTRAINT emp01_ename_nn;

제약조건의 비활성화

두 테이블 emp01,dept01이 있다고 가정합시다.

현재 emp01 테이블의 deptno 속성은 외래키로써 dept01 테이블의 deptno 속성을 참조하고 있습니다.

이 때 다음과 같은 delete 문은 오류를 발생시킵니다.

DELETE FROM dept01 WHERE deptno=10; -- 연결되어 있는 자식레코드가 있어서 제거 불가(참조하고있는 레코드가 존재)

만약 위의 delete문을 실행하려면 다음과 같이 해야합니다.

참조하고 있는 레코드들을 다 지운다.

제약조건 제거

제약조건 비활성화

제약조건을 비활성화 하려면 다음과 같이 합니다.

ALTER TABLE emp01 DISABLE CONSTRAINT emp01_deptno_fk;

그럼 아까의 delete 문을 실행할 수 있습니다.

다시 활성화를 하려면, 지웠던 컬럼을 다시 넣고 해야합니다.

INSERT INTO dept01 VALUES (10,'개발부',DEFAULT);

ALTER TABLE emp01 ENABLE CONSTRAINT emp01_deptno_fk;

CASCADE

부모 테이블과 자식 테이블 간의 참조 설정이 되어 있을 때 부모 테이블의 제약조건을

비활성화하면 이를 참조하고 있는 자식테이블의 제약조건까지 같이 비활성화 시킬 수 있습니다.

CASCADE 를 이용하면 됩니다.

만약, 이전의 테이블 emp01과 dept01 에서 다음과 같이 dept01 테이블의 기본키 제약조건을 비활성화 시키려고 했을때

ALTER TABLE dept01 DISABLE CONSTRAINT dept01_deptno_pk;

자식테이블 emp01의 외래키 제약조건때문에 오류가 발생합니다.

하지만, CASCADE를 추가하면 자식 테이블의 제약조건 까지 비활성화 시켜버립니다.

ALTER TABLE dept01 DISABLE CONSTRAINT dept01_deptno_pk CASCADE; --한번에 부모와 자식 제약조건 비활성화

활성화 할 때도 부모 제약조건 활성화 한 후에 자식 제약조건을 활성화 해야합니다.

ALTER TABLE emp01 ENABLE CONSTRAINT emp01_deptno_fk; -- 활성화 할 때도 부모 제약조건 활성화한후에 가능
ALTER TABLE dept01 ENABLE CONSTRAINT dept01_deptno_pk;

부모테이블의 제약조건 삭제 시 자식 테이블의 제약조건도 삭제되게 할때도 CASCADE를 이용합니다!

ALTER TABLE dept01 DROP CONSTRAINT dept01_deptno_pk CASCADE; --emp01_deptno_fk도 같이 삭제

자식 테이블의 외래키 제약조건에 ON DELETE CASCADE를 추가하면,

외래키가 참조하는 부모 테이블의 기본키가 삭제되었을 때 같이 삭제되도록 할 수 있습니다.

CREATE TABLE emp01 (
    empno    NUMBER(4),
    ename    VARCHAR2(10)
        CONSTRAINT emp03_ename_nn NOT NULL,
    job      VARCHAR2(9),
    deptno   NUMBER(2),
    CONSTRAINT emp01_empno_pk PRIMARY KEY ( empno ),
    CONSTRAINT emp01_deptno_fk FOREIGN KEY ( deptno )
        REFERENCES dept01 ( deptno )
            ON DELETE CASCADE
);

DELETE FROM DEPT01 WHERE DEPTNO='10'; -- 데이터 삭제시 DEPT01의 DEPTNO을 참조하는 EMP01의 데이터도 같이 삭제