[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;