[Oracle(SQL)] DDL(CREATE)

TIL/Oracle(SQL)

[Oracle(SQL)] DDL(CREATE)

yndev 2022. 1. 25. 15:38

DDL이란?

- 데이터 정의 언어이다. 객체(Object)를 만들고 (CREATE), 수정하고(ALTER), 삭제(DROP)하는 구문을 말한다.

오라클 객체의 종류

- 테이블(TABLE)
- 뷰(VIEW)

- 시퀀스(SEQUENCE)

- 인덱스(INDEX)

- 패키지(PACKAGE)

- 프로시져(PROCEDUAL)

- 함수(FUNCTION)

- 트리거(TRIGGER)

- 동의어(SYNONYM)

- 사용자(USER)

CREATE - 테이블만들기

CREATE TABLE 테이블명 (
컬럼명 자료형(크기),
컬럼명 자료형(크기),
.
.
.
);

CREATE TABLE MEMBER(
    MEMBER_ID VARCHAR2(20),
    MEMBER_PWD VARCHAR2(20),
    MEMBER_NAME VARCHAR2(20)
);

컬럼에 주석 달기

COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';

COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PWD IS '비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NAME IS '회원이름';

제약조건

- 테이블 작성 시 각 컬럼에 대해 값 기록에 대한 제약조건을 설정할 수 있다.

- 데이터 무결성 보장을 목적으로 함

- 입력/수정하는 데이터에 문제가 없는지 자동으로 검사하는 목적

- PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY

NOT NULL

- 해당 컬럼에 반드시 값이 기록되어야 하는 경우 사용

- 삽입/수정 시 NULL값을 허용하지 않도록 컬럼 레벨에서 제한

컬럼 레벨에 NOT NULL 제약조건 설정

CREATE TABLE USER_NOTNULL(
    USER_NO NUMBER NOT NULL,
    USER_ID VARCHAR2(20) NOT NULL,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30) NOT NULL,
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

SELECT
       UC.*
     , UCC.*
  FROM USER_CONSTRAINTS UC
  JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
 WHERE UC.TABLE_NAME = 'USER_NOTNULL';

이후 INSER할 때 NULL 값을 넣으려고 하면 오류가 뜬다.

UNIQUE 제약 조건

- 컬럼의 입력 값에 대해 중복을 제한하는 제약조건

- 컬럼 레벨에서 설정 가능, 테이블 레벨에서 설정 가능

UNIQUE 제약 조건 컬럼 레벨 설정

CREATE TABLE USER_UNIQUE(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE NOT NULL,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

중복되는 값을 입력하면 무결성 제약조건 위배 문구가 뜸.

상단 제약조건 코드를 넣어보면 어디에서 위배가 됐는지 조회 가능하다.

SELECT
       UCC.TABLE_NAME
     , UCC.COLUMN_NAME
     , UC.CONSTRAINT_TYPE
  FROM USER_CONSTRAINTS UC
     , USER_CONS_COLUMNS UCC
 WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
   AND UCC.CONSTRAINT_NAME = 'SYS_C008431';

두 개의 컬럼을 하나의 UNIQUE 제약 조건 설정

CREATE TABLE USER_UNIQUE3(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) NOT NULL,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    UNIQUE(USER_NO, USER_ID)
);

제약조건에 이름 설정

CREATE TABLE CONS_NAME(
    TEST_DATA1 VARCHAR2(20) CONSTRAINT NN_TEST_DATA1 NOT NULL,
    TEST_DATA2 VARCHAR2(20) CONSTRAINT UN_TEST_DATA2 UNIQUE,
    TEST_DATA3 VARCHAR2(30),
    CONSTRAINT UN_TEST_DATA3 UNIQUE(TEST_DATA3)
);

SELECT
       UC.*
  FROM USER_CONSTRAINTS UC
 WHERE TABLE_NAME = 'CONS_NAME';

CHECK 제약 조건

- 컬럼에 기록되는 값에 조건 설정을 할 수 있음

CHECK(컬럼명 비교연산자 비교값)

* 주의 : 비교값은 리터럴만 사용할 수 있음. 변하는 값이나 함수 사용 못함

GENDER값에 '남', '여'만 들어갈 수 있게 CHECK 제약조건 설정

