014 dev log

DDL (DATA DEFINITION LANGUAGE)

데이터 정의 언어

객체들을 새롭게 생성(CREATE)하고 수정(ALTER)하고 삭제(DROP)하는 구문

DELETE

테이블에 기록된 데이터를 행단위로 삭제하는 구문

WHERE절은 생략이 가능하지만 생략시 모든행이 삭제되니 주의할 것

DELETE FROM 테이블명
WHERE 조건; (생략가능)

--EMPLOYEE 테이블의 모든 행 삭제
DELETE FROM EMPLOYEE;
SELECT * FROM EMPLOYEE; --0개 행 조회
--데이터만 삭제, 테이블 삭제는 아님

--EMPLOYEE 테이블에서 김칠칠,김구구 사원 정보 삭제
DELETE FROM EMPLOYEE
WHERE EMP_NAME IN('김칠칠','김구구');

TRUNCATE

테이블의 전체행을 모두 삭제할때 사용하는 구문
DELETE 구문보다 속도가 빠르지만

별도 조건 제시가 불가능하고 ROLLBACK 불가능하니 주의 할 것

TRUNCATE TABLE 테이블명;

TRUNCATE TABLE EMP_SALARY;

ROLLBACK

변경사항에 대해 되돌리는 명령어

DML한정(INSERT,DELETE,UPDATE), 마지막 COMMIT전 까지만 롤백 가능

ROLLBACK;

COMMIT

지금까지 변경한 작업을 확정하겠다는 명령어

COMMIT;

UPDATE

테이블에 기록된 기존의 데이터를 수정하는 구문

SET절에 ,로 나열하여 여러개 컬럼값을 동시에 변경 가능

WHERE절은 생략 가능하지만 생략시 모든 행의 데이터가 변경되니 주의

UPDATE 테이블명
SET 컬럼명=변경값,컬럼명=변경값,...
WHERE 조건;

--DEPT_COPY 테이블에서 D9부서의 부서명을 전략기획팀으로 변경하기
UPDATE DEPT_COPY
SET DEPT_TITLE='전략기획팀'; --WHERE절 생략으로 모든 부서명이 전략기획팀으로 변경됨
UPDATE DEPT_COPY
SET DEPT_TITLE='전략기획팀'
WHERE DEPT_ID='D9'; --DEPT_ID가 D9인 부서 이름만 전략기회팀으로 변경됨

--EMP_SALARY테이블에서 노옹철 사원 급여 1,000만원으로 변경
UPDATE EMP_SALARY
SET SALARY=10000000
WHERE EMP_NAME='노옹철';
--EMP_SALARY테이블에서 선동일 사원 급여 700만원 보너스 0.2로 변경
UPDATE EMP_SALARY
SET SALARY=7000000, BONUS=0.2
WHERE EMP_NAME='선동일';

--전체사원의 급여를 기존 급여에서 20프로 인상한 금액으로 변경
UPDATE EMP_SALARY
SET SALARY=SALARY+(SALARY*0.2);

UPDATE에서 서브쿼리 활용

서브쿼리를 수행한 결과값으로 기존의 값을 변경

변경값이 제약조건에 맞지 않는 경우 오류 발생

CF. CREATE시 서브쿼리 사용 : 서브쿼리를 수행한 결과를 테이블에 넣어 생성

     INSERT시 서브쿼리 사용 : 서브쿼리를 수행한 결과를 해당 테이블에 삽입

UPDATE 테이블명
SET 컬럼명 = (서브쿼리)
WHERE 조건명; (생략가능)

--EMP_SALARY 테이블의 방명수 사원 부서코드를 선동일 사원 부서코드로 변경(방명수 D1 선동일 D9)
UPDATE EMP_SALARY
SET DEPT_CODE = (SELECT DEPT_CODE
                 FROM EMPLOYEE
                 WHERE EMP_NAME='선동일')
WHERE EMP_NAME='방명수';

