[Oracle] DDL - CREATE, ALTER

DDL (Date Definition Language)

    - 데이터 베이스 내의 객체(Object)를 생성, 변경,삭제를 위한 명령
    - CREATE, ALTER, DROP이 제공

1. CREATE

    - CREATE TABLE 명령을 사용하여 테이블 생성

사용형식

CREATE TABLE 테이블명 (
	컬럼명 데이터타입 [(크기)] [DEFAULT 값] [,]
                        :
	컬럼명 데이터타입 [(크기)] [DEFAULT 값] [,]
	[CONSTRAINT 기본키설정명 PRIMARY KEY(컬럼명[,컬럼명,...]) [,]
	CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명) REFERENCES 테이블명(컬럼명) [ON DELETE CASCADE] [,]
                                       :
	CONSTRAINT 외래키설정명 FOREIGN KEY(컬럼명) REFERENCES 테이블명(컬럼명) [ON DELETE CASCADE] [,] );

테이블명	생성할 테이블이름으로 기존의 테이블명과 중복되어서 안됨
컬럼명	테이블에 포함될 컬럼명으로 하나의 테이블에서 유일한 값이어야하며  다른 테이블 내에서 같은 이름이 사용될 수 있음
데이터타입	오라클에서 사용되는 자료 타입
DEFAULT 값	데이터 입력시(INSERT 문) 사용자가 해당 컬럼에 저장될 값을 지정하지 않으면 자동으로 저장될 값
기본키설정명	기본키설정에 부여되는 이름으로 유일한 값이어야함 (PK_테이블명쓰면 기본키 중복x)
PRIMARY KEY (컬럼명[,컬럼명,...]	기본키로 정의할 컬럼명. 기본키가 하나 이상의 컬럼으로 구성되면 ','로 구분하여 기술
외래키설정명	외래키설정에 부여되는 이름으로 유일한 값이어야하며 하나 이상의  외래키가 설정되면 각 각 다른 이름이 부여되어야 함
FOREIGN KEY(컬럼명)	외래키로 사용될 컬럼명 (위에 있는 컬럼 중에 하나)
REFERENCES  테이블명(컬럼명)	해당 외래키가 참조하는 부모 테이블명과 부모 테이블에서 사용하고있는 컬럼명 (위에 있는 컬럼명의 이름이 있어야함)
ON DELETE CASCADE (예약어)	부모테이블에서 해당 행이 삭제(DELETE) 될 경우 자식 테이블에서  먼저 해당 자료를 삭제한 후 부모 테이블의 자료를 삭제할 수 있도록 설정

[ ] : 생략할 수 있는 값

[,] : 다음 컬럼이 없을 시 삭제

CONSTRAINT : 제약

---

사용 예시

---

1. 상품 테이블 (GOODS)

컬럼명	데이터타입	기본값	PK/FK 여부
GID	VARCHAR2(4)		PK
GNAME	VARCHAR2(50)
PRICE	NUMBER(7)	0

CREATE TABLE GOOD(
	GID VARCHAR2(4),
    GNAME VARCHAR2(50),
    PRICE NUMBER(7) DEFAULT 0,
    CONSTRAINT pk_goods PRIMARY KEY (GID) );

2. 고객테이블(CUSTOMERS)

컬럼명	데이터타입	기본값	PK/FK 여부
CID	CHAR(4)		PK
CNAME	VARCHAR2(50)

CREATE TABLE CUSTOMERS (
	ID  CHAR(4),
	CNAME   VARCHAR2(50),
	CONSTRAINT pk_customers PRIMARY KEY (CID) );

3. 주문테이블(ORDERS)

컬럼명	데이터타입	기본값	PK/FK
ORDER_ID	NUMBER(5)		PK
ORDER_DATE	DATE
CID	CHAR(4)		FK

CREATE TABLE ORDERS (
	ORDER_ID    NUMBER(5),
	ORDER_DATE  DATE,
	CID         CHAR(4),
	CONSTRAINT pk_orders PRIMARY KEY (ORDER_ID),
	CONSTRAINT fk_orders_customers FOREIGN KEY (CID)
		--CONSTRAINT fk_ors_cust FOREIGN KEY (CID)       >줄여써도 가능
		REFERENCES CUSTOMERS(CID) );

4. 주문상품 테이블(ORDER_GOODS)

컬럼명	데이터타입	기본값	PK / FK 여부
ORDER_ID	NUMBER(5)		PK & FK
GID	VARCHAR2(4)		PK & FK
ORDER_QTY	NUMBER(4)	0

CREATE TABLE ORDER_GOODS (
	ORDER_ID    NUMBER(5),
	GID         VARCHAR2(4),
	ORDER_QTY   NUMBER(4) DEFAULT 0,
	CONSTRAINT pk_order_goods PRIMARY KEY (ORDER_ID,GID),
	CONSTRAINT fk_ogoods_orders FOREIGN KEY (ORDER_ID)  REFERENCES ORDERS(ORDER_ID),
	CONSTRAINT fk_ogoods_goods FOREIGN KEY (GID)   REFERENCES GOODS(GID));

PRIMARY KEY 설정 (KEY1 , KEY2, .....)

2. ALTER

• 테이블 명 변경
• 컬럼 추가
• 컬럼 삭제
• 컬럼 데이터 타입 변경 (데이터 존재 시 더 작은 타입으로 변경 불가능)
• 컬럼명 변경에 사용

1) 테이블명 변경

