[SQLD] 2-19. SQL2 (DDL, CTAS, PURGE, 제약 조건)

[홍쌤의 데이터랩, SQLD n과목 완벽 정리] 영상를 개인이 정리하여 [원작자의 허가 이후] 올린 글이며,

[개인이 추가로 정리한 내용]이 포함되어 있습니다.

 

 

 

 

홍쌤의 데이터랩

[개정판] SQLD n과목 완벽 정리

https://www.youtube.com/watch?v=rdfHFnqVoRw

 

 

---

 

 

목차

 

• DDL (Data Definition Language)
  • 1. CREATE
    • CTAS (Create Table As Select)
  • 2. ALTER
    • 1. 컬럼 추가
    • 2. 컬럼 변경
    • 2-1. 컬럼 사이즈 변경
    • 2-2. 컬럼 타입 변경
    • 2-3. 컬럼 이름 변경
    • 3. 컬럼 삭제
  • 3. DROP
    • PURGE
  • 4. TRUNCATE
• DELETE/DROP/TRUNCATE 요약
• 제약조건
  • 제약조건의 종류
    • 1. PRIMARY KEY
    • 2. UNIQUE
    • 3. NOT NULL
    • 4. FOREIGN KEY
    • 5. CHECK

DDL (Data Definition Language)

- AUTO COMMIT 특성을 가지며, 이에 따라 TRUNCATE가 DDL로 분류 됨에 주의

 

1. CREATE

- 테이블, 인덱스 등과 같은 객체 생성

 

CTAS (Create Table As Select)

- 소스(원본) 테이블을 기반으로 새 테이블을 만드는 방식

- 공식적인 패턴이나 방법론이라기 보다는, 현업에 자주 사용되는 일종의 트릭

- 사용되는 SQL구문의 앞자리를 따 CTAS라 부름

 

- 소스테이블의 컬럼명, 컬럼타입이 복제됨

- 인스턴스 및 NULL속성도 복제됨

- SELECT절에서 컬럼에 별칭 사용 시 => 별칭으로 컬럼 생성

- CREATE절에서 컬럼명 변경 가능

- 소스테이블의 제약조건, INDEX 등은 복제되지 않음

- 항상 false인 조건식을 지정하면 인스턴스를 제외하고 복제 가능

 

e.g.

 

1) 테이블 형식 및 인스턴스 복제

CREATE TABLE 새테이블

AS

SELECT *

FROM 소스테이블;

 

2) 테이블 형식 복제

CREATE TABLE 새테이블

AS

SELECT *

FROM 소스테이블

WHERE 1=2; -- 항상 false인 조건식

 

3-1) 컬럼명 변경하여 복제 - CREATE절에 기입

CREATE TABLE 새테이블(nc1,nc2)

AS

SELECT c1, c2

FROM 소스테이블;

 

3-2) 컬럼명 변경하여 복제 - SELECT절에 기입 + Alias 사용

CREATE TABLE 새테이블

AS

SELECT c1 [as] nc1, c2 [as] nc2

FROM 소스테이블;

 

2. ALTER

- 테이블 구조 변경(컬럼명, 타입, 사이즈, 기본 값, 컬럼 삭제, 컬럼 추가, 제약조건)

- 컬럼순서는 변경 불가(재생성 해야 함)

 

e.g.

ALTER TABLE ~ MODIFY ~;

ALTER TABLE ~ ADD ~;

ALTER TABLE ~ RENAME COLUMN ~ TO ~;

 

1. 컬럼 추가

- 새로 추가된 컬럼 위치는 항상 마지막 (중간 삽입 불가능)

- 데이터 타입 필수, 기본 값 및 제약조건 선택

- 여러 컬럼 동시 추가 가능 => 괄호 사용 필수

- 기본 값 할당하지 않을 시, NOT NULL 제약조건 할당 불가

=> 기본값 없이 새 컬럼 생성 시 NULL이 할당되므로

 

2. 컬럼 변경

- 컬럼 사이즈, 데이터타입, 기본 등

 

문법

ALTER TABLE 테이블명 MODIFY 컬럼명 데이터타입 [DEFAULT 기본값] [제약조건]

 

2-1. 컬럼 사이즈 변경

SIZE 증가 -> 항상 가능

SIZE 감소 -> 할당하려는 SIZE보다 큰 인스턴스(컬럼 데이터)가 없어야 함

- 동시 변경 가능

 

2-2. 컬럼 타입 변경

- 빈 컬럼일 경우만 데이터 타입 변경 가능

- 데이터가 있는 경우 => 호환되는 타입간의 변경 가능 (자료의 특성에 따라 단방향/양방향 호환 가능)

- 동시 변경 가능

 

2-3. 컬럼 이름 변경

- 항상 가능

- 동시 변경 불가

 

문법

ALTER TABLE 테이블 RENAME COLUMN 기존컬럼명 TO 새컬럼명

 

테이블 구조 확인

DESC 테이블명

- DESC(Description)임에 주의 => 내림차순 DESC(Descending)이랑 약어가 같음

 

