[SQLD : Ⅲ. SQL 기본] 2. DDL

* CREATE TABLE

 한 번 생성된 테이블은 특별히 사용자가 구조를 변경하기 전까지 생성 당시 구조를 유지

 1) ADD COLUMN

  - 기존 테이블에 필요한 컬럼을 추가하는 명령

ALTER TABLE 테이블명 ADD 추가할 칼럼명 데이터 유형;

[예제] PLAYER 테이블에 ADDRESS(데이터 유형은 가변 문자로 자릿수 80자리로 설정한다.) 칼럼을 추가한다

[예제] Oracle ALTER TABLE PLAYER ADD (ADDRESS VARCHAR2(80)); 테이블이 변경되었다.
[예제] SQL Server ALTER TABLE PLAYER ADD ADDRESS VARCHAR(80); 명령이 완료되었다.

[실행 결과] Oracle DESC PLAYER;

칼럼 NULL 가능 데이터 유형

PLAYER_ID NOT NULL CHAR(7)

PLAYER_NAME NOT NULL VARCHAR2(20)

TEAM_ID NOT NULL CHAR(3)

E_PLAYER_NAME VARCHAR2(40)

NICKNAME VARCHAR2(30)

JOIN_YYYY CHAR(4)

POSITION VARCHAR2(10)

BACK_NO NUMBER(2)

NATION VARCHAR2(20)

BIRTH_DATE DATE

SOLAR CHAR(1)

HEIGHT NUMBER(3)

WEIGHT NUMBER(3)

ADDRESS VARCHAR2(80) ☜ 추가된 열

 

 2) DROP COLUMN

 - 테이블에서 필요 없는 컬럼 삭제

ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

[예제] 앞에서 PLAYER 테이블에 새롭게 추가한 ADDRESS 칼럼을 삭제한다.

[예제] Oracle ALTER TABLE PLAYER DROP COLUMN ADDRESS; 테이블이 변경되었다.
[예제] SQL Server ALTER TABLE PLAYER DROP COLUMN ADDRESS; 명령이 완료되었다.

[실행 결과] SQL Server exec sp_help 'dbo.PLAYER' go

칼럼이름 데이터 유형 길이 NULL 가능

PLAYER_ID CHAR(7) 7 NO

PLAYER_NAME VARCHAR(20) 20 NO

TEAM_ID CHAR(3) 3 NO

E_PLAYER_NAME VARCHAR(40) 40 YES

NICKNAME VARCHAR(30) 30 YES

JOIN_YYYY CHAR(4) 4 YES

POSITION VARCHAR(10) 10 YES

BACK_NO TINYINT 1 YES

NATION VARCHAR(20) 20 YES

BIRTH_DATE DATE 3 YES

SOLAR CHAR(1) 1 YES

HEIGHT SMALLINT 2 YES

WEIGHT SMALLINT 2 YES

 

 3) MODIFY COLUMN

  - 테이블에 존재하는 컬럼에 대해 ALTER TABLE 명령을 이용하여 데이터 유형, 디폴트 값, NOT NULL 제약조건 변경

  - 해당 컬럼의 크기는 늘릴 수 있지만 줄이지는 못함, 데이터 훼손 우려, NULL이거나 아무 행도 없으면 줄일 수 있음

[Oracle] ALTER TABLE 테이블명 MODIFY (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 …);
[SQL Server] ALTER TABLE 테이블명 ALTER (칼럼명1 데이터 유형 [DEFAULT 식] [NOT NULL], 칼럼명2 데이터 유형 …);

[예제] TEAM 테이블의 ORIG_YYYY 칼럼의 데이터 유형을 CHAR(4)→VARCHAR2(8)으로 변경하고, 향후 입력되는 데이터의 DEFAULT 값으로 '20020129'을 적용하고, 모든 행의 ORIG_YYYY 칼럼에 NULL이 없으므로 제약조건을 NULL → NOT NULL로 변경한다.

