Oracle Database - DDL – Data Definition Language

DB-Oracle

Oracle Database - DDL – Data Definition Language

jmeen 2021. 8. 6. 14:10

728x90

DDL 요약

CREATE TABLE : 테이블 생성
ALTER TABLE : 테이블 관련 변경
DROP TABLE : 테이블 삭제
RENAME : 이름 변경
TRUNCATE : 테이블의 모든 데이터 삭제 (주의! 절대 복구 안됨)
COMMENT : 테이블에 설명 추가

테이블 생성

- CREATE TABLE 문 이용
- Syntax

CREATE TABLE [schema.]table_name
  (column datatype [DEFAULT expr]
          [column_constraints],
          ......,
  [table_constraints]);

Oracle에서의 Table 관리

Oracle은 Database의 공간을 Tablespace라는 논리적 공간으로 분할하여 관리
Table을 만들기 위해서는 CREATE TABLE 권한과 객체를 생성할 수 있는 저장장소가 있어야 한다
DBA는 user에게 권한을 주는 데이터 조작어(DCL) 명령을 사용한다
다른 user의 table을 참조하려면 참조되는 테이블은 동일한 데이터베이스 내에 있어야 한다
참조하는 테이블이 제약조건을 만드는 user의 소유가 아니라면 소유자 이름(Schema)이 제약조건에서 참조되는 table 이름 앞에 붙어야 한다
DEFAULT 옵션을 이용하면 column의 기본값을 지정할 수 있다. 새로운 row 입력시 해당 컬럼에 값이 입력되지 않으면 default 옵션에 설정된 값이 자동으로 부여된다

- 테이블명, 컬럼명, 데이터 타입 등 정의

- [연습] 아래 DDL 쿼리를 이용, book 테이블을 만들어 봅시다

CREATE TABLE book (
  book_id NUMBER(5),
  title VARCHAR2(50),
  author VARCHAR2(10),
  pub_date DATE DEFAULT SYSDATE
);

DESC 명령을 이용, 생성된 테이블이 원하는 구조대로 만들어졌는지 확인해 봅니다

Subquery를 이용한 테이블 생성

- Subquery의 결과와 동일한 테이블 생성됨
- 질의 결과 레코드들이 포함됨
- NOT NULL 제약 조건만 상속됨

CREATE TABLE it_emps AS(
    SELECT * FROM HR.employees
    WHERE job_id LIKE 'IT_%'
    );

Naming Rules

테이블, 컬럼 등의 이름 명명 규칙

문자로 시작
30자 이내
A-Z, a-z, 0-9, _, $, #
같은 사용자가 소유한 다른 객체의 이름과 겹치지 않아야 함
(다른 사용자 소유의 객체와는 같을 수도 있음)
오라클 예약어는 사용할 수 없음

사용자 테이블 (User Tables)

- 사용자에 의해 만들어지고 관리되는 테이블의 집합
- 사용자 정보를 포함함

Data Dictionary
- 오라클 서버에 의해 만들어지고 관리되는 테이블의 집합
- 데이터베이스 정보를 포함

사용 빈도가 가장 높은 타입들

참고
- VARCHAR2와 CHAR의 차이점을 구분한다
- INT, FLOAT 등의 ANSI Type도 내부적으로 NUMBER(38)로 변환됨

ALTER TABLE

* 컬럼 추가 (ADD)

ALTER TABLE book ADD (pubs VARCHAR2(50));

* 컬럼 수정 (MODIFY)

ALTER TABLE book MODIFY (title VARCHAR2(100));

* 컬럼 삭제 (DROP)

 ALTER TABLE book DROP (author);

* UNUSED 컬럼

ALTER TABLE book SET UNUSED (author);
ALTER TABLE book DROP UNUSED COLUMNS;

기타 테이블 관련 명령

* 테이블 삭제 (DROP TABLE)

DROP TABLE book;

* 데이터 삭제 (TRUNCATE TABLE)

TRUNCATE TABLE book;

* Comment

COMMENT ON TABLE book IS 'this is comment';
SELECT * FROM user_tab_comments;

* RENAME

RENAME book TO article;

* ROLLBACK 대상이 아님

Constraint(제약조건)

Database 테이블 레벨에서 특정한 규칙을 설정함
예상치 못한 데이터의 손실이나 일관성을 어기는 데이터의 추가, 변경 등을 예방

종류

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

Syntax

CREATE TABLE 테이블명 (
컬럼명 DataType [DEFAULT 기본값][컬럼 제약 조건],
컬럼명 DataType [DEFAULT 기본값][컬럼 제약 조건],
…
[테이블 제약조건] … );

- 컬럼 제약 조건 : [CONSTRAINT 이름] constraint_type
- 테이블 제약 조건 : [CONSTRAINT 이름] constraint_type(column, …)

주의
- 제약조건에 이름을 부여하지 않으면 Oracle이 Sys-Cn의 형태로 자동 부여

NOT NULL

