Oracle, 테이블 생성 / 삽입, 삭제, 수정 / 복사 / 시퀀스

테이블 생성

 

CREATE TABLE 테이블명(
	컬럼명1 컬럼타입 [제약조건],
	컬럼명2 컬럼타입 [제약조건],
	...
);

 

조건

 

문자로 시작해야 한다. (30자 이내) - 영문 대소문자, 숫자, 특수문자 ( _, $, # ), 한글 가능

중복되는 이름은 사용하면 안된다.

예약어(CREATE, TABLE, COLUMN 등)은 사용할 수 없다.

 

자료형

자료형	특징
NUMBER	number(전체 자리, 소수 이하)의 형태 숫자형(가변형)
INT	정수형 숫자(고정형)
VARCHAR / VARCHAR2	문자, 문자열(가변형) -> 최대 4000byte
CHAR	문자, 문자열(고정형) -> 2000byte
DATE	날짜형
CLOB	문자열 -> 최대 4GB
BLOB	바이너리형(그림, 음악, 동영상...) 최대 4GB

 

제약조건

제약조건	특징
NOT NULL	해당 컬럼에 NULL을 포함하지 않도록 한다.	컬럼
Unique	해당 컬럼 또는 컬럼 조합 값이 유일하도록 한다.	컬럼, 테이블
Primary key	각 행을 유일하게 식별할 수 있도록 한다. Unique + not null	컬럼, 테이블
References Table(Column)	해당 컬럼이 참조하고 있는 (부모)테이블의 특정 (컬럼, 테이블) 컬럼 값들과 일치하거나 또는 NULL이 되도록 보장한다.	컬럼, 테이블
Check	해당 컬럼에 특정 조건을 항상 만족시키도록 한다.	컬럼, 테이블

 

예시

CREATE TABLE USER(
	IDX NUMBER CONSTRAINT PKIDX PRIMARY KEY,
	ID VARCHAR2(10) CONSTRAINT UNID UNIQUE
);

 

CONSTRAINT : 제약조건에 이름(별칭)을 부여

 

제약조건 확인(Table : user_constraints)

컬럼명	제약조건
constraint_name	이름
constraint_type	유형 P : primary key U : unique R : reference C : checkm not null
search_condition	check 조건 내용
r_constraint_name	참조 테이블의 primary key 이름
delete_rule	참조 테이블의 primary key 컬럼이 삭제될 때 적용되는 규칙

 

SELECT CONSTRAINT_NAME 
FROM USER_CONSTRAINTS 
WHERE TABLE_NAME = '테이블이름';

 

삭제 / 수정 RULE

삭제 / 수정 RULE	특징
on delete casecade	대상 데이터를 삭제하면, 해당 데이터를 참조하는 데이터도 삭제한다
on delete set null	대상 데이터를 삭제하면, 해당 데이터를 참조하는 데이터는 NULL로 바꾼다
on delete restricted	삭제 대상 데이터를 참조하는 데이터가 존재하면 삭제할 수 없다. (기본값)
on update casecade	대상 데이터를 수정하면, 해당 데이터를 참조하는 데이터도 수정된다.

 

예시

CREATE TABLE DEPT(
	DEPTNO NUMBER CONSTRAINT DNO PRIMARY KEY,
	DNAME VARCHAR2(30) CONSTRAINT DNAME NOT NULL
);

CREATE TABLE EMP(
	EMPNO NUMBER CONSTRAINT ENO PRIMARY KEY,
	DEPTNO NUMBER,
	CONSTRAINT FKNO FOREIGN KEY(DEPTNO) REFERENCES DEPT ON DELETE SET NULL
);

 

문구를 해석해면

 

CONSTRAINT FKNO FOREIGN KEY(DEPTNO) 

DEPTNO를 외래키로 하고 제약조건명은 FKNO로 지정한다.

 

REFERENCES DEPT 

DEPT 테이블의 컬럼 값과 일치하거나 또는 NULL이 되어야 한다.

 

ON DELETE SET NULL

대상 데이터를 삭제하면 참조하는 데이터는 NULL로 바꾼다.

 

---

테이블 수정

 

ALTER TABLE 데이블명
	ADD 컬럼명 데이터타입 [제약조건]
	ADD constraint 제약조건명 제약조건타입(컬럼명)
    
	MODIFY 컬럼명 데이터타입
    
	DROP column 컬럼명 [casecade constraints]
	DROP primary key [casecade] | union (컬럼명...) [casecade] ... | constraint 제약조건명 [casecade]

 

이름 변경

alter table 기존테이블명 rename to 새테이블명

alter table 테이블명 rename column 기존컬럼명 to 새컬럼명

alter table 테이블명 rename constraint 기존제약조건명 to 새제약조건명

---

테이블 복사

 

서브쿼리를 이용해서 복사한 경우 not null을 제외한 제약조건은 복사 안된다.

(not null 제약조건도 sys_*****로 복사됨)

 

CREATE TABLE 테이블명([컬럼명1, 컬럼명2 ...]) AS 서브쿼리

 

구조만 복사

CREATE TABLE 테이블명1 AS SELECT * FROM 테이블명2 WHERE 1=0

 

---

시퀀스

 

순차적으로 정수값을 자동으로 생성하는 객체

 

CREATE SEQUENCE 시퀀스명
[INCREMENT BY 증가값] [START WITH 시작값]
[MAXVALUE 최대값 | MINVALUE 최소값]
[CYCLE | NO CYCLE]
[CACHE | NOCACHE]

 

Increment by 증가값 : 증가/감소 간격(기본값 : 1)

start with : 시작번호(기본값 : 1)

maxvalue / minvalue : 시퀀스의 최대/최소값 지정

cycle / nocycle : 최대/최소값에 도달 시 반복 부여 결정

cache / nocache : 지정한 수량만큼 메모리 생성 여부 결정( 최소값 : 2, 기본값 : 20 )

 

예시

CREATE SEQUENCE NO_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE;

 

이 문구의 의미는

 

CREATE SEQUENCE NO_SEQ

NO_SEQ라는 시퀀스를 생성하겠다.

 

INCREMENT BY 1

증가 간격은 1로 하고

 

START WITH 1

시작번호는 1

 

NOCYCLE

최대/최소값 도달시 반복하지 않고

 

NOCACHE

지정한 수량만큼 메모리 생성하지 않겠다..

---

TRUNCATE

 

테이블의 데이터를 전부 삭제하고 사용하고 있던 공간을 반납한다.

 

해당 테이블의 데이터가 모두 삭제되지만 테이블 자체가 지워지는 것은 아니다.

해당 테이블에 생성되어 있던 인덱스도 함께 TRUNCATE 된다.

 

TRUNCATE TABLE 테이블명;

 

DELETE 후에는 데이터만 지워지고 쓰고 있던 디스크상의 공간은 그대로 가지고 있다.

 

TRUNCATE 작업은 최초 테이블이 만들어졌던 상태, 즉 데이터가 1건도 없는 상태로 모든 데이터 삭제, 칼럼 값만 남아있다.

그리고 용량도 줄고 인덱스 등도 모두 삭제된다.