[복습] SQL | Oracle SQL의 분류(DDL, DML, DCL, TCL, DQL) 중 DDL의 ALTER

DDL 의 ALTER를 공부했다.

 

3) ALTER

alter로는 구조변경을 할 수 있다. 구조변경에는 컬럼명, 컬럼 데이터타입, 컬럼 사이즈, default 값, 컬럼삭제, 컬럼추가, 제약조건 등이 있다.

컬럼 순서는 절대 바꿀 수 없다(재생성으로 해결할 것).

 

가. 컬럼추가

새로 추가된 컬럼은 맨 마지막 컬럼으로 배치가 된다(중간에 위치하게 할 수 없음)

컬럼 추가 시 데이터타입 입력은 필수이며, default값, 제약조건은 명시할 수 있다.

여러 컬럼 동시에 추가가 가능하다(반드시 괄호 사용해야 함)

 

문법은

    -- 순서 위반하면 에러 뜸.

 

단일 컬럼 추가시에는 괄호를 생략할 수 있으나 동시 추가시에는 괄호 필수이다.

 

위의 코드를 실행해보면 단일컬럼 생성인 첫 줄과 둘 째줄은 잘 실행되나

여러 컬럼을 동시에 추가할 때에는 네 번째 줄처럼 괄호로 묶어줘야 한다.

 

첫 째줄은 제약조건 not null을 입력한 것이고, 아래는 default 값을 넣은 결과이다.

 

 

예시를 하나 풀어보자.

데이터가 이미 존재하는 테이블의 컬럼 추가는 어떻게 하는가

CREATE TABLE emp_t2 as select ename, sal, deptno from emp where deptno = 10;

위의 CREATE로 생성된 emp_t2에 대하여  not null 속성으로 hiredate를 추가하여라.

 

그냥 alter table emp_t2 add hiredate date not null; 하면 풀리지 않는다.

default를 써야 하는 상황인 것이다.

왜냐하면 테이블이 비어있을 때와 테이블에 데이터가 있을 때의 not null 속성 가능 여부가 달라진다.

테이블에 데이터가 없으면(컬럼만 있으면) 새로운 컬럼을 추가해도 새로운 데이터는 없기 때문에 not null 선언을 해도 괜찮다.

그런데 데이터가 있는데 컬럼을 추가하면 일단 그 데이터 자리는 null로 들어간다. 그래서 hiredate 컬럼을 추가할 때 데이터가 null이 될 수밖에 없다. 그렇기 때문에 not null 선언하는 것 자체가 올바르지 않다.

 

위와 같은 형태로 SQLD 문제로, 다음 중 해당 명령어의 정상 수행이 되는 경우는? 다음 중 에러가 발생하는 케이스는? 이런 문제가 시험에 나온다.

 

즉 정리하자면 이미 데이터가 있는 테이블의 경우 새로운 컬럼 추가 시 이미 있는 데이터 행에 대해 새로 추가된 컬럼의 데이터는 null이 들어간다.

 

ALTER TABLE emp_t2 ADD hiredate date default sysdate not null;

이 경우는 not null 선언이 스탭상 유효하게 되어 정상 수행된다.

 

데이터가 있는 테이블의 컬럼 추가 시 not null 속성을 갖는 컬럼은 추가가 불가능하다(★ 단 Default 선언하면 가능).

 

 

 

나. 컬럼 수정(데이터 타입 변경, 사이즈 변경, 컬럼 이름 변경 등 모든 게 가능)

  나-1. Default 값 수정(부여할 수 있고, 새로 추가할 수 있고 없앨 수도 있고)

  문법은 아래와 같다

 ALTER TABLE table_name MODIFY 컬럼명 default값;

 

데이터가 이미 존재하는 테이블의 Default 속성을 변경해보자.

  step 1) test data 생성

insert into emp_t2 values('KIM', null, null, sysdate);
commit;

 

  step  2) default 값 수정

alter table emp_t2 modify sal default 3000;

 

  step 3) 새로운 값 입력

insert into emp_t2 values('PARK', null, null, sysdate);
commit; 

 

