12. DDL(Data Definition Language)

데이터 정의 언어로 오라클에서 제공하는 객체를 만들고(CREATE), 변경(ALTER)하고, 삭제(DROP)하는 등 실제 데이터 값이 아닌 데이터의 구조 자체를 정의하는 언어로 DB 관리자, 설계자가 주로 사용한다.

 

*오라클에서의 객체 : 테이블, 뷰, 시퀀스, 인덱스, 트리거, 프로시져, 함수, 사용자 등등

 

 

<CREATE>

데이터베이스의 객체를 생성하는 구문이다.

 

 

<TABLE(테이블)>

테이블은 행과 열로 구성되는 가장 기본적인 데이터베이스 객체로 데이터베이스 내에서 모든 데이터는 테이블에 저장된다.

 

 

<테이블 생성>

[표현법]

CREATE TABLE 테이블명 (
     컬럼명 자료형(크기) [DEFAULT 기본값], [제약조건],
     컬럼명 자료형(크기) [DEFAULT 기본값], [제약조건],
     ...
);

--회원에 대한 데이터를 담을 수 있는 MEMBER 테이블 생성
CREATE TABLE MEMBER (
    ID VARCHAR2(20),
    PASSWORD VARCHAR2(20),
    NAME VARCHAR2(20),
    ENROLL_DATE DATE DEFAULT SYSDATE -- 기본값 지정
);

DESC MEMBER; -- 테이블의 구조를 표시해 주는 구문이다.
SELECT * FROM MEMBER;

 

 

<컬럼에 주석 달기>

[표현법]
COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

COMMENT ON COLUMN MEMBER.ID IS '회원 아이디';
COMMENT ON COLUMN MEMBER.PASSWORD IS '회원 비밀번호';
COMMENT ON COLUMN MEMBER.NAME IS '회원 이름';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS '회원 가입일';

 

 

<데이터 딕셔너리(메타 데이터)>

자원을 효율적으로 관리하기 위한 다양한 객체들의 정보를 저장하는 시스템 테이블이다.

사용자가 객체를 생성하거나 객체를 변경하는 등의 작업을 할 때 데이터베이스에 의해서 자동으로 갱신되는 테이블이다.

데이터에 관한 데이터가 저장되어 있다고 해서 메타 데이터라고도 한다.

USER_TABLES : 사용자가 가지고 있는 테이블들의 전반적인 구조를 확인하는 뷰 테이블이다.
USER_TAB_COLUMNS : 테이블, 뷰의 컬럼과 관련된 정보를 조회하는 뷰 테이블이다.

SELECT * FROM USER_TABLES;
SELECT * FROM USER_TAB_COLS;

-- 테이블에 샘플 데이터 추가(INSERT)
-- INSERT INTO 테이블명[(컬럼명, 컬럼명, ...)] VALUES(값, 값, ...);
INSERT INTO MEMBER VALUES('USER1', '1234', '홍길동','2021-10-19');
INSERT INTO MEMBER VALUES('USER2', '1234', '김철수', SYSDATE);
INSERT INTO MEMBER VALUES('USER3', '1234', '김철수', DEFAULT); -- 기본값
INSERT INTO MEMBER (ID, PASSWORD) VALUES('USER3', '1234'); --설정안하면 NULL

SELECT * FROM MEMBER;

--위에서 테이블에 추가한 데이터를 실제 데이터베이스에 반영한다.
--(메모리 버퍼에 임시 저장된 데이터를 실제 테이블에 반영)
COMMIT;

SHOW AUTOCOMMIT; -- 오토커밋 확인
--SET AUTOCOMIT ON; - 대부분 설정하지 않는다.
--SET AUTOCOMIT OFF;

 

 

<제약 조건(CONSTRAINT)>

사용자가 원하는 조건의 데이터만 유지하기 위해서 테이블 작성 시 각 컬럼에 대해 저장될 값에 대한 제약조건을 설정할 수 있다.

제약 조건은 데이터 무결성 보장을 목적으로 한다. (데이터의 정확성과 일관성을 유지시키는 것)

