[Oracle] 제약조건(CONSTRAINT) - FOREIGN KEY(외래키) / ON DELETE SET NULL / ON DELETE CASCADE /ON DELETE RESTRICTED

 

[Oracle] 제약조건(CONSTRAINTS) - CHECK/PRIMARY KEY(기본키)

 

FOREIGN KEY(외래키)

다른 테이블에 존재하는 값이 들어와야 되는 컬럼에 부여하는 제약조건

=> 다른 테이블(부모 테이블)을 참조한다고 표현

즉, 참조된 다른 테이블이 제공하고 잇는 값만 들어올 수 있다.

=> FOREIGN KEY 제약조건으로 다른 테이블 간의 관계를 형성할 수 있다.

-- 1) 컬럼레벨 방식
컬럼명 자료형 CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)

-- 2) 테이블레벨 방식
CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조할컬럼명)

-- 두 방식 모두 참조할 컬럼명은 생략이 가능
-- 생략시 기본적으로 참조할 테이블의 PRIMARY KEY 컬럼으로 참조할 컬럼명이 잡힌다

-- 부모테이블 생성
CREATE TABLE MEM_GRADE(
    GRADE_CODE CHAR(2) PRIMARY KEY,
    GRADE_NAME VARCHAR2(20) NOT NULL
);

INSERT INTO MEM_GRADE VALUES ('G1','일반회원');
INSERT INTO MEM_GRADE VALUES ('G2','우수회원');
INSERT INTO MEM_GRADE VALUES ('G3','특별회원');


CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE), --컬럼레벨 방식
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
    --FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)  --테이블레벨 방식    
);

INSERT INTO MEM
VALUES (1, 'user01', 'pass01', '홍길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (2, 'user02', 'pass02', '고길동', 'G2', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (3, 'user03', 'pass03', '손길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (4, 'user04', 'pass04', '유길동', NULL, NULL, NULL, NULL);
-- 외래키 제약조건이 걸려있는 컬럼에는 기본적으로 NULL 값이 들어갈 수 있다
INSERT INTO MEM
VALUES (5, 'user05', 'pass05', '이길동', 'G4', NULL, NULL, NULL);
-- G4등급은 MEM_GRADE 테이블의 GRADE_CODE컬럼에서 제공하는 값이 아니므로 오류 발생
-- ORA-02291: integrity constraint (DDL.SYS_C007071) violated - parent key not found


-- 만약 부모테이블( MEM_GRADE )에서 데이터값이 삭제 된다면?
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';
-- 자식테이블(MEM) 중 G1을 사용하고 있는 데이터가 있기 때문에 삭제할 수 없음
-- ORA-02292: integrity constraint (DDL.SYS_C007071) violated - child record found
-- 외래키 제약조건 부여시 삭제옵션을 부여하지 않았기 때문에
-- 자식테이블에서 사용하고 있는 값이 있을 경우 삭제가 안되는 "삭제제한옵션"이 걸려있음

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G3'; -- 1행이(가) 삭제되었습니다.
-- 자식테이블에서 사용되고 있는 값이 아니기 때문에 삭제 가능

 

 

728x90

>> 자식테이블 생성시 (외래키 제약조건 부여시)

부모테이블의 데이터가 삭제되었을 때 자식 테이블에는 어떻게 처리할지 옵션을 정할 수 있다.

>> FOREIGN KEY 삭제 옵션

삭제 옵션을 별도로 지정하지 않으면 ON DELETE RESTRICTED(삭제 제한)으로 기본 설정

 

1) ON DELETE SET NULL

부모데이터 삭제시 해당 데이터를 사용하고 있는 자식데이터를 NULL로 변경시키는 옵션

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2),
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL 
);

INSERT INTO MEM
VALUES (1, 'user01', 'pass01', '홍길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (2, 'user02', 'pass02', '고길동', 'G2', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (3, 'user03', 'pass03', '손길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (4, 'user04', 'pass04', '유길동', NULL, NULL, NULL, NULL);

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1'; -- 1행이(가) 삭제되었습니다.
--문제없이 잘 삭제된다.
SELECT * FROM MEM;
-- 자식테이블(MEM)의 GRADE_ID가 G1인 부분이 모두 NULL이 되어버림

 

2) ON DELETE CASCADE

부모데이터 삭제시 해당 테이터를 사용하고 있는 자식데이터도 같이 삭제해버리는 옵션

CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PWD VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2),
    GENDER CHAR(3) CHECK (GENDER IN ('남', '여')),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
    FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL 
);

INSERT INTO MEM
VALUES (1, 'user01', 'pass01', '홍길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (2, 'user02', 'pass02', '고길동', 'G2', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (3, 'user03', 'pass03', '손길동', 'G1', NULL, NULL, NULL);
INSERT INTO MEM
VALUES (4, 'user04', 'pass04', '유길동', NULL, NULL, NULL, NULL);

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1'; -- 1행이(가) 삭제되었습니다.
--문제없이 잘 삭제된다.
SELECT * FROM MEM;
-- 자식테이블(MEM)의 GRADE_ID가 G1인 행등이 모두 함께 삭제됨

 

 

[Oracle] DDL(데이터 정의 언어) / 테이블 생성하기(CREATE TABLE) / COMMENT ON COLUMN /자료형