--방명수 사원의 급여와 보너스를 유재식 사원의 급여와 보너스값으로 변경
UPDATE EMP_SALARY
SET (SALARY, BONUS) = (SELECT SALARY, BONUS
                       FROM EMP_SALARY
                       WHERE EMP_NAME='유재식')
WHERE EMP_NAME='방명수';

INSERT

테이블에 새로운 행을 추가하는 구문

INSERT INTO 테이블명 VALUES (값1,값2,값3,...);

해당 테이블에 모든컬럼에 대하여 추가할 값을 직접 제시하여 한 행에 INSERT할 때 사용
(컬럼의 순서, 자료형 개수에 맞춰 VALUES 괄호 안에 값을 나열해야함)
 - 부족하게 값을 제시할 경우 : NOT ENOUGH VALUE 오류
 - 값을 더 많이 제시할 경우 : TOO MANY VALUES 오류

--EMPLOYEE 테이블에 사원 정보 추가
INSERT INTO EMPLOYEE VALUES(999,'김구구','990909-3123323','kimgugu@naver.com',
                            '01099999999','D1','J7','S6',1999999,0.9,200,SYSDATE,NULL,DEFAULT);

INSERT INTO 테이블명 (컬럼명1,컬럼명2,컬럼명3) VALUES (값1,값2,값3);

해당 테이블에 특정 컬럼만 선택하여 추가할 값을 제시하고자 할 때 사용
선택되지 않은 컬럼은 기본값인 NULL이 입력
DEFAULT 설정이 되어 있을경우 DEFAULT 설정값이 입력(기본값 설정)
(단, NOT NULL 제약조건이 걸려있는 컬럼은 반드시 선택해서 직접 값을 넣어야 하며
단, DEFAULT 설정이 되어있다면 NOT NULL이라고 해도 선택하지 않아도 됨(DEFAULT값이 기본값이니까))

INSERT INTO EMPLOYEE(EMP_ID,EMP_NAME,EMP_NO,DEPT_CODE,JOB_CODE,SAL_LEVEL,HIRE_DATE)
VALUES(777,'김칠칠','770707-1777777','D1','J6','S5',SYSDATE);

INSERT INTO 테이블명 (서브쿼리);

VALUES()로 값을 직접 기입하는 것이 아닌 서브쿼리로 조회한 결과값을 통쨰로 INSERT하는 구문
즉, 여러행을 한번에 INSERT 할수 있다

--전체 사원들의 사번,이름,부서명을 조회한 결과를 EMP_01 테이블에 통째로 추가
--1) 조회
SELECT EMP_ID,EMP_NAME,DEPT_TITLE
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE=DEPT_ID(+); --LEFT조인하여 DEPT_TITLE NULL값도 불러오기
--2) 조회결과 INSERT
INSERT INTO EMP_01(
    SELECT EMP_ID,EMP_NAME,DEPT_TITLE
    FROM EMPLOYEE, DEPARTMENT
    WHERE DEPT_CODE=DEPT_ID(+)
);

--EMP_011 테이블을 만들고 컬럼은 사원명,직급명,보너스를 넣고 EMPLOYEE테이블에 있는 정보 넣기
--1) 조회
CREATE TABLE EMP_011
AS SELECT EMP_NAME,JOB_NAME,BONUS
    FROM EMPLOYEE E, JOB J
    WHERE E.JOB_CODE=J.JOB_CODE
    AND 1=0;
--2) 조회결과 INSERT
INSERT INTO EMP_011(
    SELECT EMP_NAME,JOB_NAME,BONUS
    FROM EMPLOYEE E, JOB J
    WHERE E.JOB_CODE=J.JOB_CODE
);

INSERT ALL

두개 이상의 테이블에 각각 INSERT할때 사용(서브쿼리가 동일해야함)

INSERT ALL
   INTO 테이블명1 VALUES(컬럼명,컬럼명,..)
   INTO 테이블명2 VALUES(컬럼명,컬럼명,..)
서브쿼리;