*종류 : NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY

[표현법]

1)컬럼 레벨
CREATE TABLE 테이블명 (
     컬럼명 자료형(크기) [CONSTRAINT 제약조건명] 제약조건,
     ...
);

2)테이블 레벨
CREATE TABLE 테이블명 (
     컬럼명 자료형(크기),
     ...,
     [CONSTRAINT 제약조건명] 제약조건(컬럼명)
);

-- 제약 조건 확인
DESC USER_CONSTRAINTS;
SELECT * FROM USER_CONSTRAINTS;
-- 사용자가 작성한 제약조건을 확인하는 뷰

DESC USER_CONSTRAINTS;
SELECT * FROM USER_CONS_COLUMNS;
-- 사용자가 작성한 제약조건이 걸려있는 컬럼을 확인하는 뷰

 

 

<NOT NULL 제약조건>

해당 컬럼에 반드시 값이 있어야만 하는 경우 사용한다.
삽입/수정 시 NULL 값을 허용하지 않도록 제한한다.

-- 기존 MEMBER 테이블은 값에 NULL이 있어도 삽입 가능하다.
INSERT INTO MEMBER VALUES(NULL, NULL, NULL, NULL);

SELECT * FROM MEMBER;

-- NOT NULL 제약조건을 설정한 테이블 만들기
-- * NOT NULL 제약조건은 컬럼 레벨에서만 설정이 가능하다.

DROP TABLE MEMBER;
CREATE TABLE MEMBER (
    ID VARCHAR2(20) NOT NULL,
    PASSWORD VARCHAR2(20) NOT NULL,
    NAME VARCHAR2(20) NOT NULL,
    ENROLL_DATE DATE DEFAULT SYSDATE
);

DESC MEMBER;

-- NOT NULL 제약 조건에 위배되어 오류 발생
INSERT INTO MEMBER VALUES(NULL, NULL, NULL, NULL);

INSERT INTO MEMBER VALUES('USER1', '1234', '길동이', NULL);

UPDATE MEMBER
SET ID = NULL
WHERE NAME ='길동이'; --에러, NULL로 변경불가 제약조건.

DESC MEMBER;
SELECT * FROM MEMBER;

 

 

<UNIQUE 제약조건>

컬럼의 입력 값에 중복 값을 제한하는 제약조건이다.
데이터를 삽입/수정 시 기존에 있는 데이터 값 중에 중복되는 값이 있을 경우 삽입/수정되지 않는다.
제약조건 지정 방식으로 컬럼 레벨, 테이블 레벨 방식 모두 사용 가능하다.

INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', DEFAULT);
INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', DEFAULT);

SELECT * FROM MEMBER;

DROP TABLE MEMBER;

CREATE TABLE MEMBER (
    ID VARCHAR2(20) NOT NULL UNIQUE,
    PASSWORD VARCHAR2(20) NOT NULL,
    NAME VARCHAR2(20) NOT NULL,
    ENROLL_DATE DATE DEFAULT SYSDATE
);

INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', DEFAULT);
INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', DEFAULT);

DROP TABLE MEMBER;

CREATE TABLE MEMBER (
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    ENROLL_DATE DATE DEFAULT SYSDATE,
    CONSTRAINT MEMBER_ID_UQ UNIQUE(ID)
);
--NOT NULL의 약자 NN

INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', DEFAULT);
INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', DEFAULT);
--어디가 잘못됐는지 확인 가능하다.
DROP TABLE MEMBER;

--여러 개의 컬럼을 묶어서 하나의 UNIQUE 제약 조건을 설정하는 것도 가능하다.
--(단, 반드시 테이블 레벨로만 설정이 가능하다.)--컬럼레벨이 아니다.
CREATE TABLE MEMBER (
    NO NUMBER CONSTRAINT MEMBER_NO_NN NOT NULL,
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    ENROLL_DATE DATE DEFAULT SYSDATE,
    CONSTRAINT MEMBER_ID_UQ UNIQUE(NO, ID)
);