CREATE TABLE USER_CHECK(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

테이블 레벨에서 CHECK 제약 조건 설정

CREATE TABLE TEST_CHECK(
    TEST_NUMBER NUMBER,
    CONSTRAINT CK_TEST_NUMBER CHECK(TEST_NUMBER > 0)
);

음수 값을 넣으면 제약조건 위배 오류가 뜬다.

[실습]

- 회원 가입용 테이블 생성(USER_TEST)
컬럼명 : USER_NO(회원번호)
USER_ID(회원아이디) -- 중복 금지, NULL값 허용 안함
USER_PWD(회원비밀번호) -- NULL값 허용 안함
PNO(주민등록번호) -- 중복 금지, NULL값 허용 안함
GENDER(성별) -- '남' 또는 '여'로 입력
PHONE(연락처)
ADDRESS(주소)
STATUS(탈퇴여부) -- NOT NULL, 'Y' 혹은 'N'으로 입력
각 제약조건 이름 부여
5명 이상 회원 정보 INSERT
각 컬럼별로 코멘트 생성

테이블 생성(CREATE)시 제약조건 설정, 제약조건 이름 부여

CREATE TABLE USER_TEST(
  USER_NO NUMBER,
  USER_ID VARCHAR2(20) CONSTRAINT NN_USER_ID NOT NULL,
  USER_PWD VARCHAR2(20) CONSTRAINT NN_USER_PWD NOT NULL,
  PNO VARCHAR2(20) CONSTRAINT NN_PNO NOT NULL,
  GENDER VARCHAR2(3),
  PHONE VARCHAR2(20),
  ADDRESS VARCHAR2(100),
  STATUS VARCHAR2(3) CONSTRAINT NN_STATUS NOT NULL,
  CONSTRAINT UK_USER_ID UNIQUE(USER_ID),
  CONSTRAINT UK_PNO UNIQUE (PNO),
  CONSTRAINT CK_GENDER CHECK(GENDER IN ('남', '여')),
  CONSTRAINT CK_STATUS CHECK(STATUS IN ('Y', 'N'))
);

각 컬럼별로 코멘트 생성

COMMENT ON COLUMN USER_TEST.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_TEST.USER_ID IS '회원아이디';
COMMENT ON COLUMN USER_TEST.USER_PWD IS '비밀번호';
COMMENT ON COLUMN USER_TEST.PNO IS '주민등록번호';
COMMENT ON COLUMN USER_TEST.GENDER IS '성별';
COMMENT ON COLUMN USER_TEST.PHONE IS '연락처';
COMMENT ON COLUMN USER_TEST.ADDRESS IS '주소';
COMMENT ON COLUMN USER_TEST.STATUS IS '탈퇴여부';

PRIMARY KEY(기본키) 제약조건

- 테이블에서 한 행의 정보를 찾기 위해 사용할 컬럼을 의미한다.
- 테이블에 대한 식별자 역할을 한다.
- NOT NULL + UNIQUE 제약조건의 의미
- 한 테이블당 한 개만 설정할 수 있음
- 컬럼 레벨, 테이블 레벨 둘 다에서 설정 가능함
- 한 개 컬럼에 설정할 수 도 있고, 여러 개의 컬럼을 묶어서 설정할 수 있음

컬럼 레벨에서 PK설정

CREATE TABLE USER_PRIMARYKEY(
    USER_NO NUMBER CONSTRAINT PK_USER_NO PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50)
);

=> PK값(USER_NO)은 NULL값이 존재할 수 없고, UNIQUE (중복없음) 해야 한다.

테이블 레벨에서 PK 설정 (복합키로 설정)

CREATE TABLE USER_PRIMARYKEY2(
    USER_NO NUMBER,
    USER_ID VARCHAR2(20),
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    CONSTRAINT PK_USER_NO2 PRIMARY KEY(USER_NO, USER_ID)
);

PRIMARY KEY는 한테이블당 한 개인데 복합키로 설정..? 값 넣은 후 궁금해서 SELECCT해봄

USER_NO, USER_ID 복합키로 설정했으니 두 값이 동시에 중복이 되지 않는다.

FOREIGN KEY(외부키 / 외래키) 제약 조건

