국비 8일차, DDL(CREATE, CONSTRAINT)

2021.01.14 국비교육 8일차

[TOC]

DDL

• 데이터 베이스 객체 이름 지정 방법
  • 문자시작, 1~30문자
  • 영어 권장
  • 이름 중복을 피한다
  • Oracle 예약어 사용 불가
  • 대소문자 구별 안함

CREATE

CREATE TABLE DEPT2
( DEPTNO NUMBER(2),
  DANEM VARCHAR2(10),
  LOC VARCHAR2(10) );

--DEPT2 테이블 생성

CREATE TABLE SCOTT.DEPT3
( DEPTNO NUMBER(2),
  DANEM VARCHAR2(10),
  LOC VARCHAR2(10) );

CREATE TABLE EMPLOYEE
( EMPNO NUMBER(4),
  ENAME CHAR(20),
  HIREDATE DATE,
  SAL NUMBER(7,2) );

INSERT INTO EMPLOYEE 
VALUES(10, '홍길동', SYSDATE, 3000);

CREATE TABLE COPY_DEPT
( DEPTNO NUMBER(2),
  DNAME CHAR(10),
  LOC CHAR(4));

INSERT INTO COPY_DEPT
VALUES(10, '인사과', '서울');

스키마(SCHEMA)

• 특정 사용자가 데이터베이스에 접근하여 생성한 개체들의 대표이름을 의미
• 일반적으로 사용자의 계정명 (EX) scott 계정의 스키마는 scott)

SELECT ROWID, DEPTNO FROM DEPT;
-- ROWID 확인하는 법

SELECT * FROM DEPT WHERE ROWID = 'AAAE5cAAEAAAAFNAAA';
--ROWID를 활용하여 ROW 출력

DEFAULT 옵션

• 해당 테이블에 행을 입력할때 NULL 대신 디폴트 값이 저장되게 하는 것

CREATE TABLE DEF_TABLE2
( NUM NUMBER(2),
  WRITEDATE DATE DEFAULT SYSDATE);

INSERT INTO DEF_TABLE2 (NUM, WRITEDATE)
VALUES(2, DEFAULT);

INSERT INTO DEF_TABLE2(NUM)
VALUES(1);

SELECT * FROM DEF_TABLE2;

CTAS

• 서브 쿼리를 이용한 테이블 생성
• 지정된 컬럼의 개수와 서브쿼리에서 리턴된 컬럼의 개수가 일치해야함
• 컬럼을 지정할 대 컬럼명과 디폴트 값만 지정 가능
• 제약조건은 만들어 지지 않으며 오직 컬럼의 데이터 타입만 동일하게 생성

CREATE TABLE DEPTA
AS
SELECT * FROM DEPT;

CREATE TABLE DEPTB(NO, NAME)
AS
SELECT DEPTNO, DNAME FROM DEPT;

CREATE TABLE DEPTC
AS
SELECT * FROM DEPT
WHERE 1=2;

제약조건 (Constraints)

• 부적절한 자료가 입력되는 것을 방지

• 컬럼 레밸: 테이블 생성시 컬럼 옆에 기입

• 테이블 레밸 : 테이블 생성시 마지막에 기입

제약조건	기술
NOT NULL	이 열은 NULL값을 포함하지 않음을 지정 (무조건 칼럼레밸)
UNIQUE	테이블의 모든 행에 대해 유일해야 하는 값을 가진 열 또는 열의 조합을 지정(중복X)
PRIMARY KEY	유일하게 테이블의 각 행을 식별 (중복, NULL 불가)
FOREIGN KEY	열과 참조된 테이블의 열 사이의 외래키 관계를 적용하고 설정
CHECK	참이어야 하는 조건을 지정

PRIMARY KEY

• 테이블에 대한 기본키 생성
• 오직 하나의 키
• UNIQUE와 NOT NULL 조건 만족

CREATE TABLE DEPARTMENT
(
DEPTNO NUMBER(2) CONSTRAINT DEPARTMENT_DEPTNO_PK PRIMARY KEY,
    DBANE VARCHAR2(15),
    LOC VARCHAR2(15)
);

CREATE TABLE PK_TAB1(
ID NUMBER(2) CONSTRAINT PK_TAB1_ID_PK PRIMARY KEY,
NAME VARCHAR2(10)
);
-- 칼럼 레밸에서 생성
INSERT INTO PK_TAB1 VALUES (10, 'AA');
INSERT INTO PK_TAB1 VALUES (10, 'BB');