--여러 컬럼을 묶어서 UNIQUE 제약 조건이 설정되어 있으면 제약 조건이 설정되어 있는
--컬럼 값이 모두 중복되는 경우에만 에러가 발생한다.
INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '아무개', DEFAULT);
INSERT INTO MEMBER VALUES(2, 'USER1', '1234', '아무개', DEFAULT);
INSERT INTO MEMBER VALUES(2, 'USER2', '1234', '아무개', DEFAULT);
INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '아무개', DEFAULT);

DROP TABLE MEMBER;

SELECT UC.CONSTRAINT_NAME, 
       UC.CONSTRAINT_TYPE, 
       UC.TABLE_NAME, 
       UCC.COLUMN_NAME
  FROM USER_CONSTRAINTS UC
  JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME;

 

 

<CHECK 제약조건>

컬럼에 기록되는 값에 조건을 설정하고 조건을 만족하는 값만 기록할 수 있다.

비교 값은 리터럴만 사용 가능하다.(변하는 값이나 함수 사용하지 못한다.)

[표현법]

CHECK(비교연산자)
CHECK(컬럼 [NOT] IN(값, 값, ...))
CHECK(컬럼 = 값)
CHECK(컬럼 BETWEEN 값 AND 값)
CHECK(컬럼 LIKE '_문자' OR 컬럼 LIKE '문자%')
...

DROP TABLE MEMBER;

CREATE TABLE MEMBER (
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    GENDER CHAR(3),
    AGE NUMBER,
    ENROLL_DATE DATE DEFAULT SYSDATE,
    CONSTRAINT MEMBER_ID_UQ UNIQUE(ID)
);

--성별, 나이에 유효한 값이 아닌 값들도 INSERT 된다..
--문법적 오류는 아니지만 논리적 오류다.
INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', '남', 25, DEFAULT);
INSERT INTO MEMBER VALUES('USER2', '1234', '문인수', '강', 39, DEFAULT);
INSERT INTO MEMBER VALUES('USER3', '1234', '홍길동', '여', -30, DEFAULT);

SELECT * FROM MEMBER;

DROP TABLE MEMBER;

CREATE TABLE MEMBER (
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    GENDER CHAR(3) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN ('남', '여')),
    AGE NUMBER CONSTRAINT MEMBER_AGE_CK CHECK(AGE>0), --AND , BETWEEN 사용 가능
    ENROLL_DATE DATE DEFAULT SYSDATE,
    CONSTRAINT MEMBER_ID_UQ UNIQUE(ID)
);

INSERT INTO MEMBER VALUES('USER1', '1234', '아무개', '남', 25, DEFAULT);
--GENDER 컬럼에 '남' 또는 '여'만 기록 가능하도록 제약조건이 설정되었기 때문에 에러가 발생.
INSERT INTO MEMBER VALUES('USER2', '1234', '문인수', '강', 39, DEFAULT);
--AGE컬럼에 0보다 큰 값만 기록 가능하도록 제약조건이 설정되었기 때문에 에러가 발생.
INSERT INTO MEMBER VALUES('USER3', '1234', '홍길동', '여', -30, DEFAULT);

SELECT * FROM MEMBER;

 

 

<PRIMARY KEY(기본 키) 제약조건>

테이블에서 한 행의 정보를 식별하기 위해 사용할 컬럼에 부여하는 제약조건이다.
각 행들을 구분할 수 있는 식별자 역할(사번, 부서 코드, 직급 코드, ...)
기본 키 제약조건을 설정하게 되면 자동으로 해당 컬럼에 NOT NULL + UNIQUE 제약조건이 설정된다.
한 테이블에 한 개만 설정할 수 있다.(단, 한 개 이상의 컬럼을 묶어서 PRIMARY KEY로 제약조건을 설정할 수 있다.)
컬럼 레벨, 테이블 레벨 방식 모두 설정 가능하다.

DROP TABLE MEMBER;