--새로운 테이블 만들기
--1)급여가 300만원 이상인 사원들의 사번,사원명,직급명,보관할 테이블
CREATE TABLE EMP_SALARY300_1
AS SELECT EMP_ID,EMP_NAME,JOB_NAME
    FROM EMPLOYEE E, JOB J
    WHERE E.JOB_CODE=J.JOB_CODE
    AND 1=0; --틀만 가져오려고

--2)급여가 300만원 이상인 사원들의 사번,사원명,부서명,보관할 테이블
CREATE TABLE EMP_SALARY300_2
AS SELECT EMP_ID,EMP_NAME,DEPT_TITLE
    FROM EMPLOYEE, DEPARTMENT
    WHERE DEPT_CODE=DEPT_ID
    AND 1=0;

INSERT ALL
    INTO EMP_SALARY300_1 VALUES(EMP_ID,EMP_NAME,JOB_NAME)
    INTO EMP_SALARY300_2 VALUES(EMP_ID,EMP_NAME,DEPT_TITLE)
SELECT *
FROM EMPLOYEE E, JOB J, DEPARTMENT D
WHERE E.JOB_CODE=J.JOB_CODE
AND DEPT_CODE=DEPT_ID
AND SALARY>=3000000;

INSERT ALL
WHEN 조건1 THEN
    INTO 테이블명 VALUES (컬럼명, 컬럼명, ...)
WHEN 조건2 THEN
    INTO 테이블명 VALUES (컬럼명, 컬럼명, ...)
서브쿼리;

--2010년도 기준으로 이전에 입사한 사원의 사번,사원명,입사일,급여를 담는 테이블 생성
CREATE TABLE EMP_OLD
AS SELECT EMP_ID,EMP_NAME,HIRE_DATE,SALARY
   FROM EMPLOYEE
   WHERE 0=1;
--2010년도 기준으로 이후에 입사한 사원의 사번,사원명,입사일,급여를 담는 테이블 생성
CREATE TABLE EMP_NEW
AS SELECT EMP_ID,EMP_NAME,HIRE_DATE,SALARY
   FROM EMPLOYEE
   WHERE 0=1;

--서브쿼리로 입력할 부분 2010년 이후, 이전
SELECT EMP_ID,EMP_NAME,HIRE_DATE,SALARY
FROM EMPLOYEE
WHERE HIRE_DATE<'2010/01/01'; --2010년도 이전 입사자
--WHERE HIRE_DATE>='2010/01/01'; --2010년도 이후 입사자

INSERT ALL
WHEN HIRE_DATE<'2010/01/01' THEN
    INTO EMP_OLD VALUES(EMP_ID,EMP_NAME,HIRE_DATE,SALARY)
WHEN HIRE_DATE>='2010/01/01' THEN
    INTO EMP_NEW VALUES(EMP_ID,EMP_NAME,HIRE_DATE,SALARY)
    SELECT *
    FROM EMPLOYEE;

DML (DATA MANIPULATION LANGUAGE)

데이터 조작 언어

테이블에 새로운 데이터를 삽입(INSERT)하거나 기존의 데이터를 수정(UPDATE)하거나 삭제(DELETE) 하는 구문

서브쿼리(SUBQUERY)를 이용한 테이블 생성

테이블을 복사하는 개념이다

CREATE TABLE 테이블명
AS 서브쿼리;

WHERE문에 조건을 부여하여 복사할 범위를 설정할수 있다

0=1(FALSE), 1=1(TRUE)

--EMPLOYEE 테이블을 복제한 새로운 테이블 생성(EMPLOYEE_COPY)
CREATE TABLE EMPLOYEE_COPY
AS SELECT * FROM EMPLOYEE;

--EMPLOYEE 테이블의 데이터가 아닌 컬럼 구조만 복사하고 싶을 때(조건 부여)
CREATE TABLE EMPLOYEE_COPY2
AS SELECT *
   FROM EMPLOYEE
   WHERE 0=1;
   
