[Oracle] 20231031 [프로그램소스] - 20231031_01_HR

20231031_01_hr.sql

SELECT USER
FROM DUAL;
--==>> HR

/*

[정규화 : 테이블을 분리, 쪼개는 것, 나누는 것.]

--> DB서버의 메모리 낭비를 줄이기 위해서
--> 식별자를 갖게끔 테이블을 나눈다.


-- 규칙 : 제 1 정규화 → 제 2 정규화 → 제 3 정규화 → 제 4 정규화

--● 제 1 정규화 : 테이블에 값이 중복될 경우 이를 테이블로 구분.
-- EX) LG(서울) SK(서울) LG(부산) 사원명 사원 정보... → 2개의 테이블로 구분.

-->> 부모 테이블, 자식 테이블로 나눠진다. (부모 : 회사, 자식 : 사원)


-- 부모 테이블 : 참조 받는 컬럼 (PRIMARY KEY)
-- 자식 테이블 : 참조 하는 컬럼 (FORIGN KEY)


-- 부모 테이블의 참조 받는 컬럼에서의 특징
-- ① 중복 X
-- ② NOT NULL
-- [PRIMARY KEY]

-- 테이블 대상으로 단 하나 존재해야 한다. PRIMARY KEY

-- [ 없는 테이블은 가급적이면 만들지 않는 것이 좋다.]
-- 왜? : 전체 테이블로서 의미를 갖기 보다는 테이블의 값을 통해 뭐가 들어있는지를 확인.
--       식별자를 갖지 않게끔 테이블이 만들어지면(PRIMARY KEY가 없으면)
--       관리가 힘들어진다. (이름(OR 주민번호)이 없는 것과 마찬가지...)
--       주민등록번호처럼 중복되지 않는 고유한 KEY가 존재해야만 테이블의 관리가 편해진다.
--       특정 인물을 호출하고 싶을 때, 기준을 정해서 한 사람만 불러내기 어렵다.
--       한 사람을 특정할 수 있는 조건이 없다면(전화번호, 주민번호)
--       키가 몇인 사람, 급여가 몇인 사람, 몸무게가 몇인 사람... → 기준 잡기가 어렵다!

--       따라서, PRIMARY KEY가 없는 테이블은 존재 가능하지만 만들지 않는 편이 좋다.



--ⓐ PRIMARY KEY 단일 프라이머리키 : 제 1 정규화에서 끝난다.
--ⓑ COMPOSITE PRIMARY KEY 복합 프라이머리키 : 반드시 제 2 정규화를 거쳐야 한다.

-- 복합 PRIMARY KEY가 넘쳐나는 테이블 구성은 설계가 잘못된 것이다.
-- 이 테이블이 꼭 복합이어야 하는지를 확인하고 그게 아니라면 분리.




--● 제 2 정규화 : 식별자가 하나가 아님(PRIMARY KEY)
-- 식별자가 아닌 컬럼은 모든 PRIMARY KEY를 구성하고 있는 컬럼에 의존해야 하는데
-- 일부 PRIMARY KEY에만 의존적이라면 이를 테이블로 분리.

-- EX) 과목번호, 과목명. 교수번호, 교수자명 (과목번호와 교수번호가 복합 프라이머리키, 이를 각각으로 분리)



--● 제 3 정규화 : 대상이 식별자가 아닌 정규화.
--               식별자가 아닌 컬럼이 식별자가 아닌 컬럼에 의존할 경우 이를 분리

-- EX) 강의실 코드, 강의실 설명 (강의실 설명이 강의실 코드에만 의존적이므로, 새롭게 테이블을 만들어주자. 교수, 과목에 의존 X)



-- 관계형 데이터베이스에서의 관계들 3 가지

-- ① 1 대 다 : 하나의 부서 번호에 여러 사원 (부모 테이블, 자식 테이블) ○

-- ② 1 대 1  : 관계형 DB에서 가급적 피해야 하는 관계. (테이블을 나누지 않아도 될 가능성)

-- ③ 다 대 다 : 논리적으로는 성립하지만, 물리적 DB에 적용할 수 없는 함정이 존재.
--             EX) 고객 → 상품 사이의 주문 테이블
--                 학생 → 과목 사이의 수강신청 테이블


--● 제 4 정규화 : 다 대 다를 부숴뜨리는 정규화 (파생 테이블 생성)




-- 비 정규화, 역 정규화 : 정규화를 역행. 나눠진 테이블들을 결합.

-- 어떤 테이블에 얼마의 데이터가 들어오는지 디테일하게 예측 가능해야
-- 역정규화 했을 때 메모리 낭비가 더 심한지 아닌지를 판단 가능하므로 업무파악 중요.

-- A 테이블, B 테이블
-- 빈번하게 조회해야 할 컬럼의 테이블이 어디에 존재하는지 파악.


-- [제약조건] : PRIMARY KEY (중복X, NOT NULL), UNIQUE (중복X, NULL 허용)


*/


--■■■ CHECK(CK:C) ■■■--



-- 1. 컬럼에서 허용 가능한 데이터의 범위나 조건을 지정하기 위한 제약조건
--    컬럼에 입력되는 데이터를 검사하여 조건에 맞는 데이터만 입력될 수 있도록 한다.
--    또한, 컬럼에서 수정되는 데이터를 검사하여 조건에 맞는 데이터로 수정되는 것만
--    허용하는 기능을 수행하게 된다.


-- 2. 형식 및 구조
-- ① 컬럼 레벨의 형식
--    컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] CHECK(컬럼 조건)


-- ② 테이블 레벨의 형식
--    컬럼명 데이터타입,
--    컬럼명 데이터타입,
--    CONSTRAINT CONSTRAINT명 CHECK(컬럼 조건)


--○ CK 지정 실습(① 컬럼 레벨의 형식)
-- 테이블 생성

-- NUMBER(3) : -999 ~ 999
-- 이를 0~100까지만 가능하도록 조건을 설정하자. CHECK 사용.