CREATE TABLE MEMBER (
    NO NUMBER CONSTRAINT MEMBER_NO_PK PRIMARY KEY,
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    GENDER CHAR(3) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN ('남', '여')),
    AGE NUMBER CONSTRAINT MEMBER_AGE_CK CHECK(AGE > 0),
    ENROLL_DATE DATE DEFAULT SYSDATE,
    --CONSTRAINT MEMBER_NO_PK PRIMARY KEY(NO),
    CONSTRAINT MEMBER_ID_UQ UNIQUE(ID)
);

INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '아무개', '남', 25, DEFAULT);
INSERT INTO MEMBER VALUES(2, 'USER2', '1234', '문인수', '남', 39, DEFAULT);
INSERT INTO MEMBER VALUES(3, 'USER3', '1234', '춘향이', '여', 20, DEFAULT);
--기본 키 중복으로 오류
INSERT INTO MEMBER VALUES(3, 'USER4', '1234', '홍길동', '남', 30, DEFAULT);
--기본 키가 NULL이므로 오류
INSERT INTO MEMBER VALUES(NULL, 'USER4', '1234', '홍길동', '남', 30, DEFAULT);

SELECT * FROM MEMBER;

DROP TABLE MEMBER;

CREATE TABLE MEMBER (
    NO NUMBER,
    ID VARCHAR2(20),
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    GENDER CHAR(3) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN ('남', '여')),
    AGE NUMBER CONSTRAINT MEMBER_AGE_CK CHECK(AGE > 0),
    ENROLL_DATE DATE DEFAULT SYSDATE,
    CONSTRAINT MEMBER_NO_PK PRIMARY KEY(NO, ID)
    --컬럼을 묶어서 하나의 기본 키를 생성
    --> "복합키"라고 한다.
);

--NO, ID 둘 다 동일한 경우만 에러가 발생한다.
INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '아무개', '남', 25, DEFAULT);
INSERT INTO MEMBER VALUES(1, 'USER2', '1234', '문인수', '남', 39, DEFAULT);
INSERT INTO MEMBER VALUES(3, 'USER2', '1234', '춘향이', '여', 20, DEFAULT);
INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '홍길동', '남', 30, DEFAULT);
--NO, ID 둘 중에 하나만 NULL이 들어가도 기본키는 NOT NULL을 포함하기 때문에 에러.
INSERT INTO MEMBER VALUES(NULL, 'USER4', '1234', '홍길동', '남', 30, DEFAULT);

SELECT * FROM MEMBER;

DROP TABLE MEMBER;

 

 

<FOREIGN KEY(외래 키) 제약 조건>

다른 테이블에 존재하는 값만을 가져야 하는 컬럼에 부여하는 제약조건이다.(단, NULL 값도 가질 수 있다.)
즉, 참조된 다른 테이블이 제공하는 값만 기록할 수 있다.(FOREIGN KEY 제약조건에 의해서 테이블 간에 관계가 형성된다.)

[표현법]
1) 컬럼 레벨
컬럼명 자료형(크기) [CONSTRAINT 제약조건명] REFERENCES 참조할테이블명 [ (기본키) ] [삭제룰]

2) 테이블 레벨
[CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명 [ (기본키) ] [삭제룰]

[삭제룰]
부모 테이블의 데이터가 삭제됐을 때의 옵션을 지정해 놓을 수 있다.
1)ON DELETE RESTRICT : 자식 테이블의 참조 키가 부모 테이블의 키 값을 참조하는 경우 부모 테이블의 행을 삭제할 수 없다.(기본적으로 적용되는 옵션)

2)ON DELETE SET NULL : 부모 테이블의 데이터가 삭제 시 참조하고 있는 자식 테이블의 컬럼 값이 NULL로 변경된다.

3)ON DELETE CASCADE : 부모 테이블의 데이터가 삭제 시 참조하고 있는 자식 테이블의 컬럼 값이 존재하는 행 전체가 삭제된다.

