[Oracle] CONSTRAINT(제약조건)_FOREIGN KEY(외래키)

회원등급에 대한 데이터를 따로 보관하는 테이블

CREATE TABLE MEM_GRADE(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO MEM_GRADE VALUES(10, '일반회원');
INSERT INTO MEM_GRADE VALUES(20, '우수회원');
INSERT INTO MEM_GRADE VALUES(30, '특별회원');

 

 

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,
    GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER -- 회원 등급번호 같이 보관할 컬럼
);

INSERT INTO MEM
VALUES(1, 'user01', 'pass01', '손흥민', '남', null, null, null);

INSERT INTO MEM
VALUES(2, 'user02', 'pass02', '이강인', null, null, null, 10);

INSERT INTO MEM
VALUES(3, 'user03', 'pass03', '이승우', '남', null, null, 40);
-- 유효한 회원등급 번호가 아님에도 불구하고 잘 insert 됨... 당연한거임 MEM_GRADE랑은 아예 관련없는 테이블
-- 유효한 회원등급 번호일때만 INSERT하기 위해서는 FOREIGN KEY 제약조건이 필요하다.

---

* FOREIGN KEY(외래키) 제약조건

다른 테이블에 존재하는 값만 들어와야 되는 특정 컬럼 부여하는 제약 조건
--> 다른 테이블을 참조한다고 표현
--> 주로 FOREIGN KEY 제약조건에 의해 테이블간의 관계가 형성됨!
    
> 컬럼레벨방식
컬럼명 자료형 [CONSTRAINT 제약조건명] REFERENCES 참조할 테이블명[(참조할 컬럼명)]
    
> 테이블레벨방식
FOREIGN KEY(컬럼명) REFERENCES 참조할 테이블명[(참조할 컬럼명)]
    
--> 참조할 컬럼명 생략시 참조할 테이블 PRIMARY KEY로 지정된 컬럼으로 자동매칭

 

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,
    GENDER CHAR(3) CHECK (GENDER IN('남', '여')),
    PHONE VARCHAR2(13),
    EMAIL VARCHAR2(50),
    GRADE_ID NUMBER REFERENCES MEM_GRADE(GRADE_CODE) -- 컬럼레벨방식 MEM_GRADE 가 부모
    -- ,FOREIGNKEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE)
);

 

INSERT INTO MEM
VALUES(1, 'user01', 'pass01', '손흥민', '남', null, null, null);

INSERT INTO MEM
VALUES(2, 'user02', 'pass02', '이강인', null, null, null, 10);

INSERT INTO MEM
VALUES(3, 'user03', 'pass03', '이승우', '남', null, null, 40);
-- ORA-02291: integrity constraint (DDL.SYS_C007172) violated - parent key not found

INSERT INTO MEM
VALUES(3, 'user03', 'pass03', '이승우', '남', null, null, 20);
     
INSERT INTO MEM
VALUES(4, 'user04', 'pass04', '안정환', null, null, null, 10);

---

-- MEM_GRADE(부모테이블) MEM(자식테이블)
-- 이때 부모테이블 (MEM_GRADE) 에서 데이터 값을 삭제할 경우 어떤 문제가 발생할까?
-- 데이터 삭제 : DELETE FROM 테이블명 WHERE 조건;

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 10;
-- ORA-02292: integrity constraint (DDL.SYS_C007172) violated - child record found
--> 자식테이블 (MEM)에 10 이라는 값을 사용하고 있기 때문에 삭제가 안됨!

DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 30;
--> 자식테이블 (MEM)에 30이라는 값을 사용하고 있지 않기 때문에 삭제가 잘됨!!

 

--> 자식테이블에 이미 사용하고 있는 값이 있을 경우
--> 부모테이블로부터 무조건 삭제가 안되게 하는 "삭제제한" 옵션이 걸려있음!!

 

삭제옵션 지정하는 방법

2023.07.29 - [IT/Oracle] - [Oracle] CONSTRAINT(제약조건)_FOREIGN KEY(외래키)_삭제옵션

[Oracle] CONSTRAINT(제약조건)_FOREIGN KEY(외래키)_삭제옵션