ALTER TABLE 원본테이블명 RENAME TO 변경된테이블명;

   - 부모 테이블의 이름이 변경되면 외래키 설정에 사용된 부모테이블의 이름은 자동변경됨

   - 변경된 테이블명은 ROLLBACK되지 않음

---

사용 예시

---

   - GOODS 테이블을 PRODUCT로 변경

ALTER TABLE GOODS RENAME TO PRODUCT;

2) 테이블 칼럼 추가

ALTER TABLE 테이블명 ADD (컬럼명 데이터타입 [ (크기) ] );

   - 해당 테이블에 새로운 컬럼을 추가 삽입

---

사용 예시

---

   - 주문(ORDERS)에 주문금액 (ORDER_AMT NUMBER(8) ) 컬럼을 추가

ALTER TABLE ORDERS ADD (ORDER_AMT NUMBER(8));

3) 데이터 컬럼 변경 (데이터 타입 OR 크기)

ALTER TABLE 테이블명 MODIFY (컬럼명 데이터타입 [ (크기) ] ) ;

   - 해당 테이블에 컬럼의 크기나 데이터 타입을 변경

   - 이미 자료가 저장된 경우 작은 크기로 변경이나 변환될 수 없는 타입으로의 변환은 허용되지 않음

   - MODIFY 예약어 사용

---

사용 예시

---

1] HR 계정의 사원테이블(EMPLOYEES)에 사원명(EMP_NAME)칼럼을 추가하시오

    데이터 타입은 VARCHAR2 (60)

ALTER TABLE HR.EMPLOYEES ADD(EMP_NAME VARCHAR2(60));

 

+ 이후 배우는 것

UPDATE HR.EMPLOYEES
	SET EMP_NAME = FIRST_NAME||' '||LAST_NAME;
	--UPDATE SET

SELECT FIRST_NAME, LAST_NAME, EMP_NAME
	FROM HR.EMPLOYEES;
	-- 결과도출
    
COMMIT;

2] HR계정의 EMP_NAME칼럼의 데이터 타입을 CHAR(60)으로 변경하시오.

ALTER TABLE HR.EMPLOYEES MODIFY(EMP_NAME CHAR(60));

CHAR(n) 나머지 값은 빈공간으로 출력

(가변길이와 고정길이로 인해 점유된 공백도 유효한 데이터로 취급되어 업데이트문을 통해 제거해야함)

 

2-1] HR계정의 EMP_NAME컬럼의 데이터 타입을 VARCHAR2(60)으로 변경하시오.

ALTER TABLE HR.EMPLOYEES MODIFY(EMP_NAME VARCHAR(60));
        
UPDATE HR.EMPLOYEES
SET EMP_NAME = TRIM(EMP_NAME);
--TRIM 빈공간 삭제

4) 테이블 컬럼 삭제

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

   - 존재하는 컬럼 삭제 기능

---

사용 예시

---

   - 주문테이블(ORDERS)의 주문금액 컬럼 (ORDER_AMT)을 삭제하시오.

ALTER TABLE ORDERS DROP COLUMN ORDER_AMT;

5) 테이블 컬럼명 변경

ALTER TABLE 테이블명 RENAME COLUMN old_column_name TO new_column_name;

   - 존재하는 컬럼 이름 변경

---

사용 예시

---

   - 상품테이블(PRODUCT)의 GID 컬럼을 PID로 변경하시오.

ALTER TABLE PRODUCT RENAME COLUMN GID TO PID;

COMMIT;