-- 회원등급에 대한 데이터를 보관하는 테이블(부모테이블)
CREATE TABLE MEMBER_GRADE (
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

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

SELECT * FROM MEMBER_GRADE;

-- 자식 테이블
CREATE TABLE MEMBER (
    NO NUMBER CONSTRAINT MEMBER_NO_PK PRIMARY KEY,
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    GENDER CHAR(3) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN ('남', '여')),
    AGE NUMBER CONSTRAINT MEMBER_AGE_CK CHECK(AGE > 0),
    GRADE_ID NUMBER CONSTRAINT MEMBER_GRADE_ID_FK REFERENCES MEMBER_GRADE(GRADE_CODE),
    ENROLL_DATE DATE DEFAULT SYSDATE,
    --CONSTRAINT MEMBER_GRADE_ID_FK FOREIGN KEY(GRADE_ID) REFERENCES MEMBER_GRADE /*(GRADE_CODE)*/--생략가능
    CONSTRAINT MEMBER_ID_UQ UNIQUE(ID)
);

INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '아무개', '남', 25, 10, DEFAULT);
-- 50이라는 값이 MEMBER_GRADE 테이블에 GRADE_CODE 컬럼에서 제공하는 값이 아니므로
-- 외래 키 제약 조건에 위배되어 오류 발생
INSERT INTO MEMBER VALUES(2, 'USER2', '1234', '문인수', '남', 39, 50, DEFAULT);
INSERT INTO MEMBER VALUES(3, 'USER3', '1234', '춘향이', '여', 20, NULL, DEFAULT);

SELECT * FROM MEMBER;
SELECT * FROM MEMBER_GRADE;


-- MEMBER테이블과 MEMBER_GRADE테이블을 JOIN하여 ID, NAME, GRADE_NAME값을 조회
SELECT M.ID, M.NAME, G.GRADE_NAME
  FROM MEMBER M
  LEFT JOIN MEMBER_GRADE G ON (M.GRADE_ID = G.GRADE_CODE);

-- 자식 테이블의 행들 중에 10을 사용하고 있기 때문에 삭제할 수 없다.
DELETE FROM MEMBER_GRADE
 WHERE GRADE_CODE = 10;
-- 자식 테이블의 행들 중에 30을 사용하고 있는 행이 없기 때문에 삭제할 수 있다.
DELETE FROM MEMBER_GRADE
 WHERE GRADE_CODE = 30;
-- * 외래키의 특징 중 하나이다.

--위에서 데이터 삭제 작업을 취소한다. ( 메모리 버퍼에 임시 삭제한 데이터를
-- 실제 테이블에 반영하지 않고 작업을 취소한다.) <-> COMMIT;
ROLLBACK; --부모테이블 데이터 돌리기.

DROP TABLE MEMBER;


--ON DELETE SET NULL 옵션이 추가 된 자식 테이블 생성
CREATE TABLE MEMBER (
    NO NUMBER CONSTRAINT MEMBER_NO_PK PRIMARY KEY,
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    GENDER CHAR(3) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN ('남', '여')),
    AGE NUMBER CONSTRAINT MEMBER_AGE_CK CHECK(AGE > 0),
    GRADE_ID NUMBER CONSTRAINT MEMBER_GRADE_ID_FK REFERENCES MEMBER_GRADE(GRADE_CODE) ON DELETE SET NULL,
    ENROLL_DATE DATE DEFAULT SYSDATE,
    CONSTRAINT MEMBER_ID_UQ UNIQUE(ID)
);

INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '아무개', '남', 25, 10, DEFAULT);
INSERT INTO MEMBER VALUES(3, 'USER3', '1234', '춘향이', '여', 20, NULL, DEFAULT);

SELECT * FROM MEMBER;
SELECT * FROM MEMBER_GRADE;

--자식 테이블을 조회해보면 삭제된 행을 참조하고 있던 컬럼의 값이 NULL로 변경된 것을 확인 할 수 있다.
DELETE FROM MEMBER_GRADE WHERE GRADE_CODE = 10;

ROLLBACK;