- 참조(REFERENCES) 된 다른 테이블에서 제공하는 값만 사용할 수 있음
- 참조 무결성을 위배하지 않게 하기 위해서 사용
- FOREIGN KEY 제약 조건에 의해서 테이블 간의 관계가 형성 됨
- 제공되는 값 외에는 NULL을 사용할 수 있음

컬럼 레벨일 경우

컬럼명 자료형(크기) [CONSTRAINT 이름] REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]

테이블 레벨일 경우

[CONSTRAINT 이름] FOREIGN KEY (적용할컬럼명) REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]

- 참조할 테이블의 참조할 컬럼명이 생략 되면 PRIMARY KEY로 설정 된 컬럼이 자동 참조할 컬럼이 됨
- 참조 될 수 있는 컬럼은 PRIMARY KEY 컬럼과, UNIQUE로 지정 된 컬럼만 외래키로 참조할 수 있음

USER_GRADE라는 테이블 생성 후 값 입력(부모테이블)

CREATE TABLE USER_GRADE(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT
  INTO USER_GRADE
(
  GRADE_CODE, GRADE_NAME
)
VALUES
(
  10, '일반회원'
);

INSERT
  INTO USER_GRADE
(
  GRADE_CODE, GRADE_NAME
)
VALUES
(
  20, '우수회원'
);

INSERT
  INTO USER_GRADE
(
  GRADE_CODE, GRADE_NAME
)
VALUES
(
  30, '특별회원'
);

참조할 테이블 USER_FOREIGNKEY 생성(GRADE_CODE)

CREATE TABLE USER_FOREIGNKEY(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER,
    CONSTRAINT FK_GRADE_CODE FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE (GRADE_CODE)
);

5명 값 입력함

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
  1, 'user01', 'pass01'
, '홍길동', '남', '010-1234-5678'
, 'hong123@greedy.com', 10
);

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
  2, 'user02', 'pass02'
, '유관순', '여', '010-7777-5678'
, 'yoo123@greedy.com', 10
);

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
  3, 'user03', 'pass03'
, '이순신', '남', '010-9999-5678'
, 'lee123@greedy.com', 30
);

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
  4, 'user04', 'pass04'
, '신사임당', '여', '010-8888-5678'
, 'shin123@greedy.com', NULL
);

INSERT
  INTO USER_FOREIGNKEY
(
  USER_NO, USER_ID, USER_PWD
, USER_NAME, GENDER, PHONE
, EMAIL, GRADE_CODE
)
VALUES
(
  5, 'user05', 'pass05'
, '윤봉길', '남', '010-1111-5678'
, 'yoon123@greedy.com', 50
);

마지막 5번째에서 GRADE_CODE 50으로 입력하니 아래와 같은 오류가 뜬다.

(부모테이블 USER_GRADE에서 GRADE_CODE를 10, 20, 30으로만 설정함)

USER_FOREIGNKEY 테이블에서 회원 아이디, 이름, 성별, 연락처, 회원 등급명 조회

SELECT
       UF.USER_ID
     , UF.USER_NAME
     , UF.GENDER
     , UF.PHONE
     , UG.GRADE_NAME
  FROM USER_FOREIGNKEY UF
  LEFT JOIN USER_GRADE UG ON(UF.GRADE_CODE = UG.GRADE_CODE);

삭제 옵션
- 부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떤 식으로 처리할 것인지에 대한 내용을 설정할 수 있다.

DELETE
  FROM USER_GRADE
 WHERE GRADE_CODE = 10;

- ON DELETE RESTRICT 로 기본 지정 되어 있음
- FOREIGN KEY로 지정 된 컬럼에서 사용 되고 있는 값일 경우 제공하는 컬럼의 값은 삭제하지 못한다.

COMMIT;

DELETE
  FROM USER_GRADE
 WHERE GRADE_CODE = 20;
 
SELECT 
       UG.*
  FROM USER_GRADE UG;
  
ROLLBACK;

ON DELETE SET NULL

- 부모 키를 삭제 시 자식 키를 NULL로 변경하는 옵션

