국비 9일차, FLASHBACK, ALTER, 제약조건 컨트롤

2021.01.15 국비교육 9일차

[TOC]

FLASHBACK

SHOW RECYCLEBIN; -- 버려진 테이블 확인

-----------------FLASHBACK 예제
CREATE TABLE DEPT_NEW
(
    NUMA NUMBER(3)
);
-- 임의 테이블 생성

INSERT INTO DEPT_NEW VALUES(10);
INSERT INTO DEPT_NEW VALUES(20);
-- 임의의 데이터 삽입

DROP TABLE DEPT_NEW CASCADE CONSTRAINTS; -- 제약조건 까지 함께 삭제

FLASHBACK TABLE DEPT_NEW TO BEFORE DROP;
-- 되돌리기

-----------------------------------------------------------------
CREATE TABLE ABCD
(
    ABC NUMBER CONSTRAINT ABCD_ABC_NN NOT NULL
);

INSERT INTO ABCD VALUES(10);

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME IN ('ABCD');

DROP TABLE ABCD CASCADE CONSTRAINTS;

FLASHBACK TABLE ABCD TO BEFORE DROP;
-------------------------------------------제약조건 이름이 이상해짐

PURGE

• DROP TABLE 테이블명 PURGE;

  -> 복구 불가

테이블 변경

• ALTER TABLE
  • ADD (컬럼 삽입)
  • MODIFY (숫자 및 문자 컬럼 길이조절, 타입 변경)
  • DROP(컬럼 삭제)
  • RENAME(이름변경)

CREATE TABLE EMP04
AS
SELECT * FROM EMP;
-- 임의의 테이블 생성

ALTER TABLE EMP04 ADD (EMAIL VARCHAR2(10), ADDRESS VARCHAR2(20));
-- 새로운 컬럼 삽입

ALTER TABLE EMP04 MODIFY(EMAIL VARCHAR2(40));
-- EMAIL 컬럼 데이터 길이 조절

ALTER TABLE EMP04 DROP(EMAIL);
-- 컬럼 삭제
-----------------------------------------------------
CREATE TABLE SCOTT_T
( NUM NUMBER(4),
  NAME VARCHAR2(10));
-- 임의 테이블 생성

ALTER TABLE SCOTT_T ADD(ADDRESS VARCHAR2(20));
-- 임의 컬럼 삽입

ALTER TABLE SCOTT_T RENAME COLUMN ADDRESS TO ADDR;
-- ADDRESS 컬럼 이름을 ADDR로 변경

DESC SCOTT_T;
-----------------------------------------------------
CREATE TABLE SCOTT_T3(
NUM NUMBER(4),
NAME VARCHAR2(10)
);

ALTER TABLE SCOTT_T3
ADD CONSTRAINT SCOTT_T3_NUM_PK PRIMARY KEY(NUM);
-- 제약 조건 삽입 (PRIMARY KEY)

ALTER TABLE SCOTT_T3
MODIFY (NAME VARCHAR2(10) NOT NULL);
-- NOT NULL 조건 삽입 // NOT NULL은 MODIFY이용해야함

ALTER TABLE SCOTT_T3 DROP(NAME);
ALTER TABLE SCOTT_T3 ADD(NAME VARCHAR2(15) CONSTRAINT SCOTT_T3_NAME_NN NOT NULL);
-----------------------------------------------------
CREATE TABLE DEPT03(
NUM NUMBER(2),
DNAME VARCHAR2(15),
LOC VARCHAR2(15)
);

ALTER TABLE DEPT03
ADD CONSTRAINT DEPT03_NUM_PK PRIMARY KEY(NUM);

CREATE TABLE EMP03(
EMPNO NUMBER(4),
ENAME VARCHAR2(15),
NUM NUMBER(2)
);

ALTER TABLE EMP03 ADD CONSTRAINT EMP03_NUM_FK FOREIGN KEY(NUM)
                            REFERENCES DEPT03(NUM) ON DELETE CASCADE;

제약조건 삭제, 활성화 / 비활성화

• USER_CONSTRAINTS와 USER_CONS_COLUMNS에서 제약조건 이름 식별
• CASCADE 옵션은 모든 종속적인 제약조건을 같이 삭제

CREATE TABLE SCOTT_T3_REF(
NUM NUMBER(4),
CONSTRAINT SCOTT_T3_REF_FK FOREIGN KEY(NUM) 
            REFERENCES SCOTT_T3(NUM));

SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'SCOTT_T3_REF';

ALTER TABLE SCOTT_T3
DROP PRIMARY KEY CASCADE;

• 기존 제약조건을 잠시 비활성화 하는 방법

CREATE TABLE SCOTT_T4(
NUM NUMBER(4) CONSTRAINT SCOTT_T4_NUM_PK PRIMARY KEY,
NAME VARCHAR2(10)
);

--임의테이블 생성

ALTER TABLE SCOTT_T4
DISABLE CONSTARINT SCOTT_T4_NUM_PK;

-- 제약조건 비활성화

ALTER TABLE SCOTT_T4
ENABLE CONSTARINT SCOTT_T4_NUM_PK;

-- 제약 조건 활성화

CREATE TABLE SCOTT_CAS(
PK NUMBER PRIMARY KEY, -- NUMBER뒤에 숫자없으면 자리수 제한 X
FK NUMBER,
C1 NUMBER,
C2 NUMBER,
CONSTRAINT SCOTT_CAS_FK FOREIGN KEY(FK) REFERENCES SCOTT_CAS(PK),
CONSTRAINT SCOTT_CAS_CK1 CHECK(PK >10 AND C1 > 10),
CONSTRAINT SCOTT_CAS_CK2 CHECK( C2 > 10)
);

SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'SCOTT_CAS';

ALTER TABLE SCOTT_CAS
DROP PK CASCADE CONSTRAINTS;