CREATE TABLE PK_TAB2(
        ID NUMBER(2),
        NAME VARCHAR2(10),
        CONSTRAINT PK_TAB2_ID_PK PRIMARY KEY(ID)
);
-- 테이블 레벨에서 생성
INSERT INTO PK_TAB2 VALUES (NULL, 'AA');


SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('DEPARTMENT');
-- 해당 테이블의 제약조건 보기

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME IN ('DEPARTMENT');
-- 어떤 컬럼에 제약조건이 정의 되었는지 확인하기 위해 사용

CREATE TABLE DEPARTMENT2
( DEPTNO NUMBER(2),
  DNAME VARCHAR2(20),
  LOC VARCHAR2(15),
  CONSTRAINT DEPARTMENT2_DEPTNO_PK PRIMARY KEY(DEPTNO));

INSERT INTO DEPARTMENT2
VALUES(10, '영업부', '수원');

UNIQUE

• 중복값 불가
• NULL값 저장 가능
• 줄일 때 주로 UK

CREATE TABLE UNI_TAB1(
DEPTNO NUMBER(2) CONSTRAINT UNI_TAB1_DEPTNO_UK UNIQUE,
DNAME CHAR(14),
LOC CHAR(13));

INSERT INTO UNI_TAB1
VALUES (NULL, NULL, NULL);

SELECT * FROM UNI_TAB1;

CREATE TABLE UNI_TAB2(
DEPTNO NUMBER(2),
DNAME VARCHAR(15),
LOC VARCHAR(15),
CONSTRAINT UNI_TAB2_DEPTNO_UK UNIQUE(DEPTNO));

CREATE TABLE DEPARTMENT4(
DEPTNO NUMBER(4) CONSTRAINT DEPARTMENT4_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15) CONSTRAINT DEPARTMENT4_DMAME_UK UNIQUE,
LOC VARCHAR2(15));

CREATE TABLE DEPARTMENT4(
DEPTNO NUMBER(4) CONSTRAINT DEPARTMENT4_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15) CONSTRAINT DEPARTMENT4_DMAME_UK UNIQUE,
LOC VARCHAR2(15));

CREATE TABLE DEPARTMENT5(
DEPTNO NUMBER(4) CONSTRAINT DEPARTMENT5_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15),
LOC VARCHAR2(15),
CONSTRAINT DEPARTMENT5_DNAME_UK UNIQUE(DNAME)
);
INSERT INTO DEPARTMENT5 VALUES(10, '인사', '서울');
INSERT INTO DEPARTMENT5 VALUES(20, '인사', '서울');

-- 두번째 데이터는 안들어감 // '인사' 겹침

CREATE TABLE SAWON_2(
S_NO NUMBER(2),
S_NAME VARCHAR2(15) NOT NULL,
S_EMAIL VARCHAR2(15) CONSTRAINT SAWON_2_S_EMAIL_UK UNIQUE
);

INSERT INTO SAWON_2 VALUES(11, NULL, 'MAIL');
-- 안들어감

CREATE TABLE DEPARTMENT6(
DEPTNO NUMBER(4) CONSTRAINT DEPARTMENT6_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15) CONSTRAINT DEPARTMENT6_DNAME_UK UNIQUE,
LOC VARCHAR2(15) CONSTRAINT DEPARTMENT6_LOC_NN NOT NULL --컬럼 레벨만 사용
);


CREATE TABLE SAWON_4(
S_NO NUMBER(2),
S_NAME VARCHAR2(10),
S_EMAIL VARCHAR(20),
CONSTRAINT SAWON_4_UK UNIQUE(S_NAME, S_EMIL) -- 두개가 동시에 중복인 경우에 에러발생
);

INSERT INTO SAWON_4 VALUES(12, '홍길동', 'MAIL');
INSERT INTO SAWON_4 VALUES(11, '홍길', 'MAIL'); -- 하나만 겹치기 때문에 생성 됨

CHECK

• 해당 컬럼에 반드시 만족해야 될 조건을 지정하는 제약조건

CREATE TABLE SAWON_7(
S_NO NUMBER(2),
S_NAME VARCHAR2(15),
S_SAL NUMBER(10) CONSTRAINT SAWON_7_S_SAL_CK CHECK (S_SAL <500));
--SAL이 500이 넘을 경우는 입력이 안됨

