[Oracle] DDL 기초 및 간단한 예제(create, drop, alter)

DDL

• Data Definition Language
• 데이터 정의어
• 데이터베이스 객체를 생성/수정/삭제한다.
• 데이터베이스 객체 : 테이블, 뷰, 인덱스, 프로지서, 트리거, 제약사항, 시노닙 등
• 예약어 : CREATE, ALTER, DROP 등

1. CREATE

• 테이블 생성하기 == 스키마 정의하기 == 속성(컬럼) 정의하기 == 컬럼의 이름,자료형,제약을 정의

CREATE table 테이블명
(
	컬럼 정의,
	컬럼 정의,
	컬럼 정의,
	컬럼명 자료형(길이),
	컬럼명 자료형(길이) NULL 제약사항
);

제약 사항, Constraint

• 해당 컬럼에 들어갈 데이터(값)에 대한 조건
  1. 조건을 만족하면 > 대입을 실행한다.
  2. 조건을 불만족하면 > 에러를 낸다.
• 유효성 검사 도구의 역할을 한다.
• 데이터 무결성을 보장하기 위한 도구(****)

1. NOT NULL

• 해당 컬럼이 반드시 값을 가져야 한다.(필수값)
• 해당 컬럼에 값이 없으면 에러 발생

2. PRIMARY KEY, PK

• 기본키
• 테이블의 행을 구분하기 위한 제약사항
• 행을 식별하는 수많은 키(후보키)들 중 대표로 선정된 키
• 모든 테이블은 반드시 한개의 기본키가 존재해야 한다.(***)
• 중복값을 가질 수 없다. > unique 특성 포함
• 값을 반드시 가진다. > not null 특성 포함

3. FOREIGN KEY, FK

• 한 테이블(자식테이블)을 다른 테이블(부모테이블)과 연결해주는 역할
• REFERENCES 부모테이블명(PK)

-- 직원 명단 테이블
CREATE TABLE tblStaff
(
	seq NUMBER PRIMARY KEY,
	name varchar2(30) NOT NULL,
	salary NUMBER NOT NULL,
	address varchar2(300) NOT NULL
); -- 부모테이블

-- 프로젝트 테이블(FK 추가)
CREATE TABLE tblProject(
	seq NUMBER PRIMARY KEY, 								-- 프로젝트 번호(PK)
	project varchar2(100) NOT NULL,							-- 프로젝트명
	staff_seq NUMBER NOT NULL REFERENCES tblstaff(seq)		-- 담당 직원 번호(FK)
); -- 자식테이블

 

4. UNIQUE

• 유일한 값 > 레코드간의 중복값을 가질 수 없다.
• PK와 비슷하지만 null을 가질 수 있다. > 식별자가 될 수 없다.
  ex) 교실
  - 학생(번호(pk), 이름(not null), 직책)
  1, 홍길동, 반장
  2, 아무개, null
  3, 하하하, 부반장
  4, 테스트, null
• PK = UQ + NN

5. CHECK

• 사용자 정의형
• where 절의 조건을 컬럼의 제약사항으로 적용
• check(category IN ('할일','공부','약속','가족','개인'))

6. DEFAULT

• 기본값 설정
• insert/update 작업 시 컬럼에 값이 없으면 null대신 미리 설정한 값을 대입한다.

CREATE TABLE tblMemo
(
	seq number(3) PRIMARY KEY,								--메모번호(PK)
	name varchar2(30) unique,								--작성자(UQ)	> 한 사람당 하나의 메모만 쓰기 가능
	memo varchar2(1000) NOT NULL,							--메모(NN) > 공백 불가
	regdate DATE DEFAULT sysdate							--작성날짜 > 공백시 현재 날짜 자동 대입
	priority NUMBER(1)	check(priority BETWEEN 1 AND 3)	,	--중요도(1(중요), 2(보통), 3(안중요))
	-- 카테고리(할일,공부,약속,가족,개인)
	category varchar2(30) check(category IN ('할일','공부','약속','가족','개인'))
);

제약사항을 만드는 방법

1. 컬럼 수준에서 만드는 방법

• 위 내용
• 컬럼을 선언할 때 제약사항도 같이 선언하는 방법

2. 테이블 수준에서 만드는 방법

• 가독성 향상
• not null, default는 불가능하다

3. 외부에서 만드는 방법

CREATE TABLE tblMemo
(
	seq number(3),	-- CONSTRAINT 생략 가능 / tblmemo_seq_pk 생략하면 자동으로 생성됨
	name varchar2(30),
	memo varchar2(1000) DEFAULT '메모',
	regdate DATE NOT NULL,
	
	-- 테이블 수준에서 제약사항 정의
	CONSTRAINT tblmemo_seq_pk PRIMARY key(seq),		-- PRIMARY key(컬럼)
	CONSTRAINT tblmemo_name_uq unique(name),
	CONSTRAINT tblmemo_memo_ck check(LENGTH(memo)>=10)
	
);

 

2. DROP

• 테이블 초기화한다.

DROP table tblMemo;

Delete, Truncate, Drop 차이점

3. ALTER

• 테이블 정의 수정한다.
  (스키마 수정, 컬럼 수정, 컬럼명 or 자료형(길이) or 제약사항 등)
• ALTER table 테이블명 [ADD|DROP|MODIFY]
• 되도록 테이블 수정하는 상황을 발생시키면 안된다!!!

테이블 수정해야 하는 상황 발생한다면

1. 테이블 삭제(DROP) → 테이블 DDL(CREATE) 수정 → 수정된 DDL로 새롭게 테이블 생성
   a. 기존 테이블에 데이터가 없었을 경우 > 아무 문제 없음
   b. 기존 테이블에 데이터가 있었을 경우 > 미리 데이터 백업 > 테이블 삭제 > 수정된 테이블 다시 생성 > 데이터 복구

• 개발 중에 사용 가능
• 공부할 때 사용 가능
• 서비스 운영 중에는 거의 불가능한 방법

 

2. ALTER 명령어 사용 > 기존 테이블의 구조 변경
      a. 기존 테이블에 데이터가 없었을 경우 > 아무 문제 없음
      b. 기존 테이블에 데이터가 있었을 경우 > 경우에 따라 비용 차이

• 개발 중에 사용 가능
• 공부할 때 사용 가능
• 서비스 운영 중에는 경우에 따라 가능

 

컬럼 추가하기

ALTER TABLE 테이블명 ADD (컬럼명 컬럼속성 컬럼옵션);

-- DEFAULT 값을 입력하면 NOT NULL 컬럼을 추가 할 수있다.
ALTER TABLE tbledit	ADD (color varchar2(30) DEFAULT 'white' NOT NULL);

 

컬럼 삭제하기

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

ALTER TABLE tbledit	DROP COLUMN color;

 

컬럼 수정하기

ALTER TABLE 테이블명 MODIFY (컬럼명 컬럼속성);

-- 제약사항 수정 (NOT NULL -> NULL)
ALTER TABLE tbledit MODIFY (DATA varchar2(100) NULL);

-- 컬럼 길이 수정(varchar2(20) -> varchar2(100))
ALTER TABLE tbledit	MODIFY (DATA varchar2(100));

-- 컬럼 자료형 수정 (주의!!!!)
-- ORA-01439: column to be modified must be empty to change datatype
ALTER TABLE tbledit MODIFY (DATA NUMBER);

ORA-01439: column to be modified must be empty to change datatype

• 기존 데이터가 있는 경우 테이블을 비우고(delete) 자료형을 수정해줘야 한다.