CREATE TABLE TBL_TEST8
( COL1  NUMBER(5)       PRIMARY KEY
, COL2  VARCHAR2(30)    
, COL3  NUMBER(3)       CHECK(COL3 BETWEEN 0 AND 100) -- COL3의 허용 범위 : 0~100까지.
);
--==>> Table TBL_TEST8이(가) 생성되었습니다.


-- 데이터 입력

INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES (1, '박범구', 100);
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES (1, '엄재용', 100); --> 에러 발생 (첫 번째 COL1이 PRIMARY KEY이므로, 중복 X)
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES (2, '엄재용', 101); --> 에러 발생 (세 번째 COL3가 0~100까지만 입력되도록 CHECK 제약조건을 걸었으므로)
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES (2, '엄재용', -1);  --> 에러 발생 (CHECK 제약조건 0 ~ 100을 만족시키지 못함)
INSERT INTO TBL_TEST8(COL1, COL2, COL3) VALUES (2, '엄재용', 80);  


-- 확인
SELECT *
FROM TBL_TEST8;
--==>>
/*
[음수나, 100을 초과하는 데이터는 들어가지 않았음을 확인할 수 있다.]
COL1	COL2	    COL3
1	    박범구	100
2	    엄재용	80
*/

-- 커밋
COMMIT;
--==>> 커밋 완료.


SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST8';
--==>>
/*
[SEARCH_CONDITION : CHECK에서 설정한 COL3의 제약조건을 명시하고 있다.]

OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION    	DELETE_RULE
HR	    SYS_C007090	    TBL_TEST8	C	            COL3	    COL3 BETWEEN 0  AND 100	
HR	    SYS_C007091	    TBL_TEST8	P	            COL1		
       (오라클이 임의지정)
*/



--○ CK 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성

CREATE TABLE TBL_TEST9
( COL1  NUMBER(5)
, COL2  VARCHAR2(30)
, COL3  NUMBER(3)
, CONSTRAINT TEST9_COL1_PK PRIMARY KEY (COL1)
, CONSTRAINT TEST9_COL3_CK CHECK(COL3 BETWEEN 0 AND 100)
);
--==>> Table TBL_TEST9이(가) 생성되었습니다.


-- 데이터 입력

INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES (1, '박범구', 100);
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES (1, '엄재용', 100); --> 에러 발생 (첫 번째 COL1이 PRIMARY KEY이므로, 중복 X)
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES (2, '엄재용', 101); --> 에러 발생 (세 번째 COL3가 0~100까지만 입력되도록 CHECK 제약조건을 걸었으므로)
                                                                  --  (ORA-02290: check constraint (HR.TEST9_COL3_CK) violated)
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES (2, '엄재용', -1);  --> 에러 발생 (CHECK 제약조건 0 ~ 100을 만족시키지 못함)
INSERT INTO TBL_TEST9(COL1, COL2, COL3) VALUES (2, '엄재용', 80); 


-- 확인

SELECT *
FROM TBL_TEST9;


-- 커밋
COMMIT;
--==>> 커밋 완료


-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST9';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	        DELETE_RULE
HR	    TEST9_COL3_CK	TBL_TEST9	C	            COL3	    COL3 BETWEEN 0 AND 100	
HR	    TEST9_COL1_PK	TBL_TEST9	P	            COL1		
*/


--○ CK 지정 실습(③ 테이블 생성 이후 제약조건 추가)
-- 테이블 생성

CREATE TABLE TBL_TEST10
( COL1  NUMBER(5)
, COL2  VARCHAR2(30)
, COL3  NUMBER(3)
);  
--==>> Table TBL_TEST10이(가) 생성되었습니다.

-- 제약 조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST10';
--==>> 조회 결과 없음 (제약조건이 설정된 컬럼이 없다.)


ALTER TABLE TBL_TEST10
ADD (  CONSTRAINT TEST10_COL1_PK PRIMARY KEY (COL1)
     , CONSTRAINT TEST10_COL3_CK CHECK(COL3 BETWEEN 0 AND 100) );
--==>> Table TBL_TEST10이(가) 변경되었습니다.



-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST10';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION        	DELETE_RULE
HR	    TEST10_COL1_PK	TBL_TEST10	P	            COL1		
HR	    TEST10_COL3_CK	TBL_TEST10	C	            COL3	    COL3 BETWEEN 0 AND 100	
*/



-- 테이블 생성

CREATE TABLE TBL_TESTMEMBER
( SID   NUMBER
, NAME  VARCHAR2(30)
, SSN   CHAR(14)          -- 입력 형태 → 'YYMMDD-NNNNNNN' (14자리)
, TEL   VARCHAR2(40)
);
--==>> Table TBL_TESTMEMBER이(가) 생성되었습니다.


--○ TBL_TESTMEMBER 테이블의 SSN 컬럼(주민등록번호 컬럼)에서
--   데이터 입력이나 수정 시, 성별이 유효한 데이터만 입력될 수 있도록
--   체크 제약조건을 추가할 수 있도록 한다.
--   (→ 주민번호 특정 자리에 입력 가능한 데이터를 1, 2, 3, 4 만 가능하도록 처리)
--   또한, SID 컬럼에는 PRIMARY KEY 제약조건을 설정할 수 있도록 한다.


-- #1 SID 컬럼에 PRIMARY KEY 제약조건 설정

ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TMEMBER_SID_PK PRIMARY KEY(SID)
    , CONSTRAINT TMEMBER_SSN_CK CHECK(SUBSTR(SSN, 8, 1) IN ('1', '2', '3', '4')) );

--★ CHECK() 괄호 안에는 WHERE 절의 조건을 적는 것처럼 제약조건을 설정해 주면 된다.
--★ WHERE의 경우 조회하는 조건이었다면, CHECK는 입력 가능한 데이터의 조건이다.

     
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TESTMEMBER';


INSERT INTO TBL_TESTMEMBER(SID, SSN) VALUES(1, '001016-8123456');
--> 에러 발생
-- (ORA-02290: check constraint (HR.TMEMBER_SSN_CK) violated)
-- CHECK 조건인 SSN의 8번째 자리 수가 '1', '2', '3', '4' 가 아니므로 데이터가 입력되지 않는다는 것을 확인.