[예제] Oracle ALTER TABLE TEAM_TEMP MODIFY (ORIG_YYYY VARCHAR2(8) DEFAULT '20020129' NOT NULL); 테이블이 변경되었다.
[예제] SQL Server ALTER TABLE TEAM_TEMP ALTER COLUMN ORIG_YYYY VARCAHR(8) NOT NULL; 명령이 완료되었다. 
                  ALTER TABLE TEAM_TEMP ADD CONSTRAINT DF_ORIG_YYYY DEFAULT '20020129' FOR ORIG_YYYY; 명령이 완료되었다.

[실행 결과] Oracle DESC TEAM_TEMP;

칼럼 NULL 가능 데이터 유형

TEAM_ID NOT NULL CHAR(3)

REGION_NAME NOT NULL VARCHAR2(4)

TEAM_NAME NOT NULL VARCHAR2(40)

E_TEAM_NAME VARCHAR2(50)

ORIG_YYYY NOT NULL VARCHAR2(8) ☜ 기본값'20020129'

STADIUM_ID NOT NULL CHAR(3)

ZIP_CODE1 CHAR(3)

ZIP_CODE2 CHAR(3)

ADDRESS VARCHAR2(80)

DDD VARCHAR2(3)

TEL VARCHAR2(10)

FAX VARCHAR2(10)

HOMEPAGE VARCHAR2(50)

OWNER VARCHAR2(10)

 

  - 컬럼명을 불가피하게 변경해야하는 경우 RENAME COLUMN 문구 사용

  - 오라클 일부 DBMS에서만 지원하는 기능

ALTER TABLE 테이블명 RENAME COLUMN 변경해야 할 칼럼명 TO 새로운 칼럼명;

ALTER TABLE PLAYER RENAME COLUMN PLAYER_ID TO TEMP_ID; 테이블이 변경되었다. 
ALTER TABLE PLAYER RENAME COLUMN TEMP_ID TO PLAYER_ID; 테이블이 변경되었다.

  - SQL 서버에서는 sp_rename 저장 프로시저로 컬럼 이름 변경 가능

    엔티티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있음

sp_rename 변경해야 할 칼럼명, 새로운 칼럼명, 'COLUMN';
sp_rename 'dbo.TEAM_TEMP.TEAM_ID', 'TEAM_TEMP_ID', 'COLUMN';

  

 4) DROP CONSTRAINT

  - 테이블 생성 시 부여했던 제약조건을 삭제하는 명령어 형태

ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;

[예제] PLAYER 테이블의 외래키 제약조건을 삭제한다.

[예제] Oracle ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK; 테이블이 변경되었다.
[예제] SQL Server ALTER TABLE PLAYER DROP CONSTRAINT PLAYER_FK; 명령이 완료되었다.

 

 5) ADD CONSTRAINT

  - 테이블 생성 시 제약조건을 적용하지 않으면 생성 후 필요에 의해 제약조건 추가 가능

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건 (칼럼명);

[예제] PLAYER 테이블에 TEAM 테이블과의 외래키 제약조건을 추가한다. 제약조건명은 PLAYER_FK로 하고, PLAYER 테이블의 TEAM_ID 칼럼이 TEAM 테이블의 TEAM_ID를 참조하는 조건이다.

[예제] Oracle ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID); 테이블이 변경되었다.
[예제] SQL Server ALTER TABLE PLAYER ADD CONSTRAINT PLAYER_FK FOREIGN KEY (TEAM_ID) REFERENCES TEAM(TEAM_ID); 명령이 완료되었다.

[예제] PLAYER 테이블이 참조하는 TEAM 테이블을 제거해본다.

[예제] Oracle DROP TABLE TEAM; ERROR: 
외래 키에 의해 참조되는 고유/기본 키가 테이블에 있다. ※ 테이블은 삭제되지 않음
[예제] SQL Server DROP TABLE TEAM; ERROR: 
엔터티 'TEAM'은 FOREIGN KEY 제약 조건을 참조하므로 삭제할 수 없다. ※ 테이블은 삭제되지 않음