CREATE TABLE DEPARTMENT8(
DEPTNO NUMBER(2),
DNAME VARCHAR2(15) CONSTRAINT DEPARTMENT8_DNAME_CK CHECK(DNAME IN ('인사', '개발'))
LOC VARCHAR2(15));
--인사과와 개발과 외에는 입력 불가

CREATE TABLE DEPARTMENT9(
DEPTNO NUMBER(2),
DNAME VARCHAR2(15), 
LOC VARCHAR2(15),
CONSTRAINT DEPARTMENT9_DNAME_CK CHECK(DNAME IN ('인사', '개발'))
);

FOREIGN KEY

• FOREIGN KEY는 DETAIL쪽에서 정리

• 추가옵션

  • ON ELETE CASCAD

    FK 제약조건에 의해 참조되는 테이블의 행이 삭제되면, 해당 행을 참조하는 테이블의 행도 같이 삭제되도록 한다.

  • ON DELETE SET NULL

    FK 제약조건에 의해 참조되는 테이블의 행이 삭제되면 해당 행을 참조하는 테이블의 컬럼을 NULL값으로 변경

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('EMP', 'DEPT');
--제약 조건 확인

CREATE TABLE DEPT02(
DEPTNO NUMBER(2) CONSTRAINT DEPT_02_DEPTNO_PK PRIMARY KEY,
DNAME VARCHAR2(15),
LOC VARCHAR2(15)
);

INSERT INTO DEPT02(DEPTNO, DNAME, LOC) VALUES(10, '인사', '서울');
INSERT INTO DEPT02(DEPTNO, DNAME, LOC) VALUES(20, '개발', '광주');
INSERT INTO DEPT02(DEPTNO, DNAME, LOC) VALUES(30, '관리', '부산');
INSERT INTO DEPT02(DEPTNO, DNAME, LOC) VALUES(40, '영업', '경기');

COMMIT;

--테스트용 테이블 생성

CREATE TABLE EMP02(
EMPNO NUMBER(4) CONSTRAINT EMP02_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(15),
DEPTNO NUMBER(2) CONSTRAINT EMP02_DEPTNO_FK 
                    REFERENCES DEPT02(DEPTNO)
);
-- 레퍼런스 완료

INSERT INTO EMP02 VALUES(1000, 'JOHN', 10);
INSERT INTO EMP02 VALUES(4000, 'MIKE', 50); -- 제약조건 위반

CREATE TABLE EMP03(
EMPNO NUMBER(4) CONSTRAINT EMP03_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(15),
DEPTNO NUMBER(2), 
CONSTRAINT EMP03_DEPTNO_FK FOREIGN KEY(DEPTNO) 
                    REFERENCES DEPT02(DEPTNO)
);
-- 테이블레벨로 작성

INSERT INTO EMP03 VALUES(1000, 'JOHN', 10);
INSERT INTO EMP03 VALUES(4000, 'MIKE', 50); -- 똑같이 이부분에서 오류

CREATE TABLE EMP02(
EMPNO NUMBER(4) CONSTRAINT EMP02_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(15),
DEPTNO NUMBER(2) CONSTRAINT EMP02_DEPTNO_FK 
                    REFERENCES DEPT02(DEPTNO) ON DELETE CASCADE
);

INSERT INTO EMP02 VALUES(1000, 'JOHN', 10);
INSERT INTO EMP02 VALUES(2000, 'SMITH', 20);

SELECT * FROM EMP02;

DELETE FROM DEPT02 WHRER DEPTNO = 10; -- 이번에는 삭제 가능

-------------------------------------------------------------------------------
CREATE TABLE EMP02(
EMPNO NUMBER(4) CONSTRAINT EMP02_EMPNO_PK PRIMARY KEY,
ENAME VARCHAR2(15),
DEPTNO NUMBER(2) CONSTRAINT EMP02_DEPTNO_FK 
                    REFERENCES DEPT02(DEPTNO) ON DELETE SET NULL
);


INSERT INTO EMP02 VALUES(1000, 'JOHN', 10);
INSERT INTO EMP02 VALUES(2000, 'SMITH', 20);

drop table dept02; -- 참조때문에 삭제 안됨 // 제약 조건 연쇄적으로 삭제하면 됨
drop table dept02 CASCADE CONSTRAINTS; -- 제약조건 연쇄적 삭제 

오라클 constraint type

C	Check on a table	Column
O	Read Only on a view	Object
P	Primary Key	Object
R	Referential AKA Foreign Key	Column
U	Unique Key	Column
V	Check Option on a view	Object