step3에 새로운 값을 넣어도 default 속성이 변경되지 않는다.

여기서 다시 짚어보는★ Default 값은 특정 컬럼의 값이 생략될 경우(입력시 언급되지 않을 경우) null대신 정해진 값으로 대체하기 위해 필요한 값이다. insert시 주의할 점은 default 값이 선언된 컬럼에 null을 직접 입력할 때에는 default 값으로 입력되지 않는다.

default 값의 발현 시점은 이미 데이터가 존재하는 테이블에 default 값 선언 시 기존 데이터는 수정되지 않으며 그 이후 입력된 데이터만 해당된다.

 

이미 테이블에 입력된 데이터가 있고, 테이블 속성 변경은 alter를 한 시점에 적용이 되고, default를 3000 부여하겠다(sal)에 default value가 적용되는 시점이 중요한데, 앞으로 들어오는 데이터에 대해 default value를 적용하라는거지 이미 들어온 것에 대해서는 아니다.

 

insert into emp_t2 values('HONG', default, null, sysdate);
commit; 

 

 

존재하는 default 값을 삭제하려면 어떻게 하면 될까?

예) default 값 삭제

 

  step 1) default 값 부여

 alter table emp_t1 modify job default 'CLECK';

  

  step 2) 테이블 복제

  create table emp_t3 as select * from emp_t1;

 

  step 3) default 값 삭제

alter table emp_t3 modify job default null;

 

  step 4) default 값 확인

  컬럼의 속성을 가진 딕셔너리 뷰(user_recyclebin도 딕셔너리 뷰의 일부임)에서 확인 가능하다.

딕셔너리 뷰는 모든 컬럼의 속성 정보를 확인할 수 있다.

select *
 from user_tab_columns;

select *
  from user_tab_columns
  where table_name = 'EMP_T1'; 보면 흔적이 남아있음.

 select *
  from user_tab_columns
  where table_name = 'EMP_T3';보면 흔적이 남아있음.

 

 

★따라서 CTAS는 제약조건은 복사되지 않는다. default value도 복제되지 않으나 not null은 복제된다.

(바로 위에서 emp_t1테이블에 job default 걸어 두고 copy했으나 emp_t3에 없었음).

 

 

  나-2. 사이즈 변경

문법은

ALTER TABLE table_name MODIFY 컬럼명 데이터타입(사이즈);

데이터 타입 바꾸는 문법이기도 하다.

사이즈 증가는 언제나 가능하다.

사이즈 축소는 데이터 존재 여부에 따라 제한되는데, 데이터가 있는 경우 최대 데이터 사이즈만큼만 줄일 수 있다.

또한 여러 개 동시 변경이 가능하다.

ALTER TABLE table_name MODIFY(컬럼명1 데이터타입(사이즈),  컬럼명2 데이터타입(사이즈), .....);

 

컬럼 사이즈 변경 예시이다.

바로 위에서 언급했듯 유념해야 할 점은 모든 사이즈 변경이 원활하게 이루어지는 것이 아니라는 것이다. 결론적으로 사이즈를 늘리는 것은 충분히 가능하다. 원본을 해치지 않는다는 조건이 중요한 것이다. 원본 데이터가 항상 먼저 고려가 되어야 하기 때문에 원본 데이터보다 작게 사이즈 조정하면 안된다.

 

desc emp_t2;
alter table emp_t2 modify ename varchar2(20);  가능(size up 가능)

 

작은 사이즈로의 변경은 항상 불가능하다? 에 대한 답변은 아니오 이다.

alter table emp_t2 modify DEPTNO NUMBER(1);

데이터가 있는 한 기존의 데이터보다 더 작은 사이즈로의 변경이 불가한 것이다.

(ORA 메세지로 자리수를 축소할 열은 비어있어야 합니다 라고 뜸)

 

현업에서 이런 요청이 들어오게 되면, 예를들어 deptno를 한 자리로 바꿔주세요 라고 한다면 기존 데이터를 먼저 한 자리로 바꿔주고 난 후 사이즈를 변경하거나, 다 null로 한 후에 바꿔야한다.

