안녕하세요.
https://blog.naver.com/sysysy0302여니입니다 :)
+ 이전 요약정리문에서 FOREIGN KEY(외래키) 파트는 예시문을 간략히 다루기에 식이 너무 길어 따로 빼서 요약정리합니다.
-- 부모테이블 만들기
-- 회원등급에 대한 데이터를 보관하는 테이블
CREATE TABLE MEM_GRADE(
GRADE_CODE CHAR(2) PRIMARY KEY, -- 등급코드 / 문자열 'G1', 'G2' , ...
GRADE_NAME VARCHAR2(20) NOT NULL -- 등급명 / 문자열 (일반회원, 우수회원, VIP회원)
);
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,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL, -- 컬럼레벨방식 제약조건명 부여
GRADE_ID CHAR(2),
-- GRADE_ID CHAR(2) REFERENCES MEM_GRADE,
GENDER CHAR(3) CHECK( GENDER IN('남','여')), -- GENDER ='남' OR GENDER='여'
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL, -- DEFAULT가 (제약조건이 아니기때문에 제약조건보다 앞에) 먼저 와야함.
UNIQUE(MEM_ID) --테이블 레벨 방식
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) -- 테이블레벨 방식
);
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(1,'user01','pass01','ksy','G1');
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(2,'user02','pass01','ksy','G2');
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(3,'user03','pass01','ksy','G3');
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(4,'user04','pass01','ksy','G4');
SELECT MEM_ID, GRADE_NAME
FROM MEM
JOIN MEM_GRADE ON GRADE_ID = GRADE_CODE;
-- 문제) 부모테이블에서 데이터값이 삭제된다면?
-- MEM_GRADE테이블에서 GRADE_CODE가 G3인 데이터만 지워보기?
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G3';
-- 자식테이블에서 현재 G3 값을 사용하고 있기 때문에 삭제할 수 없음.
-- 현재 외래키 제약조건 부여시 삭제에 대한 옵션을 별도로 부여하지 않은 상태
-- => 기본값으로 삭제 제한 옵션이 걸려있음
/*
* 자식테이블 생성시 외래키 제약조건을 부여했을때
부모테이블의 데이터가 삭제되었을 때 자식테이블에서는 어떻게 처리할지를 옵션으로 정해둘수있다.
* FOREIGN KEY 삭제 옵션
- ON DELETE SET NULL : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 NULL로 바꾸겠다.
- ON DELETE CASCADE : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 같이 삭제하겠다.
- ON DELETE RESTRICTED : 삭제를 제한하겠다(기본옵션)
*/
DROP TABLE MEM;
CREATE TABLE MEM(
MEM_NO NUMBER PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GRADE_ID CHAR(2),
-- GRADE_ID CHAR(2) REFERENCES MEM_GRADE, --외래키 제약조건 (컬럼레벨)
GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
UNIQUE(MEM_ID),
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL--외래키 제약조건 (테이블레벨)
);
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(1,'user01','pass01','ksy','G1');
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(2,'user02','pass01','ksy','G2');
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(3,'user03','pass01','ksy','G3');
SELECT * FROM MEM;
-- 부모테이블에서 GRADE_CODE가 G1인 데이터 삭제하기
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G1';
SELECT * FROM MEM_GRADE;
SELECT * FROM MEM;
-- 문제없이 삭제잘되고, G1을 참조하던 자식 테이블의 GRADE_ID 값에 G1대신 NULL값이 들어갔다.
DROP TABLE MEM;
CREATE TABLE MEM(
MEM_NO NUMBER PRIMARY KEY,
MEM_ID VARCHAR2(20) NOT NULL,
MEM_PWD VARCHAR2(20) NOT NULL,
MEM_NAME VARCHAR2(20) NOT NULL,
GRADE_ID CHAR(2),
-- GRADE_ID CHAR(2) REFERENCES MEM_GRADE, --외래키 제약조건 (컬럼레벨)
GENDER CHAR(3) CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(15),
EMAIL VARCHAR2(30),
MEM_DATE DATE DEFAULT SYSDATE NOT NULL,
UNIQUE(MEM_ID),
FOREIGN KEY(GRADE_ID) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE --외래키 제약조건 (테이블레벨)
);
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(1,'user01','pass01','ksy','G2');
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(2,'user02','pass01','ksy','G2');
INSERT INTO MEM(MEM_NO, MEM_ID, MEM_PWD, MEM_NAME, GRADE_ID)
VALUES(3,'user03','pass01','ksy','G3');
SELECT * FROM MEM;
DELETE FROM MEM_GRADE
WHERE GRADE_CODE = 'G2';
SELECT * FROM MEM_GRADE;
SELECT * FROM MEM;
-- 문제없이 잘 삭제되었고, 자식테이블의 GRADE_ID 가 G2인 행까지 함께 삭제해버림
-- 조인문제
-- 전체 회원의 회원번호, 아이디, 비밀번호, 이름, 등급명을 오라클전용구문, ANSI 구문으로 나눠서 작성
-- ANSI
SELECT MEM_NO 회원번호, MEM_ID 아이디, MEM_PWD 비밀번호, MEM_NAME 이름, GRADE_ID 등급명
FROM MEM
JOIN MEM_GRADE ON GRADE_ID = GRADE_CODE;
-- 오라클
SELECT MEM_NO 회원번호, MEM_ID 아이디, MEM_PWD 비밀번호, MEM_NAME 이름, GRADE_ID 등급명
FROM MEM, MEM_GRADE
WHERE GARADE_ID = GRADE_CODE;
/*
외래키 제약조건이 걸려있지 않더라도 JOIN은 가능함. 단, 두 컬럼의 동일한 의미의 데이터가
담겨있어야 정상적으로 조회 가능함.
*/