CREATE TABLE USER_GRADE2(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

USER_GRADE2 생성

CREATE TABLE USER_GRADE2(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

USER_GRADE2를 부모테이블로 받을 FOREIGNKEY2 테이블 생성

CREATE TABLE USER_FOREIGNKEY2(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER,
    CONSTRAINT FK_GRADE_CODE2 FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE2 (GRADE_CODE) ON DELETE SET NULL
);

COMMIT;

DELETE 
  FROM USER_GRADE2
 WHERE GRADE_CODE = 10;
 
SELECT
       UG.*
  FROM USER_GRADE2 UG;
  
SELECT
       UF.*
 FROM USER_FOREIGNKEY2 UF;

ON DELETE CASCADE

- 부모 키 삭제 시 자식 키를 가진 행도 함께 삭제

USER_GRADE3 생성

CREATE TABLE USER_GRADE3(
    GRADE_CODE NUMBER PRIMARY KEY,
    GRADE_NAME VARCHAR2(30) NOT NULL
);

USER_GRADE3 부모키로 참조할 USER_FOREIGNKEY3 테이블 생성

CREATE TABLE USER_FOREIGNKEY3(
    USER_NO NUMBER PRIMARY KEY,
    USER_ID VARCHAR2(20) UNIQUE,
    USER_PWD VARCHAR2(30) NOT NULL,
    USER_NAME VARCHAR2(30),
    GENDER VARCHAR2(10),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(50),
    GRADE_CODE NUMBER,
    CONSTRAINT FK_GRADE_CODE3 FOREIGN KEY(GRADE_CODE) REFERENCES USER_GRADE3 (GRADE_CODE) ON DELETE CASCADE
);

(값입력 코드 생략)

DELETE
  FROM USER_GRADE3
 WHERE GRADE_CODE = 10;
 
SELECT
       UG.*
  FROM USER_GRADE3 UG;
  
SELECT
       UF.*
  FROM USER_FOREIGNKEY3 UF;

서브쿼리를 이용한 테이블 생성
- 컬럼명, 데이터 타입, 값이 복사되고 제약 조건은 NOT NULL만 복사 됨

CREATE TABLE EMPLOYEE_COPY2
AS
SELECT
       E.EMP_ID
     , E.EMP_NAME
     , E.SALARY
     , D.DEPT_TITLE
     , J.JOB_NAME
  FROM EMPLOYEE E
  LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
  LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);
  
  SELECT
       EC.*
  FROM EMPLOYEE_COPY2 EC;

제약조건 추가

ALTER TABLE 테이블명 ADD PRIMARY KEY (컬럼명);
ALTER TABLE 테이블명 ADD FOREIGN KEY (컬럼명) REFERENCES 테이블명 (컬럼명);
ALTER TABLE 테이블명 ADD UNIQUE (컬럼명);
ALTER TABLE 테이블명 ADD CHECK (컬럼명 비교연산자 비교값);
ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

[실습]

- EMPLOYEE 테이블의 DEPT_CODE에 외래키 제약조건 추가
참조 테이블은 DEPARTMENT, 참조컬럼은 DEPARTMENT의 기본키

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT (DEPT_ID);

- DEPARTMENT 테이블의 LOCATION_ID에 외래키 제약조건 추가

참조 테이블은 LOCATION, 참조 컬럼은 LOCATION의 기본키

ALTER TABLE DEPARTMENT ADD FOREIGN KEY (LOCATION_ID) REFERENCES LOCATION (LOCAL_CODE);

- EMPLOYEE 테이블의 JOB_CODE에 외래키 제약조건 추가
참조 테이블은 JOB 테이블, 참조 컬럼은 JOB테이블의 기본키

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE);

- EMPLOYEE 테이블의 SAL_LEVEL에 외래키 제약조건 추가
참조테이블은 SAL_GRADE테이블, 참조 컬럼은 SAL_GRADE테이블 기본키

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (SAL_LEVEL) REFERENCES SAL_GRADE (SAL_LEVEL);

- EMPLOYEE테이블의 ENT_YN컬럼에 CHECK제약조건 추가('Y','N')
단, 대 소문자를 구분하기 때문에 대문자로 설정

ALTER TABLE EMPLOYEE ADD CHECK (ENT_YN IN ('Y', 'N'));

- EMPLOYEE테이블의 SALARY 컬럼에 CHECK제약조건 추가(양수)

ALTER TABLE EMPLOYEE ADD CHECK (SALARY > 0);

- EMPLOYEE테이블의 EMP_NO컬럼에 UNIQUE 제약조건 추가

ALTER TABLE EMPLOYEE ADD UNIQUE (EMP_NO);