--전체사원의 사번, 사원명, 급여, 연봉 조회한 결과를 복제한 테이블 생성(내용도 같이)
CREATE TABLE EMPLOYEE_COPY4
AS SELECT EMP_ID 사번, EMP_NAME 사원명, SALARY 급여, (SALARY*12) 연봉
   FROM EMPLOYEE;
--서브쿼리에서 산술연산 또는 함수식이 기술된 경우 별칭 필수!

CONSTRAINTS (제약조건)

원하는 데이터값만 유지하기 위해 특정 컬럼마다 설정하는 제약
제약조건이 부여된 컬럼에 들어올 데이터에 문제 여부를 자동으로 검사하는 목적(데이터 무결성)

제약조건 부여방식

//컬럼레벨방식
CREATE TABLE 테이블명(
컬럼명 자료형 제약조건1 제약조건2,
컬럼명 자료형 CONSTRAINT 조약조건명 제약조건,
컬럼명 자료형,
...
);

//테이블레벨방식
CREATE TABLE 테이블명(
컬럼명 자료형,
컬럼명 자료형,
...,
컬럼명 자료형,
CONSTRAINT 제약조건명 제약조건(컬럼명)
);

CREATE TABLE MEM_CON_NM(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) CONSTRAINT MEM_NAME_NN NOT NULL, --컬럼레벨방식
    GENDER CHAR(3),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    CONSTRAINT MEM_ID_UQ UNIQUE(MEM_ID) --테이블레벨방식
);

INSERT INTO MEM_CON_NM VALUES(10,'test01','test01','김테스트',NULL,NULL,NULL);
INSERT INTO MEM_CON_NM VALUES(12,'test01','test02','김테스트2',NULL,NULL,NULL);
--오류발생!! unique constraint (DDL.MEM_ID_UQ)
INSERT INTO MEM_CON_NM VALUES(15,'test51','test012','김연습','나',NULL,NULL); 
--성별에 '나'로 오타냄
--(DDL.MEM_ID_UQ)제약조건이 오류문구에 발생하니까 오류 찾기가 쉬움

SELECT *
FROM MEM_CON_NM;

NOT NULL

NULL값이 절대 들어와서는 안되는 컬럼에 부여하는 제약조건

해당 컬럼에 반드시 값이 존재해야만 할 경우 사용
데이터 삽입/수정 시 NULL값을 허용하지 않게 제한

컬럼레벨방식으로 작성

--테이블 생성
CREATE TABLE MEM_NOTNULL(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

--데이터 추가
INSERT INTO MEM_NOTNULL 
       VALUES(1,'user01','password01','김일번','남','010-1234-1243','kim1@gmail.com');
INSERT INTO MEM_NOTNULL 
       VALUES(2,NULL,NULL,NULL,NULL,NULL,NULL); --오류발생!! cannot insert NULL into
INSERT INTO MEM_NOTNULL 
       VALUES(2,'user02','password02','박이번',NULL,NULL,NULL);
INSERT INTO MEM_NOTNULL 
       VALUES(3,'user03','password03','유삼번','여',NULL,'yu3@gmail.com');

--테이블 확인
SELECT *
FROM MEM_NOTNULL;

UNIQUE

컬럼에 중복값을 제한하는 제약조건
삽입/수정 시 기존에 해당 컬럼값 중에 중복값이 있을 경우 추가 또는 수정이 되지 않게 제약
컬럼레벨방식, 테이블레벨방식으로 작성

--컬럼레벨 방식
--테이블 생성
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE, --한개의 컬럼에 여러가지 제약조건을 걸수있다
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30)
);

--데이터 입력
INSERT INTO MEM_UNIQUE VALUES(1,'user01','password01','장기하',NULL,NULL,NULL);
INSERT INTO MEM_UNIQUE VALUES(2,'user01','password11','장기상',NULL,NULL,NULL); 
--오류발생!! unique constraint


--테이블레벨링 방식
CREATE TABLE MEM_UNIQUE(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3),
    PHONE VARCHAR2(15),
    EMAIL VARCHAR2(30),
    UNIQUE(MEM_NAME) --테이블레벨링
);

