DDL (Date Dfinition Language)

CREATE 문

CREATE USER 문

• 계정 생성

CREATE USER 계정명
IDENTIFIED BY 비밀번호;
-- 추가 옵션
-- ...

• 계정은 대소문자 구분하지 않음
• 비밀번호는 대소문자 구분
• 계정 생성에는 권한이 필요

CREATE TABLE 문

• 테이블 생성

1. 일반적인 방법

• 형식

CREATE [GLOBAL TEMPORARY] TABLE [스키마명.]테이블명(
  컬럼명 자료형 [DEFAULT 표현식] [제약조건]
  , ...
);

CREATE TABLE emp
(
-- 사원들을 구별할 수 있는 고유한 키로 사원번호(empno)컬럼을 설정
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,  --PRIMARY KEY(PK) == 고유키
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4), 
    HIREDATE DATE, 
    SAL NUMBER(7,2), 
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT  --부서테이블(dept)의 부서번호(deptno)를 참조
);

2. 서브쿼리를 이용한 방법
   • 쿼리를 실행하여 그 결과의 테이블을 만드는 방법
   • 기존 테이블을 복사 후 수정해서 사용하기에 용이함
   • 테이블 구조만 복사하기 위해서 사용하기도 함
   • 제약조건은 복사 안됨(NOT NULL 제외)

• 형식

CREATE TABLE 테이블명 [(컬럼명 [, ...])] 

CREATE TABLE tbl_emp10
AS (
    SELECT *
    FROM emp
    WHERE deptno=10
);
-- emp 테이블에서 deptno가 10인 레코드로만 tbl_emp10 테이블 생성
-- 컬럼명 생략 시 서브쿼리와 같은 컬럼명

CREATE TABLE tbl_copy
AS (
    SELECT *
    FROM emp
    WHERE 1=0 -- 항상 거짓인 조건
);
-- 테이블 레코드를 제외하고 구조만 복사

제약조건

1. 데이터의 '무결성 제약조건'(integrity constraint)'을 위해 레코드 추가, 수정, 삭제 시 적용되는 규칙
2. 테이블 삭제 방지의 역할도 함

• 데이터 무결성

종류	내용
개체 무결성 (Entity Integrity)	릴레이션에 저장되는 튜블의 유일성을 보장하기 위한 제약조건
참조 무결성 (Relational Integrity)	릴레이션 간 데이터의 "일관성"을 보장하기 위한 제약조건
개체 무결성 (Entity Integrity)	컬럼의 값의 데이터 타입, 길이, 기본 키, 유일성, null허용,... 제약조건

• 제약조건 종류

  제약조건	타입	내용
  PRIMARY KEY (PK)	P	기본키(고유키, PRIMARY KEY) 유일한 값들로 이루어져야 함 NULL 허용 X
  FOREIGN KEY (FK)	R	외래키(참조키, FOREIGN KEY) 다른 테이블의 기본키를 참조
  UNIQUE (UK)	U	유일성 제약조건 해당 컬럼에 데이터들은 중복되지 않고 유일
  CHECK (CK)	C	조건 설정
  NOT NULL (NN)	C	NULL 허용 X

선언

1. 컬럼 레벨 (column level)

• IN-LINE constraint 방법
• CREATE TABLE 문에서 컬럼 선언 시 같은 라인에 작성

CREATE TABLE tbl_column_level
(
  -- 고유키
  empno NUMBER(4) NOT NULL CONSTRAINTS PK_TBLCOLUMNLEVEL_EMPNO PRIMARY KEY,
  -- NOT NULL
  ename VARCHAR2(20) NOT NULL,
  -- 외래키
  deptno NUMBER(2) NOT NULL CONSTRAINTS FK_TBLCOLUMNLEVEL_DEPTNO REFERENCES dept(deptno),
  -- CHECK
  kor NUMBER(3) CONSTRAINTS CK_TBLCOLUMNLEVEL_KOR CHECK (kor BETWEEN 0 AND 100),
    -- 유일성 제약조건
  email VARCHAR2(50) CONSTRAINTS UK_TBLCOLUMNLEVEL_EMAIL UNIQUE,
  city VARCHAR2(20)
);

2. 테이블 레벨 (table level)

• OUT_OF_LINE constraint 방법
• CREATE TABLE 문에서 컬럼 선언 라인과 다른 줄에 선언
  • NOT NULL은 선언 불가