------------------------------------------------------
--○ [제약 조건 삭제]

ALTER TABLE '테이블명' DROP CONSTRAINT '제약조건명';

--○ [컬럼 삭제]

ALTER TABLE '테이블명' DROP COLUMN '컬럼명';
------------------------------------------------------



-- 제약조건 추가

ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
    , CONSTRAINT TESTMEMBER_SSN_CK CHECK(주민번호 8번째 자리 1개가 '1' 또는 '2' 또는 '3' 또는 '4') );


ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
    , CONSTRAINT TESTMEMBER_SSN_CK CHECK(SUBSTR(SSN, 8, 1)가 '1' 또는 '2' 또는 '3' 또는 '4') );


ALTER TABLE TBL_TESTMEMBER
ADD ( CONSTRAINT TESTMEMBER_SID_PK PRIMARY KEY(SID)
    , CONSTRAINT TESTMEMBER_SSN_CK CHECK(SUBSTR(SSN, 8, 1) IN ('1', '2', '3', '4') ));
--==>> Table TBL_TESTMEMBER이(가) 변경되었습니다.



-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TESTMEMBER';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	    CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	                DELETE_RULE
HR	    TMEMBER_SID_PK	TBL_TESTMEMBER	P	            SID		
HR	    TMEMBER_SSN_CK	TBL_TESTMEMBER	C	            SSN	        SUBSTR(SSN, 8, 1) IN ('1', '2', '3', '4')	
*/  


-- 데이터 입력 테스트

INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(1, '이윤수', '950106-1234567', '010-1111-1111');

INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(2, '박나영', '990208-2234567', '010-2222-2222');

INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(3, '최혜인', '070811-4234567', '010-3333-3333');

INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(4, '길현욱', '090211-3234567', '010-4444-4444');



INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(5, '정현욱', '000220-5234567', '010-5555-5555');    --> 에러 발생 (주민번호 8번째 숫자가 '5'이므로 CHECK 제약조건 ('1','2','3','4')에 맞지 않음!

INSERT INTO TBL_TESTMEMBER(SID, NAME, SSN, TEL)
VALUES(5, '정현욱', '000220-6234567', '010-5555-5555');    --> 에러 발생 (주민번호 8번째 숫자가 '6'이므로 CHECK 제약조건 ('1','2','3','4')에 맞지 않음!


-- 확인

SELECT *
FROM TBL_TESTMEMBER;
--==>>
/*
SID	NAME    	SSN	            TEL
1	이윤수	950106-1234567	010-1111-1111
2	박나영	990208-2234567	010-2222-2222
3	최혜인	070811-4234567	010-3333-3333
4	길현욱	090211-3234567	010-4444-4444
*/

-- 커밋

COMMIT;
--==>> 커밋 완료.



-- 내가 가진 컬럼을, 외부의 테이블에서 갖는 FOREIGN KEY로 사용할 수 있도록 하는 설정은
-- 까다롭다.

-- 제약 조건이 복잡하다. 외부 테이블이 어떤 형식으로 구성되어있는지 파악해야 하므로.

-- EMP, DEPT (DEPTNO : 부서번호 (10, 20, 30, 40)
-- DEPT 테이블의 부서번호 값 중 하나만 EMP가 INSERT 되어야 함. 여러 개가 들어가면 안 된다.

-- 맨 처음에 부모 테이블이 만들어져 있어야 자식 테이블에서 참조 가능.

-- REFERENCES 참조하겠다
-- 참조테이블명(참조컬럼명)을...


--■■■ FOREIGN KEY(FK:F:R) ■■■--

-- 1. 참조 키(R) 또는 외래 키(FK:F)는 두 테이블의 데이터 간 연결을 설정하고
--    강제 적용시키는데 사용되는 열이다.

--    한 테이블의 기본 키 값이 있는 열을
--    다른 테이블에 추가하면 테이블 간 연결을 설정할 수 있다
--    이 때, 두 번째 테이블에 추가되는 열이 외래키가 된다.


-- 2. 부모 테이블(참조받는 컬럼이 포함된 테이블)이 먼저 생성된 후
--    자식 테이블(참조하는 컬럼이 포함된 테이블)이 생성되어야 한다.
--    이 때, 자식 테이블에 FOREIGN KEY 제약조건이 설정된다.

-- 3. 형식 및 구조
-- ①컬럼 레벨의 형식
-- 컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명]
--                   REFERENCES 참조테이블명(참조컬럼명)
--                   [ON DELETE CASCADE | ON DELETE SET NULL]   → 추가 옵션

--② 테이블 레벨의 형식
-- 컬럼명 데이터타입,
-- 컬럼명 데이터타입,
-- CONSTRAINT COSTRAINT명 FOREIGN KEY(컬럼명)
--           REFERENCES 참조테이블명(참조컬럼명)
--           [ON DELETE CASCADE | ON DELETE SET NULL]   → 추가 옵션


--※ FOREIGN KEY 제약조건을 설정하는 실습을 진행하기 위해서는
--   부모 테이블의 생성 작업을 먼저 수행해야 한다.
--   그리고 이 때, 부모테이블에는 반드시 PK 또는 UK 제약조건이
--   설정된 컬럼이 존재해야 한다.


-- 부모 테이블 생성
CREATE TABLE TBL_JOBS
( JIKWI_ID      NUMBER
, JIKWI_NAME    VARCHAR2(30)
, CONSTRAINT JOBS_ID_PK PRIMARY KEY(JIKWI_ID)
);
--==>> Table TBL_JOBS이(가) 생성되었습니다.


-- 부모 테이블에 데이터 입력
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(1, '사원');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(2, '대리');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(3, '과장');
INSERT INTO TBL_JOBS(JIKWI_ID, JIKWI_NAME) VALUES(4, '부장');
--==>> 1 행 이(가) 삽입되었습니다. * 4


-- 확인

SELECT *
FROM TBL_JOBS;
--==>>
/*
JIKWI_ID	JIKWI_NAME
1	        사원
2	        대리
3	        과장
4	        부장
*/


-- 커밋
COMMIT;
--==>> 커밋 완료.



--○ FK 지정 실습(① 컬럼 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_EMP1
( SID       NUMBER          PRIMARY KEY
, NAME      VARCHAR2(30)
, JIKWI_ID  NUMBER          REFERENCES TBL_JOBS(JIKWI_ID) -- JOBS 테이블이 갖고 있는 JIKWI_ID를 참조한다.
                                                -------- TBL_EMP1의 컬럼이 아니라, TBL_JOBS의 컬럼.
);
--==>> Table TBL_EMP1이(가) 생성되었습니다.


-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>>
/*
[CONSTRAINT_TYPE 'P' : PRIMARY KEY]
[CONSTRAINT_TYPE 'R' : REFERENCES]

OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007099	    TBL_EMP1    	P	            SID		
HR	    SYS_C007100	    TBL_EMP1    	R	            JIKWI_ID		NO ACTION
*/  

-- [NO ACTION]


-- 데이터 입력

                                             -- 사원번호     직위ID
INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES (1, '노은하', 1);

INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES (2, '박가영', 2);

INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES (3, '채다선', 3);

INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES (4, '김수환', 4);


INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES (5, '김다슬', 5); --> 에러 발생 TBL_JOBS의 JIKWI_ID에 '5'라는 값이 없으므로 에러가 발생한다! (1~4까지만 가능)

INSERT INTO TBL_EMP1(SID, NAME, JIKWI_ID) VALUES (5, '김다슬', 1); 

-- JIKWI_ID를 입력하지 않더라도 입력 가능하다!! (NULL 가능! 제약조건 없음!)
-- PRIMARY KEY가 아니다! (비워두는 것 가능하지만, 값을 입력할 때는 제대로 된 값만 입력하도록!)
INSERT INTO TBL_EMP1(SID, NAME) VALUES (6, '오수경');


-- 확인

SELECT *
FROM TBL_EMP1;
--==>>
/*
SID	NAME    	JIKWI_ID
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	4
5	김다슬	1
6	오수경	
*/


COMMIT;
--==>> 커밋 완료.



--○ FK 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성

-- 테이블 제작할 때는 'FOREIGN KEY' 키워드 등장!
CREATE TABLE TBL_EMP2
( SID       NUMBER
, NAME      VARCHAR2(30)
, JIKWI_ID  NUMBER
, CONSTRAINT EMP2_SID_PK PRIMARY KEY(SID)
, CONSTRAINT EMP2_JIKWI_ID_FK FOREIGN KEY(JIKWI_ID) -- TBL_EMP2의 JIKWI_ID 컬럼.
             REFERENCES TBL_JOBS(JIKWI_ID)          -- TBL_JOBS의  JIKWI_ID 컬럼.
);
--==>> Table TBL_EMP2이(가) 생성되었습니다.

-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP2';

--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    EMP2_SID_PK	TBL_EMP2        	P	            SID		
HR	    EMP2_JIKWI_ID_FK	TBL_EMP2	    R	            JIKWI_ID		NO ACTION
*/








--○ FK 지정 실습(③ 테이블 생성 이후 제약조건 추가)

-- 테이블 생성

CREATE TABLE TBL_EMP3
( SID           NUMBER
, NAME          VARCHAR2(30)
, JIKWI_ID      NUMBER
);
--==>> Table TBL_EMP3이(가) 생성되었습니다.


-- 제약조건 추가

ALTER TABLE TBL_EMP3
ADD ( CONSTRAINT EMP3_SID_PK PRIMARY KEY (SID)
    , CONSTRAINT EMP3_JIKWI_ID_FK FOREIGN KEY (JIKWI_ID)
                 REFERENCES TBL_JOBS(JIKWI_ID) );
--==>> Table TBL_EMP3이(가) 변경되었습니다.

-- 제약 조건은 수정 X!!
-- 제약 조건을 제거하고 다시 설정하는 형태로 제약 조건을 관리하고 운영하도록 하자.


-- 제약 조건 제거

ALTER TABLE TBL_EMP3
DROP CONSTRAINT EMP3_JIKWI_ID_FK;
--==>> Table TBL_EMP3이(가) 변경되었습니다.


-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP3';
--==>> 
/*
[JIKWI_ID의 제약조건이 삭제되어 P만 남음.]
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    EMP3_SID_PK	    TBL_EMP3	    P	            SID		
*/


ALTER TABLE TBL_EMP3
ADD CONSTRAINT EMP3_JIKWI_ID_FK FOREIGN KEY (JIKWI_ID)
               REFERENCES TBL_JOBS(JIKWI_ID);
--==>> Table TBL_EMP3이(가) 변경되었습니다.




-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP3';
--==>>
/*
OWNER	CONSTRAINT_NAME	    TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    EMP3_SID_PK	        TBL_EMP3    	P	            SID		
HR	    EMP3_JIKWI_ID_FK	TBL_EMP3    	R   	            JIKWI_ID	NO ACTION
*/



-- ON DELETE CASCADE, ON DELETE SET NULL -> 위험한 옵션. 웬만하면 절대 쓰지 말자.

-- 4. FOREIGN KEY 생성 시 주의사항
--    참조하고자 하는 부모 테이블을 먼저 생성해야 한다.
--    참조하고자 하는 컬럼이 PRIMARY KEY 또는 UNIQUE 제약조건이 설정되어 있어야 한다.
--    테이블 사이에 PRIMARY KEY 와 FOREIGN KEY 가 정의되어 있으면
--    PRIMARY KEY 제약조건이 설정된 데이터 삭제 시
--    FOREIGN KEY 컬럼에 그 값이 입력되어 있는 경우 삭제되지 않는다.
--    (즉, 자식 테이블에 참조하는 레코드가 존재할 경우
--    부모 테이블의 참조받는 해당 레코드는 삭제할 수 없다는 것이다.)
--    단, FK 설정 과정에서 『ON DELETE CASCADE』나 『ON DELETE SET NULL』 옵션을
--    사용하여 설정한 경우에는 삭제가 가능하다.
--    또한, 부모 테이블을 제거하기 위해서는 자식 테이블을 먼저 제거해야 한다.



-- 부모 테이블

SELECT *
FROM TBL_JOBS;
--==>>
/*
JIKWI_ID	JIKWI_NAME
1	        사원
2	        대리
3	        과장
4	        부장
*/



-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
SID	NAME    	JIKWI_ID
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	4
5	김다슬	1
6	오수경	
*/



-- 부모 테이블 제거 시도

DROP TABLE TBL_JOBS;
--==>> 에러 발생
--     (ORA-02449: unique/primary keys in table referenced by foreign keys)
-- TBL_JOBS 테이블은 현재 테이블을 제거할 수 없다.

-- 부모 테이블의 부장 직위 데이터 삭제 시도

SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID = 4;
--==>> 4	부장


DELETE
FROM TBL_JOBS
WHERE JIKWI_ID = 4;
--==>> 에러 발생
--     (ORA-02292: integrity constraint (HR.SYS_C007100) violated - child record found)


-- 김수환 부장의 직위를 사원으로 변경



UPDATE TBL_EMP1
SET JIKWI_ID = 1
WHERE SID = 4;
--==>> 1 행 이(가) 업데이트되었습니다.

-- 확인
SELECT *
FROM TBL_EMP1;
--==>>
/*
SID	NAME	    JIKWI_ID
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	1
5	김다슬	1
6	오수경	
*/

-- 커밋
COMMIT;
--==>> 커밋 완료.

-- 수환 부장 → 수환 사원!!
-- 이렇게 되면... 자식 테이블 TBL_EMP1에 부장 직위 번호 '4'를 참조하는 레코드가 없다.

-- 부모 테이블(TBL_JOBS)의 부장 데이터를 참조하고 있는
-- 자식 테이블(TBL_EMP1)의 데이터가 존재하지 않는 상황


-- 이와 같은 상황에서 부모 테이블(TBL_JOBS)의
-- 부장 데이터 삭제

DELETE
FROM TBL_JOBS
WHERE JIKWI_ID = 4;
--==>> 1 행 이(가) 삭제되었습니다.
-- 부장 데이터를 참조하는 자식 테이블 레코드가 존재하지 않으므로, 정상적으로 삭제됨을 확인!!



-- 확인
SELECT *
FROM TBL_JOBS;
--==>>
/*
JIKWI_ID	JIKWI_NAME
1	사원
2	대리
3	과장
*/

-- 커밋
COMMIT;
--==>> 커밋 완료.



--※ 부모 테이블의 데이터를 자유롭게(?) 삭제하기 위해서는
--   『ON DELETE CASCADE』 옵션 지정이 필요하다.


-- TBL_EMPT 테이블(자식 테이블)에서 FK 제약조건을 제거한 후
-- CASCADE 옵션을 포함하여 다시 FK 제약조건을 설정한다.

-- EMP1 의 제약조건 제거

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';


-- #1 FK 제약조건 제거.
ALTER TABLE TBL_EMP1
DROP CONSTRAINT SYS_C007100;
--==>> Table TBL_EMP1이(가) 변경되었습니다.



-- #2 FK 제약조건 다시 부여

ALTER TABLE TBL_EMP1
ADD CONSTRAINT EMP1_JIKWI_ID_FK FOREIGN KEY (JIKWI_ID)
               REFERENCES TBL_JOBS(JIKWI_ID)
               ON DELETE CASCADE;
--==>> Table TBL_EMP1이(가) 변경되었습니다.




-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>> 
/*
OWNER	CONSTRAINT_NAME     	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007099	        TBL_EMP1    	P	            SID		
HR	    EMP1_JIKWI_ID_FK	TBL_EMP1	    R	            JIKWI_ID		CASCADE
(# 이미 제거하고 바꾼 상태여서 CONSTRAINT_NAME이 내가 지정한 이름임.)
*/


-- 제약조건 제거

ALTER TABLE TBL_EMP1
DROP CONSTRAINT SYS_C007029; -- R(FOREIGN KEY 의 CONSTRAINT_NAME)
--==>> Table TBL_EMP1이(가) 변경되었습니다.



-- 제약조건 제거 이후 다시 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	DELETE_RULE
HR	    SYS_C007099	    TBL_EMP1    	P	            SID		

*/



-- 『ON DELETE CASCADE』 옵션이 포함된 내용으로 제약조건 다시 지정

ALTER TABLE TBL_EMP1
ADD CONSTRAINT EMP1_JIKWI_ID_FK FOREIGN KEY (JIKWI_ID)
               REFERENCES TBL_JOBS(JIKWI_ID)
               ON DELETE CASCADE;
--==>> Table TBL_EMP1이(가) 변경되었습니다.



-- 제약조건 생성 이후 다시 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_EMP1';
--==>>
/*
OWNER	CONSTRAINT_NAME	    TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    SYS_C007099	        TBL_EMP1    	P	            SID		
HR	    EMP1_JIKWI_ID_FK	TBL_EMP1    	R	            JIKWI_ID		                CASCADE

-- DELETE_RULE 에 CASCADE 가 설정되어 있는것을 확인!!
*/

/*
-- 하나의 부모 테이블, 여러 개의 자식 테이블.
-- CASCADE 옵션을 설정하게 되면... 
-- 부모의 데이터 '부장' ID=4 를 제거했을 떄
-- 부모의 데이터 ('부장' ID = 4)를 참조하는
-- 모든 자식 테이블들의 데이터가 삭제된다.
               
-- 시간을 멈추고, 자식 테이블에서 부장 ID=4 를 참조하는 레코드를 모두 제거한다.
-- 하나라도 남아있을 경우 부모 테이블에서 제거가 되지 않는다.

-- 자식 테이블의 레코드를 모두 제거한 이후에, 현재로 돌아와서 부모의 레코드를 삭제한다.
*/
               
               
--※ CASCADE 옵션을 지정한 후에는
--  참조받고 있느 부모 테이블의 데이터를
--  언제든지 자유롭게 삭제하는 것이 가능하다.
--  단,... 부모 테이블의 데이터가 삭제될 경우...
--  이를 참조하는 자식 테이블의 데이터도 모~~~~~~~~~ 두 함께 삭제된다.

               


-- 부모 테이블

SELECT *
FROM TBL_JOBS;
--==>>
/*
JIKWI_ID	JIKWI_NAME
1	        사원
2	        대리
3	        과장
4	        부장
*/



-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
SID	NAME    	JIKWI_ID
1	노은하	1
2	박가영	2
3	채다선	3
4	김수환	1
5	김다슬	1
6	오수경	
*/


-- 부모 테이블(TBL_JOBS)에서 과장 데이터 삭제


SELECT *
FROM TBL_JOBS
WHERE JIKWI_ID = 3;
--==>> 3	과장



DELETE
FROM TBL_JOBS
WHERE JIKWI_ID = 3;          
--==>> 1 행 이(가) 삭제되었습니다.
-- 다선 사원이 과장 직위를 참조함에도 불구하고, 바로 부모 테이블에서 삭제가 이루어진다.   
-- CASCADE 옵션을 적용했으므로...
               
    
SELECT *
FROM TBL_EMP1;    
--==>>
/*
SID	NAME    	JIKWI_ID
1	노은하	1
2	박가영	2
4	김수환	1
5	김다슬	1
6	오수경	
*/
               
               
-- 부모 테이블(TBL_JOBS)에서 사원 데이터 삭제

DELETE
FROM TBL_JOBS
WHERE JIKWI_ID = 1;
--==>> 1 행 이(가) 삭제되었습니다. (내부적으로는 더 많은 행이 삭제되었다.)




SELECT *
FROM TBL_JOBS;
--==>>
/*
JIKWI_ID	JIKWI_NAME
2	        대리
*/



-- 자식 테이블
SELECT *
FROM TBL_EMP1;
--==>>
/*
[직위가 사원인 데이터들이 모두삭제되었다.]
SID	NAME	    JIKWI_ID
2	박가영	2
6	오수경	
*/


DROP TABLE TBL_EMP2;
--==>> Table TBL_EMP2이(가) 삭제되었습니다.


DROP TABLE TBL_EMP3;
--==>> Table TBL_EMP3이(가) 삭제되었습니다.


-- 아직 TBL_EMP1 테이블을 제거하지 않았다!
DROP TABLE TBL_JOBS;
--==>> 에러 발생
--     (ORA-02449: unique/primary keys in table referenced by foreign keys)



DROP TABLE TBL_EMP1;
--==>> Table TBL_EMP1이(가) 삭제되었습니다.



-- TBL_EMP1 테이블까지 모두 삭제하고 나자, 제대로 부모 테이블이 제거되는 것을 확인할 수 있다.
DROP TABLE TBL_JOBS;
--==>> Table TBL_JOBS이(가) 삭제되었습니다.


-- 테이블을 제거할 때는, 참조하는 데이터가 있다 없다가 중요한 것이 아니라
-- 자식 테이블이 있느냐 없느냐가 중요한 것이므로 이와 같은 현상이 발생한 것이다.

-- JOBS 테이블의 자식 테이블이 존재하면 아무리 삭제하려고 해도 지워지지 않음.

/*
테이블 컬럼 수정
테이블의 컬럼을 수정할 때에는

MODIFY 명령어를 이용하여 다음과 같이 작성합니다.

ALTER TABLE [테이블명] MODIFY [컬럼명] [데이터타입(길이)];


이 때, 컬럼의 데이터 타입을 변경할건지, 아니면 컬럼의 길이를 변경할건지에 따라

각각 상황에 맞게 입력해주면 됩니다.

ALTER TABLE User MODIFY User_NAME VARCHAR2(10);
*/




-- ■■■ NOT NULL(NN:CK:C) ■■■--

-- 1. 테이블에서 지정한 컬럼의 데이터가 NULL 인 상태를 갖지 못하도록 하는 제약조건.


-- 2. 형식 및 구조
-- ① 컬럼 레벨의 형식
-- 컬럼명 데이터타입 [CONSTRAINT CONSTRAINT명] NOT NULL

-- ② 테이블 레벨의 형식
-- 컬럼명 데이터타입,
-- 컬럼명 데이터타입,
-- CONSTRAINT CONSTRAINT명 CHECK(컬럼명 IS NOT NULL)


-- 3. 기존에 생성되어 있는 테이블에 NOT NULL 제약조건을 추가할 경우
--    ADD 보다 MODIFY 절이 더 많이 사용된다.
--    ALTER TABLE 테이블명
--    MODIFY 컬럼명 데이터타입 NOT NULL;


-- 4. 기존 테이블에 데이터가 이미 들어있지 않은 컬럼(→ NULL인 상태)을
--    NOT NULL 제약조건을 갖도록 수정하는 경우에는 에러 발생한다.



--○ NOT NULL 지정 실습(① 컬럼 레벨의 형식)
-- 테이블 생성

CREATE TABLE TBL_TEST11
( COL1  NUMBER(5)       PRIMARY KEY
, COL2  VARCHAR2(30)    NOT NULL
);
--==>> Table TBL_TEST11이(가) 생성되었습니다.

-- 데이터 입력
INSERT INTO TBL_TEST11(COL1, COL2) VALUES (1, 'TEST');
INSERT INTO TBL_TEST11(COL1, COL2) VALUES (2, 'ABCD');
INSERT INTO TBL_TEST11(COL1, COL2) VALUES (3, NULL); --> 에러 발생 (COL2 컬럼은 NULL 을 허용하지 않으므로)
                                                     -- (ORA-01400: cannot insert NULL into ("HR"."TBL_TEST11"."COL2")
INSERT INTO TBL_TEST11(COL1) VALUES (4);             --> 에러 발생 (COL2 컬럼은 NULL 허용 X)


-- 확인
SELECT *
FROM TBL_TEST11;
--==>>
/*
COL1	COL2
1	    TEST
2	    ABCD
*/


-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST11';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	     DELETE_RULE
HR	    SYS_C007107	    TBL_TEST11	C	            COL2	        "COL2" IS NOT NULL	
HR	    SYS_C007108	    TBL_TEST11	P	            COL1		
*/


--○ NOT NULL 지정 실습(② 테이블 레벨의 형식)
-- 테이블 생성
CREATE TABLE TBL_TEST12
( COL1  NUMBER(5) 
, COL2  VARCHAR2(30) 
, CONSTRAINT TEST12_COL1_PK PRIMARY KEY (COL1)
, CONSTRAINT TEST12_COL2_NN CHECK(COL2 IS NOT NULL)
);
--==>> Table TBL_TEST12이(가) 생성되었습니다.



-- 제약조건 확인

SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST12';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    TEST12_COL2_NN	TBL_TEST12	C	            COL2	        COL2 IS NOT NULL	
HR	    TEST12_COL1_PK	TBL_TEST12	P	            COL1		
*/






--○ NOT NULL 지정 실습(③ 테이블 생성 이후 제약조건 추가)
-- 테이블 생성

CREATE TABLE TBL_TEST13
( COL1  NUMBER(5)
, COL2  VARCHAR2(30)
);
--==>> Table TBL_TEST13이(가) 생성되었습니다.


-- 제약 조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST13';
--==>> 조회 결과 없음


-- 제약 조건 추가
ALTER TABLE TBL_TEST13
ADD ( CONSTRAINT TEST13_COL1_PK PRIMARY KEY (COL1)
    , CONSTRAINT TEST13_COL2_NN CHECK(COL2 IS NOT NULL) );
--==>> Table TBL_TEST13이(가) 변경되었습니다.


    

-- 제약 조건 확인
SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME = 'TBL_TEST13';
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    TEST13_COL1_PK	TBL_TEST13	P	            COL1		
HR	    TEST13_COL2_NN	TBL_TEST13	C	            COL2	        COL2 IS NOT NULL	
*/

-- 컬럼 레벨에서 NOT NULL 조건 추가 : "COL2" IS NOT NULL
-- 테이블 레벨에서 NOT NULL 조건 추가 : COL2 IS NOT NULL
-- 테이블 생성 후 제약조건 생성시 테이블 레벨 조건 추가와 같음.



-- ※ NOT NULL 제약조건만 TBL_TEST13 테이블의 COL2 에 추가하는 경우
--    다음과 같은 방법을 사용하는 것도 가능하다.

ALTER TABLE TBL_TEST13
MODIFY COL2 NOT NULL;
--==>> Table TBL_TEST13이(가) 변경되었습니다.



-- 컬럼 레벨에서 NOT NULL 제약조건을 지정한 테이블(TBL_TEST11)
DESC TBL_TEST11;
--==>>
/*
이름   널?       유형           
---- -------- ------------ 
COL1 NOT NULL NUMBER(5)    
COL2 NOT NULL VARCHAR2(30) 
*/


-- 테이블 레벨에서 NOT NULL 제약조건을 지정한 테이블(TBL_TEST12)
DESC TBL_TEST12;
--==>>
/*
---- -------- ------------ 
COL1 NOT NULL NUMBER(5)    
COL2          VARCHAR2(30) 
*/

-- 두 번째 컬럼 COL2에 제약조건을 부여했음에도 불구하고,
-- NOT NULL 이 COL1에 들어가있다...
-- 제약조건을 확인하는 VIEW를 통해서만 NOT NULL을 확인할 수 있다.

-- NOT NULL 은 컬럼 레벨에서 지정하는 것이 더 바람직하다.
-- 테이블 레벨에서 지정해 보니... DESC 결과가 올바르지 못하다.



-- 테이블 생성 이후 ADD 를 통해 NOT NULL 제약조건을 추가하였으며
-- 여기에 더하여, MODIFY 절을 통해 NOT NULL 제약조건을 추가한 테이블(TBL_TEST13)

DESC TBL_TEST13;
--==>>
/*
이름   널?       유형           
---- -------- ------------ 
COL1 NOT NULL NUMBER(5)    
COL2 NOT NULL VARCHAR2(30) 

*/


SELECT *
FROM VIEW_CONSTCHECK
WHERE TABLE_NAME IN ('TBL_TEST11', 'TBL_TEST12', 'TBL_TEST13');
--==>>
/*
OWNER	CONSTRAINT_NAME	TABLE_NAME	CONSTRAINT_TYPE	COLUMN_NAME	SEARCH_CONDITION	    DELETE_RULE
HR	    SYS_C007107	    TBL_TEST11	C	            COL2	       "COL2" IS NOT NULL	
HR	    SYS_C007108	    TBL_TEST11	P	            COL1		

HR	    TEST12_COL2_NN	TBL_TEST12	C	            COL2        	COL2 IS NOT NULL	
HR	    TEST12_COL1_PK	TBL_TEST12	P	            COL1		

HR	    TEST13_COL1_PK	TBL_TEST13	P	            COL1		
HR	    TEST13_COL2_NN	TBL_TEST13	C	            COL2        COL2 IS NOT NULL
HR	    SYS_C007116	    TBL_TEST13	C	            COL2	       "COL2" IS NOT NULL	
*/


-- NOT NULL 을 할 때는 컬럼레벨로 하면서
-- MODIFY를 이용해서 이름을 넣어주는 것이 좋다.
-- 그러나 MODIFY에는 이름을 지어주는 과정을 잘 하지 않기 때문에
--★★★★ 컬럼 레벨에서 CONSTRAINT_NAME을 지정해 주는 것이 좋다!




--■■■ DEFAULT 표현식 ■■■--

-- 1. INSERT 와 UPDATE 문에서
--    특정 값이 아닌 기본 값을 입력하도록 처리할 수 있다.

-- 2. 형식 및 구조
--    컬럼명 데이터타입 DEFAULT 기본값

-- 3. INSERT 명령 시 해당 컬럼에 입력될 값을 할당하지 않거나
--    DEFAULT 키워드를 활용하여 기본으로 설정된 값을 입력하도록 할 수 있다.

-- 4. DEFAULT 키워드와 다른 제약(NOT NULL 등) 표기가 함께 사용되어야 하는 경우
--    DEFAULT 키워드를 먼저 표기(작성)할 것을 권장한다.



--○ DEFAULT 표현식 적용 실습
-- 테이블 생성
CREATE TABLE TBL_BBS                            -- 게시판 테이블 생성
( SID           NUMBER          PRIMARY KEY     -- 게시물 번호 → 식별자 → 자동 증가
, NAME          VARCHAR2(20)                    -- 게시물 작성자
, CONTENTS      VARCHAR2(200)                   -- 게시물 내용
, WRITEDAY      DATE            DEFAULT SYSDATE -- 게시물 작성일
, COUNTS        NUMBER          DEFAULT 0       -- 게시물 조회수
, COMMENTS      NUMBER          DEFAULT 0       -- 게시물 댓글 개수
);
--==>> Table TBL_BBS이(가) 생성되었습니다.

-- DEFAULT 를 0으로 지정해 주지 않으면, 값을 입력해 주지 않았을 경우 NULL 이 들어가므로...
-- 조회수를 1씩 증가시키거나 댓글 수를 1씩 증가시키더라도 NULL + 1 이므로 결과가 NULL
-- 이를 방지하기 위해 DEFAULT 0!!



--※ SID 를 자동 증가 값으로 운영하려면 시퀀스 객체가 필요하다.
-- 자동으로 입력되는 컬럼은 사용자의 입력 항목에서 제외시킬 수 있다.


-- 시퀀스 생성
CREATE SEQUENCE SEQ_BBS
NOCACHE;    -- 캐시만 부여하지 않음.
--==>> Sequence SEQ_BBS이(가) 생성되었습니다.

-- 날짜 관련 세션 설정 변경

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
--==>> Session이(가) 변경되었습니다.

-- 게시물 작성

INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '김다슬', '오라클 DEFAULT 표현식을 실습중입니다.'
     , TO_DATE('2023-10-31 14:39:10', 'YYYY-MM-DD HH24:MI:SS'), 0, 0);
--==>> 1 행 이(가) 삽입되었습니다.


INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '김다슬', '오라클 DEFAULT 표현식을 실습중입니다.', SYSDATE, 0, 0);
--==>> 1 행 이(가) 삽입되었습니다.



INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '노은하', '계속 실습중입니다.', SYSDATE, 0, 0);
--==>> 1 행 이(가) 삽입되었습니다.



INSERT INTO TBL_BBS(SID, NAME, CONTENTS, WRITEDAY, COUNTS, COMMENTS)
VALUES(SEQ_BBS.NEXTVAL, '문정환', '열심히 실습중입니다.', SYSDATE, DEFAULT, DEFAULT);
--==>> 1 행 이(가) 삽입되었습니다.


INSERT INTO TBL_BBS(SID, NAME, CONTENTS)
VALUES(SEQ_BBS.NEXTVAL, '이윤수', '무진장 실습중입니다.');
--==>> 1 행 이(가) 삽입되었습니다.



-- 확인
SELECT *
FROM TBL_BBS;
--==>>
/*
SID	NAME    	CONTENTS	                            WRITEDAY            	COUNTS COMMENTS
1	김다슬	오라클 DEFAULT 표현식을 실습중입니다.	2023-10-31 14:39:10	0	     0
2	김다슬	오라클 DEFAULT 표현식을 실습중입니다.	2023-10-31 14:45:43	0	     0
3	노은하	계속 실습중입니다.	                2023-10-31 14:46:14	0	     0
4	문정환	열심히 실습중입니다.             	    2023-10-31 14:47:10	0	     0
5	이윤수	무진장 실습중입니다.	                2023-10-31 14:48:05	0	     0
*/



--○ DEFAULT 표현식 조회(확인)

SELECT *
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'TBL_BBS';
--==>>
/*
TABLE_NAME	COLUMN_NAME	        DATA_TYPE	DATA_TYPE_MOD	DATA_TYPE_OWNER	DATA_LENGTH	DATA_PRECISION	DATA_SCALE	NULLABLE	COLUMN_ID	DEFAULT_LENGTH	DATA_DEFAULT	NUM_DISTINCT	LOW_VALUE	HIGH_VALUE	DENSITY	NUM_NULLS	NUM_BUCKETS	LAST_ANALYZED	SAMPLE_SIZE	CHARACTER_SET_NAME	CHAR_COL_DECL_LENGTH	GLOBAL_STATS	USER_STATS	AVG_COL_LEN	CHAR_LENGTH	CHAR_USED	V80_FMT_IMAGE	DATA_UPGRADED	HISTOGRAM
TBL_BBS	    SID	NUMBER			    22			N	1													NO	NO		0		NO	YES	NONE
TBL_BBS     	NAME	VARCHAR2			    20			Y	2											CHAR_CS	20	NO	NO		20	B	NO	YES	NONE
TBL_BBS	    CONTENTS	VARCHAR2			200			Y	3											CHAR_CS	200	NO	NO		200	B	NO	YES	NONE
TBL_BBS	    WRITEDAY	DATE			7			Y	4	8	"SYSDATE
"											                NO	NO		0		NO	YES	NONE
TBL_BBS	    COUNTS	NUMBER			22			Y	5	2	"0
"											            NO	NO		0		NO	YES	NONE
TBL_BBS	    COMMENTS	NUMBER			22			Y	6	2	"0
"											                        NO	NO		0		NO	YES	NONE
*/


--○ 테이블 생성 이후 DEFAULT 표현식 추가 / 변경

ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT 기본값;


-- DEFAULT 표현식을 제거하려면 어떻게 해야 할까? 
-- 제거하는 구문이 따로 만들어져 있지 않다.
-- DEFAULT 표현식을 제거하는 것은 → 값을 넣지 않았을 때 NULL 이 들어가게끔만 바꾸면 되는 것이다


--○ 기존의 DEFAULT 표현식 제거

ALTER TABLE 테이블명
MODIFY 컬럼명 [자료형] DEFAULT NULL;


COMMIT;
--==>> 커밋 완료.