SQL DDL 데이터 정의어 총 정리

DDL은 DB구조를 생성하거나 수정 및 삭제하는 SQL문에 해당합니다

SQL종류	명령문
Data Definition Language (데이터 정의어)	CREATE
	ALTER
	DROP
	RENAME
	TRUNCATE

 

*오라클의 데이터타입

DATA TYPE	특징
CHAR(SIZE)	고정길이 문자저장 최소 1BYTE, 최대  2000BYTE
VARCHAR2(SIZE)	가변길이 문자저장 최소 1BYTE, 최대  4000BYTE
NVARCHAR(SIZE)	국가별 국가 집합에 따른 문자 OR 바이트의 가변길이 문자 최소 1BYTE, 최대  4000BYTE
NUMBER(P,S)	가변길이 숫자 저장 방식, P(전체자리수) S(소수점자리수)
DATE	날짜 및 시간
ROWID	테이블 내 행의 고유 주소를 가지는 64비트 문자
BLOB	대용량 이진데이터 저장 (최대 4GB)
CLOB	대용량 텍스트데이터 저장 (최대 4GB)
BFILE	대용량 이진데이터를 파일 형태 저장 (최대 4GB)

 

1. CREATE

 

CREATE TABLE 테이블명
(컬럼명 데이터타입)

CREATE TABLE scott.employee
( no NUMBER(4),
 name VARCHAR2(20),
 hiredate DATE,
 sal NUMBER(7,2));

 

하지만, 여기서 데이터 중복이 가능하기 때문에 데이터 무결성이 보장되지 않기 때문에

뒤에 제약조건 TYPE은 5가지가 있습니다.

 

• 추가 > 기본키(Primary Key)
• 추가 > Unique
• 변경 > Not Null
• 추가 > Check
• 추가 > 외래키/참조키 (Foreign Key)

 

제약조건을 추가하는 방식 또한 제약조건을 추가하는 위치에 따라서 2가지로 나눌 수 있는데,

아래와 같이 컬럼을 선언하면서 제약조건을 선언하는 법 (컬럼레벨)

 

CREATE TABLE 테이블명
(컬럼명 데이터타입 + [DEFAULT값 | 제약조건])

 

2번째는 테이블을 모두 선언하고 나서, 제약조건을 추가하는 방법(테이블레벨)입니다

(여기서 not null은컬럼을 변경하는 개념이기 때문에 테이블 레벨에 사용될 수 없음, 다른 제약조건은 모두 컬럼/테이블 레벨 방식 모두 지원함)

CREATE TABLE 테이블명
(컬럼명 데이터타입,
 컬럼명 데이터타입,
 컬럼명 데이터타입,
 [제약조건 추가]);

 

 

---

 

예시 코드) Primary Key 컬럼 레벨 제약 조건 지정

CREATE TABLE department
( dept NUMBER(2) CONSTRAINT deptno_pk PRIMARY KEY,
 name VARCHAR2(15),
 loc VARCHAR2(15) );

CONSTRAINT + 제약조건 이름(임의지정 가능,생락시 자동으로 실수형이름이 부여) + 제약조건 타입(PRIMARY KEY)

 

**테이블에 어떤 제약조건이 있는지 조회하는 방법 (USER CONSTRAINTS)사용

 

SELECT *

FROM USER_CONSTRAINTS

WHERE table_name = '테이블명';

 

#조회 시 나오는 constraint type

P : Primary Key

F: Foreign Key

U : Unique

C: Not Null, Check

 

**어떤 컬럼에 어떤 제약조건이 있는지 조회하는 방법 (USER CONS_COLUMNS)사용

 

SELECT *

FROM USER_CONS_COLUMNS

WHERE table_name = 'DEPARTMENT'

 

예시 코드) Primary Key 테이블 레벨 제약 조건 지정

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, 컬럼명 데이터타입,                #CONSTRAINT전에 콤마 주의 할것
[CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명[,컬럼명2)     #복합컬럼으로 컬럼 2개 기입가능
);


#복합컬럼은 2개의 컬럼이 묶여서 하나의 PRIMARY KEY로 사용되는 경우

• 여기서 복합컬럼은 테이블레벨에만 사용 가능함

 

CREATE TABLE det2
( dep NUMBER(2), 
 dna VARCHAR2(15),
 loc VARCHAR2(15) ,
 CONSTRAINT dt2_depo_pk PRIMARY KEY(dep) #괄호에 컬럼명이 들어감
);

 

예시 코드) Primary Key 와 Unique의 컬럼레벨 적용