--데이터 입력
INSERT INTO MEM_UNIQUE VALUES(1,'user1','pw01','김당산',NULL,NULL,NULL);
INSERT INTO MEM_UNIQUE VALUES(1,'user2','pw02','김당산',NULL,NULL,NULL); 
--오류발생!! unique constraint

CHECK

컬럼에 기록될 수 있는 값에 대한 조건을 설정하는 제약조건
EX) 성별 '남' OR '여'만 들어올수 있게 하고 싶다

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE NOT NULL --회원가입일
);

INSERT INTO MEM_CHECK 
       VALUES(1,'user11','password','김열심','남','010-3333-7777','kimhus@nate.com',SYSDATE);
INSERT INTO MEM_CHECK 
       VALUES(2,'user22','password','김최고','여','010-1111-6666','kimbest@nate.com',SYSDATE);
INSERT INTO MEM_CHECK 
       VALUES(3,'user33','password','김노력','나','010-6666-5555','kimtry@nate.com',SYSDATE); 
       --오류발생!!

DEFAULT 설정

특정 컬럼에 들어올 값에 대한 기본값 설정 가능

DEFAULT 설정 후 제약조건을 작성한다

EX) 회원가입일 컬럼에 회원정보가 삽입된 순간의 시간을 기록하고 싶다 -> SYSDATE

CREATE TABLE MEM_CHECK(
    MEM_NO NUMBER NOT NULL,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL --DEFAULT 먼저 설정 후 제약조건 작성
);

INSERT INTO MEM_CHECK 
       VALUES(2,'user22','password','김최고','여','010-1111-6666','kimbest@nate.com',SYSDATE);
INSERT INTO MEM_CHECK 
       VALUES(2,'user22','password','김최고','여','010-1111-6666','kimbest@nate.com',DEFAULT);
INSERT INTO MEM_CHECK 
       (MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GENDER)VALUES(2,'user22','password','김최고','여');
--컬럼 형식을 지정해주면 지정이 안된 컬럼에 대하여 기본 NULL값 입력
--DEFAULT 설정 시 DEFAULT값 입력

PRIMARY KEY (기본키)

테이블에서 각 행들의 정보를 유일하게 식별할수 있는 컬럼에 부여하는 제약조건
각 행들을 구분할 수 있는 식별자의 역할이다
식별자의 조건 : 중복값 X, NULL값 X (NOT NULL + UNIQUE)

한 테이블 당 한개의 컬럼에만 지정 가능하다

EX) 사번, 부서아이디, 직급코드, 회원번호, 학번 등

CREATE TABLE MEM_PRIMARYKEY1(
    MEM_NO NUMBER CONSTRAINT MEM_PK PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO MEM_PRIMARYKEY1(MEM_NO,MEM_ID,MEM_PW,MEM_NAME) 
       VALUES (1,'test1','test2','김테스트');
INSERT INTO MEM_PRIMARYKEY1(MEM_NO,MEM_ID,MEM_PW,MEM_NAME)
       VALUES (1,'test2','test3','김밥'); --오류발생!! UNIQUE
INSERT INTO MEM_PRIMARYKEY1(MEM_NO,MEM_ID,MEM_PW,MEM_NAME)
       VALUES (NULL,'test2','test3','김밥'); --오류발생!! NOT NULL

FOREIGN KEY (외래키)

해당 컬럼에 다른 테이블에 존재하는 값만 들어와야 하는 컬럼에 부여하는 제약조건
외래키 값을 제공하는 부모테이블과 외래키가 포함된 자식테이블이 존재한다

EX) COMPANY계정에서 EMPLOYEE테이블 <--- DEPARTMENT테이블 관계인 경우

-> DEPT_CODE에는 DEPT_ID에 존재하는 값들만 들어올 수 있다.
=  FOREIGN KEY 제약조건(연결고리 역할)으로 다른 테이블과 관계를 형성 할 수 있다 (JOIN)