3. 컬럼 삭제

- 데이터 존재 여부와 상관없이 가능

-  RECYCLEBIN에 남지 않으므로 FLASHBACK 불가

=> RECYLEBIN은 객체(테이블, 인덱스 등) 단위의 보존을 목표로 하므로 컬럼은 적용 안됨

=> 객체 단위는 RECYLEBIN에 남으며, PREGE 옵션으로 즉시 DROP 가능

- 동시 삭제 불가능

 

3. DROP

- 객체 삭제

- DROP 이후 조회 불가

 

PURGE

- 객체 DROP에 적용하는 옵션

- 이후 RECYCLEBIN에서 조회 불가능

 

e.g.

DROP TABLE 테이블명 [PURGE];

 

4. TRUNCATE

- 테이블 구조를 유지한 채 모든 데이터 삭제, AUTO COMMIT

- 이후 RECYCLEBIN에서 조회 불가능

 

e.g.

TRUNCATE TABLE 테이블명;

 

DELETE/DROP/TRUNCATE 요약

 

DELETE

- 테이블 메타데이터 (= 구조) 유지

- 데이터 일부/전체 삭제

- 트랜잭션 포함, 롤백 가능 (DML)

- 상대적으로 느림 (트랜잭션 로그 별도 기록)

 

TRUNCATE

- 테이블 메타데이터 유지

- 데이터 전체 삭제

- 트랜잭션 미포함, 롤백 불가능 (DDL & Auto Commit)

- 상대적으로 빠름

 

DROP

- 테이블 메타데이터 삭제

- 데이터 전체 삭제

- 트랜잭션 미포함, 롤백 불가능 (DDL  & Auto Commit)

- 상대적으로 빠름

 

DELETE, DROP, TRUNCATE시 복구

https://blog.naver.com/eatgs/221838799716

 

제약조건

- 데이터 무결성을 위해 각 컬럼에 적용하는 데이터 제약 장치

- 유지보수를 위해 제약조건명 사용자 정의 권장, 지정하지 않으면 RDMBS가 임의 부여

 

e.g.

1) 테이블 생성 시 - 컬럼별 제약조건 부여

CREATE TABLE 테이블명(

컬럼명 데이터타입 [DEFAULT 기본값] [CONSTRAINT 제약조건명] 제약조건 종류,

...

);

 

2) 테이블 생성 시 - 다중 컬럼 제약조건 부여

CREATE TABLE 테이블명(

컬럼명1 데이터타입1 [DEFAULT 기본값],

컬럼명2 데이터타입2 [DEFAULT 기본값],

...

[CONSTRAINT 제약조건명] 제약조건 종류(컬럼명1, 컬럼명2, ...)

);

 

제약조건의 종류

1. PRIMARY KEY

- UNIQUE + NOT NULL

- UNIQUE INDEX 생성 => UNIQUE 특성이므로

- 복합키로 구성 가능 (다중 컬럼으로 구성 가능)

 

2. UNIQUE

- 중복 불허용, NULL 허용

- UNIQUE INDEX 생성

 

3. NOT NULL

- 다른 제약 조건 다소 상이

=> CTAS로 복제 시 제약 조건 복사 됨

=> 이미 존재하는 컬럼에 추가시 ADD가 아닌 MODIFY 사용

 

4. FOREIGN KEY

- 참조 테이블의 참조 컬럼(Reference Key)가 사전에 PK나 UNIQUE인 상태여야 함

 

1) 테이블 생성 시 참조키 지정

CREATE TABLE 테이블명(

컬럼명1 데이터타입1 [DEFAULT 기본값] REFERENCES 참조테이블(참조키),

...

);

 

2) 정의된 컬럼에 참조키 지정

*상호 참조관계 등도 존재하기에 항상 부모-자식 관계는 아니지만, 이해를 위해 사용

(참조/피참조 테이블, 연관/대상 테이블 등으로 치환 구분)

 

ALTER TABLE 자식테이블 ADD [CONSTRAINT 제약조건명] FOREIGN KEY(자식테이블 KEY)

REFERENCES  부모테이블(부모테이블 KEY)

 

참조키 옵션

- 생성 시 정의해야 하며 이후 변경 불가 (필요 시 제약조건 재생성)

 

1. ON [DELETE/UPDATE] CASCADE : 부모 데이터 [삭제/수정] 시 자식 데이터 함께 [삭제/수정]

2. ON [DELETE/UPDATE] SET NULL : 부모 데이터 [삭제/수정]  시 자식 데이터 NULL 지정

 

5. CHECK

- 사용자 정의 데이터 제약조건 부여

 

e.g.

CREATE TABLE 테이블명(

컬럼명1 데이터타입1 [DEFAULT 기본값],

컬럼명2 데이터타입2 [DEFAULT 기본값],

...

[CONSTRAINT 제약조건명] CHECK (컬럼명1 >= 컬럼명2)

-- [CONSTRAINT 제약조건명] CHECK (컬럼명1 IN(1,2,3))

);