CREATE TABLE depart2
( deo NUMBER(2) CONSTRAINT depart2_deptno_pk PRIMARY KEY,
 dna VARCHAR2(15) CONSTRAINT depart2_dname_uk UNIQUE,
 loc VARCHAR2(15) );

 

예시 코드) Primary Key 와 Unique의 테이블 레벨 적용

CREATE TABLE depar1
( dep NUMBER(2) CONSTRAINT depart1_dep_pk PRIMARY KEY,
 dname VARCHAR2(15), 
 loc VARCHAR2(15),
 CONSTRAINT depart1_dname_uk UNIQUE(dname)
);

 

예시 코드) Check의 컬럼 레벨 적용

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건식) , #조건식이 들어가는게 차이점
컬럼명 데이터타입);

check 내부의 조건식은 where 조건문과 유사함

 

CREATE TABLE depart33
( deptno NUMBER(2) ,
 dname VARCHAR2(15)
 CONSTRAINT depart33_dname_ck CHECK( dname IN('sales','scm')) ,
 loc VARCHAR2(15) 
);

 

예시 코드) Check의 테이블 레벨 적용 (테이블 레벨 적용 시 constraint앞의 콤마 주의 필요)

CREATE TABLE depar8
( deo NUMBER(2) ,
 dnae VARCHAR2(15),
 loc VARCHAR2(15),
CONSTRAINT depar8_dnae_ck CHECK( dnae IN('hr','wood')) 
);

 

예시 코드) Foreign Key 컬럼 레벨 적용 방법 -> References + 부모테이블명

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] REFERENCES 부모테이블명(컬럼명))
#어떤 부모테이블을 reference한다는 방식으로 표현

CREATE TABLE emp02
( empno NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 deptno NUMBER(2) CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno)
);

 

예시 코드) Foreign Key 테이블 레벨 적용 방법 

CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, 컬럼명 데이터타입,
[CONSTRAINT 제약조건명] FOREIGN KEY(컬럼명)     #누가 Foreign Key인지 알려주고
 REFERENCES 부모테이블명(컬럼명)                #누가 부모테이블인지 보여줘야 함
);

 

**Foreign Key사용시 주의 사항
1. 외래키 테이블이 참조하고 있는 마스터 레코드 삭제가 불가함
--> 이러한 종속적 상황 해결을 위해서는 부모 테이블 행 삭제 시, 참조하는 외래키 테이블도 함께 삭제되는 ON DELETE CASCASDE, 혹은 외래키 테이블이 NULL이 되는 ON DELETE SET NULL사용 가능

 

---

 

2. DROP

DROP TABLE 테이블명 [CASCADE CONSTRAINTS];

==> TABLE에 저장된 모든 데이터 및 관련 인덱스, 외래키 제약 조건을 제외한 모든 제약조건이 삭제

==> CASCADE CONSTRAINTS 를 통해 외래키도 포함한 종속적인 삭제도 진행

 

---

 

3. ALTER (변경: 컬럼 추가, 삭제, 타입 변경, 길이 변경)

 

ALTER TABLE - ADD문 : 새로운 칼럼 추가 (한번에 여러 컬럼 추가 가능)

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

 

 

ALTER TABLE - MODIFY문 : 기존 칼럼 변경

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

예시) 기존 컬럼의 데이터 크기 변경

ALTER TABLE Sales
MODIFY ( PRODUCT VARCHAR2(40) );

예시) 기존 컬럼의 NOT NULL 제약 조건 추가 (NOT NULL은 변경하는 문법이기 때문에 MODIFY사용!!!!-컬럼레벨과 유사)

ALTER TABLE 테이블명
MODIFY ( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL );

 

 

ALTER TABLE - DROP문 : 기존 칼럼 삭제

ALTER TABLE 테이블명
DROP ( 컬럼명 [,컬럼명] )

 

 

ALTER TABLE - ADD [CONSTRAINT ] : 기존 칼럼 삭제

ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건타입(컬럼명);

ALTER TABLE dep
ADD CONSTRAINT dep_no_pk PRIMARY KEY(no)

(이 외에도 NOT NULL을 제외한 제약조건은 위와 같이 테이블 레벨에서 적용하면 됨)

 

 

ALTER TABLE - DROP - CONSTRAINT문 : 기존 테이블의 제약조건 삭제

ALTER TABLE 테이블명
DROP PRIMARY KEY|UNIQUE(컬럼)|
 CONSTRAINT 제약조건명 [CASCADE];
 
 #CASCADE는 부모 테이블 제약조건을 없애면서 외래키의 제약조건도 함께 삭제하기 위해 사용

(USER_CONSTRAINTS 조회를 통해서 제약조건명을 알아낼 수 있음)