참조컬럼타입과 외래키 지정컬럼타입이 같을 경우 참조컬럼명 생략가능
자동으로 참조테이블의 PRIMARY KEY에 해당되는 컬럼이 참조컬럼으로 잡힘

컬럼명 자료형 REFERENCES 참조테이블명(참조컬럼명(생략가능))

FOREIGN KEY 삭제 옵션

자식테이블 생성 시(외래키 제약조건을 부여 시) 부모테이블의 데이터 삭제가 되었을 때

자식테이블 데이터 처리를 옵션으로 지정가능하다

ON DELETE SET NULL : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 NULL로 바꾸겠다.
ON DELETE CASCADE : 부모데이터를 삭제할 때 해당 데이터를 사용하는 자식데이터를 같이 삭제하겠다.
ON DELETE RESTRICTED : 삭제 제한(기본옵션)

외래키 예제) 

① 부모테이블과 자식테이블 생성

--부모테이블 만들기
--회원등급에 대한 데이터(등급코드,등급명)를 보관하는 테이블
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_PW 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(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(1,'user1','pwd1','김패스','G1');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(2,'user2','pwd2','김쓰루패스','G2');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(3,'user3','pwd3','김롱패스','G3');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(4,'user4','pwd3','김백패스','G4'); 
--오류발생!! parent key not found 참조하고 있는 컬럼에 없는 데이터를 넣어서
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(5,'user5','pwd3','김하이패스',NULL); --NULL값은 잘 들어감

② 부모테이블(MEM_GRADE)에서 데이터값이 삭제된다면?

--MEM_GRADE 테이블로부터 GRADE_CODE가 'G1'인 데이터만 지워보자
DELETE FROM MEM_GRADE WHERE GRADE_CODE='G1'; --오류발생!!
--child record found : 자식테이블중에 G1값을 참조해서 사용하고 있기 때문에 삭제할수 없다
--현재 외래키 제약조건 부여시 삭제에 대한 옵션을 따로 부여하지 않았다.
--> 기본적으로 삭제 제한 옵션이 걸려있다

③ 위의 자식테이블 삭제 후 삭제옵션 재설정하자

--1) ON DELETE SET NULL
CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE SET NULL, --외래키 지정
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(1,'user1','pwd1','김패스','G1');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(2,'user2','pwd2','김쓰루패스','G2');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(3,'user3','pwd3','김롱패스','G3');

SELECT * FROM MEM;

--부모테이블의 GRADE_CODE 중 'G1'인 데이터 삭제
DELETE FROM MEM_GRADE WHERE GRADE_CODE='G1'; --삭제되면서 자식테이블의 'G1'값이 NULL로 변경됨

--2)ON DELETE CASCADE
CREATE TABLE MEM(
    MEM_NO NUMBER PRIMARY KEY,
    MEM_ID VARCHAR2(20) NOT NULL UNIQUE,
    MEM_PW VARCHAR2(20) NOT NULL,
    MEM_NAME VARCHAR2(20) NOT NULL,
    GRADE_ID CHAR(2) REFERENCES MEM_GRADE(GRADE_CODE) ON DELETE CASCADE, --외래키 지정
    GENDER CHAR(3) CHECK (GENDER IN ('남','여')),
    PHONE VARCHAR2(20),
    EMAIL VARCHAR2(40),
    MEM_DATE DATE DEFAULT SYSDATE NOT NULL
);

INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(1,'user1','pwd1','김패스','G1');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(2,'user2','pwd2','김쓰루패스','G2');
INSERT INTO MEM(MEM_NO,MEM_ID,MEM_PW,MEM_NAME,GRADE_ID)
VALUES(3,'user3','pwd3','김롱패스','G3');

SELECT * FROM MEM;
--부모테이블(MEM_GRADE)에서 GRADE_CODE 'G1' 삭제 -- ON DELETE CASCADE
DELETE FROM MEM_GRADE WHERE GRADE_CODE='G1'; 
--삭제되면서 자식테이블의 'G1'값을 가진 튜플이 삭제됨

SELECT * FROM MEM_GRADE;