- NULL 값을 허용하지 않음
- 컬럼 형태로만 제약조건 정의할 수 있음(테이블 제약 조건 불가

CREATE TABLE book (
book_id NUMBER(5) NOT NULL
);

UNIQUE

- 중복된 값을 허용하지 않음 (NULL은 들어올 수 있음)
- 복합 컬럼에 대해서도 정의 가능
- 자동적으로 인덱스 생성

CREATE TABLE book (
book_id NUMBER(5) CONSTRAINT c_book_u UNIQUE
);

**PRIMARY KEY (*)**

- NOT NULL + UNIQUE (인덱스 자동 생성)
- 테이블 당 하나만 나올 수 있음
- 복합 컬럼에 대하여 정의 가능 (순서 중요)

CREATE TABLE book (
...
PRIMARY KEY (book_id)
);

CHECK (Only oracle)

- 임의의 조건 검사 조건식이 참이어야 변경 가능
- 동일 테이블의 컬럼만 이용 가능

CREATE TABLE book (
rate NUMBER CHECK (rate IN (1,2,3,4,5))
);

[연습] author 테이블 생성
- book 테이블의 author 컬럼을 author 테이블로 분리, 관리하고자 합니다.
- 다음 조건으로 author 테이블을 생성해 봅시다

CREATE TABLE author (
    author_id NUMBER(10),
    author_name VARCHAR2(50) NOT NULL,
    author_desc varchar2(500),
    -- 컬럼 정의가 끝난 다음 테이블 제약
    PRIMARY KEY (author_id)
);

[연습] book 테이블 변경
- book 테이블의 author 컬럼을 삭제해 봅니다
- book 테이블에 author_id 컬럼을 추가합니다
- author 테이블의 author_id 컬럼과 같은 형식(NUMBER(10))으로 지정합니다

--  book 테이블의 author 컬럼을 삭제
alter table book drop column author;

-- author 테이블 참조를 위한 컬럼 author_id 추가
alter table book
add (author_id number(10));

-- book 테이블의 book_id 도 number(10)으로 변경
alter table book
modify (book_id number(10));
desc book;

FOREIGN KEY

참조 무결성 제약
- 일반적으로 REFERENCE 테이블의 PK를 참조
- REFERENCE 테이블에 없는 값은 삽입 불가
- REFERENCE 테이블의 레코드 삭제시 동작
- ON DELETE CASCADE : 해당하는 FK를 가진 참조행도 삭제
- ON DELETE SET NULL : 해당하는 FK를 NULL로 바꿈

CREATE TABLE book (
  …
  author_id NUMBER(10),
  CONSTRAINT c_book_fk FOREIGN KEY (author_id)
  REFERENCE author(id)
  ON DELETE SET NULL
);

-- book.book_id 에 PK 제약조건 부여
alter table book
ADD CONSTRAINT pk_book_id PRIMARY KEY (book_id);

-- book.author_id를 author.author_id를 참조하도록 제약
alter table book
add constraint fk_author_id FOREIGN KEY (author_id)
                            REFERENCES author(author_id)
                            on delete cascade;

ADD / DROP CONSTRAINTS

제약조건 추가

- ALTER TABLE 테이블명 ADD CONSTRAINT …
- NOT NULL은 추가 못함

ALTER TABLE book ADD CONSTRAINT c_book_fk
FOREIGN KEY (author_id) REFERENCES author(author_id);

제약조건 삭제

- ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명
- PRIMARY KEY의 경우 FK 조건이 걸린 경우에는 CASCADE로 삭제해야 함

ALTER TABLE book DROP CONSTRAINT c_book_fk;
ALTER TABLE author DROP PRIMARY KEY CASCADE;

ENABLE/DISABLE CONSTRAINTS

제약조건 비활성화

- 제약조건 검사를 중지함
- CASCADE를 사용하여 의존되어 있는 다른 조건을 함께 중지시킬 수 있음
- 대규모 데이터 변경 등의 속도를 빠르게 함
- UNIQUE, PRIMARY KEY의 경우 인덱스 제거됨

ALTER TABLE book DISABLE CONSTRAINT c_book_fk CASCADE

제약조건 활성화

- 중지되어 있던 제약조건 검사를 활성화
- UNIQUE, PRIMARY KEY의 경우 인덱스 자동 생성

ALTER TABLE book ENABLE CONSTRAINT c_book_fk CASCADE;

- 제약조건이 걸려 있는 테이블이나 컬럼은 삭제시 에러 발생

ALTER TABLE book DROP (author_id) CASCADE CONSTRAINT;

- 컬럼이나 테이블을 DROP 할 때 관련 제약조건도 함께 삭제할 때

DROP TABLE book CASCADE CONSTRAINT;

Data Dictionary

Oracle이 관리하는 모든 정보를 저장하는 카탈로그
- 내용
- 모든 스키마 객체 정보, 스키가 객체의 공간 정보, 컬럼의 기본값, 제약 조건 정보, 오라클 사용자 정보,

권한 및 룰 정보, 기타 데이터베이스 정보 …
- Base-Table과 View로 구성됨
- VIEW의 Prefix
- USER : 로그인한 사용자 레벨
- ALL : 모든 사용자 정보
- DBA : 관리자
- SYS scheme에 속함
- 주의
- DICTIONARY의 테이블이나 컬럼 이름은 모두 대문자 사용!

- 모든 Dictionary 정보 확인

SELECT * FROM DICTIONARY

- 사용자 스키마 객체 확인 (테이블)

SELECT object_name
FROM user_objects
WHERE object_type = 'TABLE';

- 제약조건 확인 (BOOK 테이블의 예)

SELECT constraint_name, constraint_type,search_condition
  FROM user_constraints
  WHERE table_name = ‘BOOK';

- 제약조건 컬럼 확인

SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = ‘EMP';

- 모든 사용자 확인 (dba에서 확인 가능)

SELECT username, default_tablespace, temporary_tablespace
FROM DBA_USERS;