alter table emp_t1 modify deptno number(1); 

하면 변경되는데 이는 emp_t1이 빈 테이블이어서 가능하기 때문이다.

 

 

  나-3. 데이터타입 변경

컬럼에 데이터가 없는 경우는 언제나 가능하며, 데이터가 있는 경우에는 데이터 타입 변경이 불가하다.

문법

ALTER TABLE table_name MODIFY (컬럼명1 데이터타입(사이즈), 컬럼명2 데이터타입(사이즈), .....);

 

예) 데이터타입을 변경하여보자.

desc emp_t1;
alter table emp_t1 modify empno varchar2(4);

빈 테이블의 경우 언제나 가능

 

alter table emp_t2 modify ename number(4); 

데이터가 있을 땐 변경되지 않는다.

 

보통 숫자가 문자 안에 포함되는 경우가 있는데 

alter table emp_t2 modify deptno varchar2(2); 

숫자에서 문자로 잘 바뀔 것 같지 않은데 이것도 에러가 난다.

빈 테이블이 아니기 때문이다.

 

따라서 데이터타입 변경 시에는 데이터가 비어있어야 한다.

 

그런데 한 가지 짚고 넘어가야 할 것이 있다.

★데이터가 존재하는 경우 항상 데이터 타입 변경은 불가하다? (X)

같은 데이터타입에서는 변경이 가능하다.

char → varchar2, varchar2 → char

는 가능하다.

즉 위 두 가지의 변경은 데이터 존재 여부와 상관없이 가능하다.

 

 

제약조건은 modify가 아니라 삭제와 연관되어 있다. 장치같은 개념이다. modify는 속성같은 개념이다(컬럼의 성격 같은 것).

varchar2는 객체가 아니라 특징, 성격 같은 것이다. 특징은 modify는 변경이 가능한데 자체적으로, 독립적으로 만들어진 오브젝트 같은 것들은 drop, add 같은 개념이다.

 

728x90

 

 

다. 컬럼 이름 변경

문법

ALTER TABLE table_name RENAME COLUMN 기존컬럼명 to 새컬럼명;

 

예) 컬럼 이름 변경

alter table emp_t2 rename column ename to name;

 

기존의 쿼리에 select문을 다 변경해야하는 경우가 생기므로 함부로 컬럼 이름을 변경해서는 안된다.

그래서 현실적으로는 사용 불가하지만 시험에 잘 나온다.

문법에 'column'이라는 단어가 들어가기 때문이다. (modify는 column 안 들어감).

동시에 여러 컬럼 이름 변경은 불가하다.

 

 

라. 컬럼 삭제

문법

ALTER TABLE table_name DROP COLUMN 컬럼명;

 

예) 컬럼 삭제

alter table emp_t1 drop column hpage;

alter table emp_t2 drop column hiredate;

위처럼 데이터가 있는 경우, 없는 경우 둘 다 삭제가 된다.

 

drop column으로 삭제한 컬럼은 recyclebin에 남지 않는다.

정리하자면, 데이터 존재 여부와 상관없이 언제나 컬럼 삭제는 가능하며 recyclebin에 남지 않는다.

구조를 삭제 했기 때문에 redo log에도 남지 않는다.

drop table보다 더 위험한 경우이다.

손쉽게 복구가 불가능하며 flashback으로도 복구가 불가하다.

만일 실수로 삭제하면 시점복구 외에는 방법이 없다.

 

시점복구(DBMS의 복구 방식)는 두 가지가 가정이 되어야 하는데,

(1) 백업본이 있어야 한다.

(2) 아카이브 로그 모드(오라클에서는 이렇게 부름)여야 한다(오라클에서 DML이나 DDL로 작업했을 시 작업의 기록이 redo log에저장되는 것).

 

★그리고 동시 삭제가 불가하다(포맷에 괄호를 쓰도록 되어있지 않음).

alter table emp_t1 drop column (birthday3, birthday4);

하면 부적합한 식별자라고 한다(못알아듣는 대상이 나오는 에러메시지).