[예제] PLAYER 테이블이 참조하는 TEAM 테이블의 데이터를 삭제해본다.

[예제] Oracle DELETE TEAM WHERE TEAM_ID = 'K10'; ERROR: 
무결성 제약조건(SCOTT.PLAYER_FK)이 위배되었다. 자식 레코드가 발견되었다. ※ 데이터는 삭제되지 않음
[예제] SQL Server DELETE TEAM WHERE TEAM_ID = 'K10'; ERROR: 
FOREIGN KEY 제약 조건을 참조하므로 삭제할 수 없다. ※ 데이터는 삭제되지 않음

 

* RENAME TABLE

RENAME 변경전 테이블명 TO 변경후 테이블명;

 - SQL 서버에서는 sp_rename을 이용해서 테이블 이름 변경

sp_rename 변경전 테이블명, 변경후 테이블명;

[예제] RENAME 문장을 이용하여 TEAM 테이블명을 다른 이름으로 변경하고, 다시 TEAM 테이블로 변경한다.

[예제] Oracle RENAME TEAM TO TEAM_BACKUP; 테이블 이름이 변경되었다. 
              RENAME TEAM_BACKUP TO TEAM; 테이블 이름이 변경되었다.
[예제] SQL Server sp_rename 'dbo.TEAM','TEAM_BACKUP'; 
주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다. 
                  sp_rename 'dbo.TEAM_BACKUP','TEAM'; 
주의: 엔터티 이름 부분을 변경하면 스크립트 및 저장 프로시저를 손상시킬 수 있다.

 

* DROP TABLE

DROP TABLE 테이블명 [CASCADE CONSTRAINT];

 - CASCADE CONSTRAINT 옵션은 해당 테이블과 관계가 있었던 참조되는 제약조건에 대해서도 삭제한다는 의미

 - SQL 서버에서는 CASCADE옵션이 존재하지 않음, FK 제약조건 또는 참조 테이블 먼저 삭제해야 함

[예제] PLAYER 테이블을 제거한다.

[예제] Oracle DROP TABLE PLAYER; 테이블이 삭제되었다. 
              DESC PLAYER; ERROR: 설명할 객체를 찾을 수 없다.
[예제] SQL Server DROP TABLE PLAYER; 명령이 완료되었다. 
exec sp_help 'dbo.PLAYER'; 
메시지 15009, 수준 16, 상태 1, 프로시저 sp_help, 
줄 66 데이터베이스 ‘northwind'에 엔터티 'dbo.player'이(가) 없거나 이 작업에 적합하지 않다.

 

* TRUNCATAE TABLE

 - 테이블 자체 삭제가 아닌 테이블 내의 모든 행 제거, 공간 재사용 가능하게 함

TRUNCATE TABLE PLAYER;

[예제] DROP TABLE을 사용하여 해당 테이블을 제거하고 테이블 구조를 확인한다.

[예제] Oracle DROP TABLE TEAM; 테이블이 삭제되었다. 
                    DESC TEAM; ERROR: 설명할 객체를 찾을 수 없다.
[예제] SQL Server DROP TABLE TEAM; 명령이 완료되었다. 
exec sp_help 'dbo.TEAM'; 메시지 15009, 수준 16, 상태 1, 프로시저 sp_help, 
줄 66 데이터베이스 'northwind'에 엔터티 'dbo.TEAM'이(가) 없거나 이 작업에 적합하지 않다.

 - DROP TABLE은 테이블 자체가 없어져서 테이블 구조를 확인할 수 없음

 - TRUNCATE TABLE은 테이블 구조는 유지하고 데이터만 삭제하는 기능

 - DML로 분류할 수 있지만 내부처리방식이나, Auto commit 특성으로 DDL로 분류

 - DELETE 와 TRUNCATE는 처리 방식이 다름

 - 테이블 전체 데이터 삭제, 시스템 활용 측면에서는 부하가 적은 TRUNCATE TABLE을 권고

    TRUNCATE TABLE은 정상적인 복구가 불가능하므로 주의