• ALTER TABLE 문으로 추가 시 사용

CREATE TABLE tbl_table_level
(
    empno NUMBER(4) NOT NULL,
    ename VARCHAR2(20) NOT NULL,
    deptno NUMBER(2) NOT NULL,
    kor NUMBER(3),
    email VARCHAR2(50),
    city VARCHAR2(20),
    
    --PK 제약조건 설정
    CONSTRAINTS PK_TBLTABLELEVEL_empno PRIMARY KEY(empno), --복합키
    CONSTRAINTS FK_TBLTABLELEVEL_DEPTNO FOREIGN KEY(deptno) REFERENCES dept(deptno),
    CONSTRAINTS UK_TBLTABLELEVEL_EMAIL UNIQUE(email),
    CONSTRAINTS CK_TBLTABLELEVEL_KOR CHECK (kor BETWEEN 0 AND 100)
);

삭제

ALTER TABLE 테이블명
DROP PRIMARY KEY;

ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명

활성화 / 비활성화

1. 비활성화 (DISABLE)
   • 제약조건을 체크하지 않음

ALTER TABLE 테이블명
DISABLE CONSTRAINT 제악조건명 [CASCADE];

2. 활성화 (ENABLE)
   • 비활성화된 제약조건을 활성화

ALTER TABLE 테이블명
ENABLE CONSTRAINT 제악조건명;

FOREIGN KEY 삭제 옵션

1. ON DELECT CASCADE

• 부모키 삭제 시, 참조키에 해당되는 레코드들도 삭제

ALTER TABLE tbl_emp
ADD CONSTRAINT FK_TBLEMP_DEPTNO FOREIGN KEY (deptno) REFERENCES tbl_dept (deptno) ON DELETE CASCADE;

DELETE FROM tbl_dept
WHERE deptno = 30;

2. ON DELECT SET NULL

• 부모키 삭제 시, 참조키에 해당되는 레코드들의 해당 컬럼은 NULL값으로 바뀜

ALTER TABLE tbl_emp
ADD CONSTRAINT FK_TBLEMP_DEPTNO FOREIGN KEY (deptno) REFERENCES tbl_dept (deptno) ON DELETE SET NULL;

DELETE FROM tbl_dept
WHERE deptno = 30;

ALTER 문

ALTER USER 문

• 계정을 수정

ALTER USER hr 
IDENTIFIED BY lion
ACCOUNT UNLOCK;;
-- hr계정의 비밀번호를 lion으로 변경 + 잠금 상태 해제

ALTER TABLE 문

• 테이블의 컬럼을 추가, 삭제, 변경에 사용

1. 추가
   • 컬럼을 추가
   • 제약조건을 추가

ALTER TABLE 테이블명
ADD (
  컬럼명 자료형 [DEFAULT 표현식] [제약조건]
  , ...
)

2. 수정
   • 컬럼을 수정 (한번에 한 컬럼씩 삭제)
   • 제약조건은 수정이 불가

ALTER TABLE 테이블명
MODIFY (
  컬럼명 자료형 [DEFAULT 표현식]
  , ...
)

3. 컬러명 변경

ALTER TABLE 테이블명
RENAME COLUMN 전_컬럼명 TO 후_컬럼명

4. 삭제
   • 컬럼을 삭제
   • 제약조건을 삭제

ALTER TABLE 테이블명
DROP COLUMN 컬럼명;

ALTER TABLE 테이블명
DROP [CONSTRAINT] 제약조건;

RENAME

• 테이블, 뷰, ... 이름변경

RENAME 전_컬럼명 TO 후_컬럼명

DROP 문

DROP USER 문

• 계정을 삭제
• 삭제 할 떄, CASCADE 옵션은 계정이 소유하는 객체(스키마)들까지 같이 삭제

DROP USER scott CASCADE;

DROP TABLE 문

• 테이블을 삭제
• 형식

DROP TABLE [스키마명.]테이블명 [CASCADE CONSTRAINTS] [PURGE];

• CASCADE CONSTRAINTS
  • 무결성 제약조건 삭제
  • 대상 테이블의 기본키를 다른 테이블에서 참조키로 사용 중일 떄 삭제하기 위함
• PURGE
  • 완전히 삭제