DROP TABLE MEMBER;

--ON DELETE CASCADE 옵션이 추가 된 자식 테이블 생성
CREATE TABLE MEMBER (
    NO NUMBER CONSTRAINT MEMBER_NO_PK PRIMARY KEY,
    ID VARCHAR2(20) CONSTRAINT MEMBER_ID_NN NOT NULL,
    PASSWORD VARCHAR2(20) CONSTRAINT MEMBER_PASSWORD_NN NOT NULL,
    NAME VARCHAR2(20) CONSTRAINT MEMBER_NAME_NN NOT NULL,
    GENDER CHAR(3) CONSTRAINT MEMBER_GENDER_CK CHECK(GENDER IN ('남', '여')),
    AGE NUMBER CONSTRAINT MEMBER_AGE_CK CHECK(AGE > 0),
    GRADE_ID NUMBER CONSTRAINT MEMBER_GRADE_ID_FK REFERENCES MEMBER_GRADE(GRADE_CODE) ON DELETE CASCADE,
    ENROLL_DATE DATE DEFAULT SYSDATE,
    CONSTRAINT MEMBER_ID_UQ UNIQUE(ID)
);

INSERT INTO MEMBER VALUES(1, 'USER1', '1234', '아무개', '남', 25, 10, DEFAULT);
INSERT INTO MEMBER VALUES(3, 'USER3', '1234', '춘향이', '여', 20, NULL, DEFAULT);

SELECT * FROM MEMBER;
SELECT * FROM MEMBER_GRADE;

-- 자식 테이블을 조회해 보면 삭제된 행을 참조하고 있던 자식 테이블의 행들이 모두 삭제된 것을 확인할 수 있다.
DELETE FROM MEMBER_GRADE WHERE GRADE_CODE = 10;

ROLLBACK;

SELECT UC.CONSTRAINT_NAME, 
       UC.CONSTRAINT_TYPE, 
       UC.TABLE_NAME, 
       UCC.COLUMN_NAME
  FROM USER_CONSTRAINTS UC
  JOIN USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
 WHERE UCC.TABLE_NAME = 'MEMBER';

* 대부분의 데이터는 함부로 지우기가 조심스러운 부분이 있다. 때문에 이러한 옵션정도가 있구나 정도로 알아 둘 것.

 

 

<SUBQUERY를 이용한 테이블 생성>

SUBQUERY를 사용해서 테이블을 생성한다.

컬럼명, 데이터 타입, 값이 복사되고 제약 조건은 NOT NULL만 복사 된다.

[표현법]
CREATE TABLE 테이블명 {
AS 서브 쿼리;

--EMPLOYEE 테이블을 복사한 새로운 테이블 생성
--(컬럼, 데이터 타입, 값, NOT NULL 제약 조건을 복사)
CREATE TABLE EMPLOYEE_COPY
   AS SELECT *
        FROM EMPLOYEE;
     
DROP TABLE EMPLOYEE_COPY;

--EMPLOYEE 테이블을 복사한 새로운 테이블 생성
--(컬럼, 데이터 타입, NOT NULL 제약 조건을 복사)
CREATE TABLE EMPLOYEE_COPY
   AS SELECT *
        FROM EMPLOYEE
       WHERE 1=0;--모든 행에 대해서 매번 FALSE 이기 때문에 테이블의 구조만 복사되고 데이터 값은 복사되지 않는다.

DROP TABLE EMPLOYEE_COPY;


--EMPLOYEE 테이블의 사번, 사원명, 급여, 연봉을 저장하는 테이블을 서브 쿼리를 사용해서 생성
CREATE TABLE EMPLOYEE_COPY
   AS SELECT EMP_ID AS "사번",
             EMP_NAME AS "사원명",
             SALARY AS "급여",
             SALARY*12 AS "연봉"
        FROM EMPLOYEE;
-- SELECT 절에 산술연산 또는 함수식이 기술된 경우 별칭을 정해줘야 한다.


DROP TABLE EMPLOYEE_COPY;