본문 바로가기

별걸다하는 IT/데이터베이스 Database

[SQL]새 컬럼 추가/삭제하기,데이터 있을 경우 컬럼 유형 (타입) 변경하기 (alter, modify) 등 상황별 정리

 

데이터베이스(DB, DBMS) 목차

 

안에 데이터가 있을 경우 또는 없을 경우, 어떤 제약이 있을 경우 또는 없을 경우 등

같은 sql명령어로다 상황에따라 결과가 달라지는 데 기본 개념과 함께 한 번에 정리해볼까 합니다.

 

↓ emp 테이블

[예시로 사용할 emp 테이블 정보]

[예시로 사용 할 emp 테이블 내부 데이터]

 

새 컬럼 추가 문법

alter table 해당테이블명

add 추가할컬럼명 추가할컬럼의데이터타입;

alter table emp add newCol int not null

라클의 경우 위와 같이 not null 을 지정해서 컬럼을 추가하게 되면 데이터가 없을 경우에는 잘 수행되지만

안에 데이터가 있을 경우

오류보고 - table must be empty to add mandatory (NOT NULL) column 이라고 오류가 뜨게 됩니다.

오류가 뜨는 이유는 논리적으로 not null이면 null값이 들어가면 안되는데

이미 기존에 데이터가 있을 경우 not null의 제약조건을 가진 새 컬럼이 추가 되면

기존의 데이터가 가지게 되는 새 열의 값에 null이 들어간 것과 마찬가지가 되어버려 문제가 생기기 때문입니다.

 

 

반면 mysql은 not null 제약조건을 포함해서 새로 열을 생성해도 오류가 나지 않습니다. 

 

 

 

[mysql의 경우]

newCol이라는 열을 새로 생성했는데, mysql의 경우 내부적으로,

null값에 0을 대신 삽입해줌으로써 null이 들어가는 것을 방지하는 동작을 처리해주기 때문입니다.

 

 

그러므로 오라클의 경우에도 위 문제를 해결하기 가장 좋은 방법은 mysql처럼 똑~같은 액션을 해주는 default라는 제약조건을 사용하는 거예요. 디폴트는 간단히말해, 아무런 값을 입력하지 않았을 때 자동으로 디폴트로 값이 입력되도록 하게 하는 것입니다.

(mysql 이 null 자리에 0을 채워준것처럼)

alter table emp add newCol int default 0 not null

 

컬럼 삭제 문법

alter table 해당테이블명

drop column 삭제할컬럼명;

 

컬럼 타입 변경 문법

sql server/ms access의 경우에는 alter라는 키워드를 사용하고 my sql/ oracle의 경우에는 modify라는 키워드를 사용합니다.

 

alter table 해당테이블명

alter/column 컬럼명 바꿀데이터타입;

emp 테이블의 ID컬럼 int 타입을 char(10)으로 변경하는 예시로 들어볼게요

-- SQL server/ MS Access:
alter table emp
alter column ID char(10);

--My SQL / Oracle (10G이전버전)
alter table emp
modify column id char(10);

--10G이후버전의 oracle -> column 키워드만 없어짐
alter table emp
modify id char(10);

그림과 같이 테이블 안에 아무 데이터가 없을 때 뿐만 아니라 데이터가 있어도 int가 char로 자동으로 변환되어 잘 적용됩니다. (mysql경우)

하지만! 오라클의 경우

안에 데이터가 있을 경우 컬럼 타입 변경을 시도하면

 

 

오류보고 - ORA-01349: column to be modified mus be empty to change datatype 이라면서 오류가 나게 됩니다.

 

이럴 경우,

1. 변경하고자 하는 데이터 타입을 가진 새로운 컬럼을 새로 생성

2. 기존 컬럼의 데이터를 새 컬럼으로 복사

3. 기존 컬럼 삭제

4. 새 컬럼의 이름을 기존 컬럼의 이름으로 변경

작업을 해주셔야 합니다. 

 

alter table emp add temp char(10); -- emp테이블에 임시 컬럼 추가하기
update emp set temp = id;          --emp 테이블의 temp 컬럼에다가 id 컬럼 내용 대입
alter table emp drop column id;    -- emp 테이블의 기존 id컬럼 버리기
alter table emp rename column temp to id; --temp 컬럼의 이름을 id로 바꾸기
--만약 삭제한 컬럼이 기본키라면 (즉 변경하고자하는 컬럼이 기본키면) 
--마지막으로 기본키제약조건 다시 추가해주기--
alter table emp add primary key (id);

또, 아래의 empfamily 테이블이 emp테이블에서 바꾸려는 컬럼을 참조하고 있는 상황처럼

create table empfamily (
 empid int ,
 familycnt int,
 primary key (empid),
 foreign key (empid) references emp(id) #
 --emp 테이블의 id열을 내가 empid 열에서 참조하겠다
);

즉 변경하려는 컬럼이 다른 테이블에서 참조되고 있을 때 변경하려고 시도하면 (위의 예시는 mysql 테이블 생성은 여기 참조)

Error Code: 1833. Cannot change column 'id' : used in a foreign key constraint ~

와 같이 에러가 발생합니다.

 

 

이럴 경우 삭제하고자 하는 열(id)을 참조하고 있는 empfamily 테이블의 empid의 참조 규칙을 삭제해줘야 합니다.

삭제하려면 그 제약조건의 이름을 알아야 삭제할 수 있습니다.

 

 

1
2
3
4
--mysql
show create table empfamily; 
--oracle
select * from USER_CONSTRAINTS where table_name ='EMPFAMILY';
cs

 

[show create table empfamily]

 

[select * from USER_CONSTRAINTS where table_name ='EMPFAMILY']

 

제약조건 이름을 알 수 있는 방법은 다양하지만 저한테는 이게 제일 간편하더라고요 ㅎ

여기 보면 왼쪽그림의 경우 이름이 empfalily_ibfk_1 이라는 것을 알 수 있습니다.

오른쪽의 그림의 경우 constraint_type이 R인 것이 foreign key 제약조건을 가리킵니다.

해당 이름을 사용해서 제약조건 삭제하고 나면 이제 기존 문법대로 컬럼 타입을 변경할 수 있습니다.

 

 

1
2
3
alter table empfamily drop foreign key empfamily_ibfk_1; (mysql)
alter table empfamily drop constraint sys_c007535; (오라클)
alter table emp modify column id char(10);
cs

 

문제~

아래의 table에 대해서 보기의 sql중 에러가 나지 않는 sql은? (2018년 11월 31회차 시험 기출)

[table]

create table sqld_31_24

( PK1 NUMBER(3) PRIMARY KEY,

C1 VARCHAR2(10),

C2 DATE,

 

 

 

C3 NUMBER(3) );

 

[데이터]

PK1

 C1

 C2 

 C3 

 1

 ABC

 2018-12-02 08:30:28 

 100 

 2

 XXX

 2018-12-04 08:30:47

 200

 3

 YYY

 2018-12-05 08:30:49

 300

 4

 

 2018-12-06 08:32:45

 400

 

1. ALTER TABLE SQLD_31_24 MODIFY PK1 NUMBER(5);

2. ALTER TABLE SQLD_31_24 MODIFY C1 VARCHAR2(10) NOT NULL;

3. ALTER TABLE SQLD_31_24 MODIFY C2

4. ALTER TABLE SQLD_31_24 MODIFY C3 VARCHAR2(5);

 

답: 1.(pk1은 같은 타입)

 

도움이 됐으면 공감!