[Oracle] 테이블 수정 Alter Table문

💡테이블 수정하기

---

• 테이블 정의 수정 > 스키마 수정 > 컬럼 수정 > 컬럼명 OR 자료형 (길이) OR 제약사항 등 수정

테이블을 수정한다는 건 실제로는 컬럼명, 자료형, 제약사항 등의 것들을 수정하는 걸 의미한다.

테이블을 수정하는 상황은 되도록 피하도록 한다.

테이블을 수정하는 상황을 만들기 이전에 설계를 완벽하게 해야 한다.🏗️

 

[Oracle] SQL 키워드, 식별자, 자료형

[Oracle] 제약 사항 (Constraint)

테이블의 컬럼명, 자료형, 제약사항에 대한 글이다.

위 글을 참고하면서 본문을 읽는 것을 추천한다.

 

💡테이블 수정 해결 방법

---

1. 테이블 삭제 (DROP)

테이블을 삭제하는 방법은 테이블 DDL(CREATE)로 수정하고 수정된 DDL로 새롭게 테이블을 생성하는 방법이다.
ALTER 명령어를 쓰는 것과 비용이 비슷하고, 뒤탈이 없기 때문에 많이 사용한다.

• a. 기존 테이블에 데이터가 있는 경우: 문제없음
• b. 기존 테이블에 데이터가 없는 경우: 미리 데이터 백업

기존 테이블의 데이터를 계속 사용해야 하는 경우, 데이터를 백업한 다음에 테이블을 삭제해야 한다.
그리고 수정된 테이블을 다시 생성하고, 백업한 데이터를 다시 복구한다.

2. ALTER 명령어 사용

ALTER 명령어를 사용하는 방법은 기존 테이블의 구조를 변경하는 방법이다.

• a. 기존 테이블에 데이터가 있는 경우: 문제없음
• b. 기존 테이블에 데이터가 없는 경우: 경우에 따라 비용 차이 발생

서비스 운영 중에 ALTER로 테이블을 수정하면 테이블을 삭제할 때보다 부담은 덜하긴 하지만, 안심하고 마음대로 사용하기에는 애매하다.

물론 ALTER 명령어를 사용해 테이블을 수정할 때에도 서비스는 중단 상태여야 한다.

 

💡ALTER 명령어 사용

---

DROP TABLE tblEdit;

CREATE TABLE tblEdit (
	seq NUMBER PRIMARY KEY,
	data varchar2(20) NOT NULL
)

INSERT INTO tblEdit VALUES (1, '마우스');
INSERT INTO tblEdit VALUES (2, '키보드');
INSERT INTO tblEdit VALUES (3, '모니터');

SELECT * FROM tblEdit;

 

Case 1. 새로운 컬럼을 추가하기

ADD

ALTER TABLE tblEdit
	ADD (price NUMBER);

ADD 괄호 안에 새로 정의할 컬럼을 넣으면 새로운 컬럼을 추가할 수 있다.

 

--ORA-01758: table must be empty to add mandatory (NOT NULL) column
ALTER TABLE tblEdit
	ADD (qty NUMBER NOT NULL)

구문에 문제가 있어서 에러가 나는 게 아니라. mandatory 컬럼(NOT NULL)을 추가하려면 테이블이 반드시 비어 있어야 한다는 오류가 발생했다.

NOT NULL 컬럼이 문제 되는 이유는 조금해 생각 보면 알 수 있다. 테이블의 다른 컬럼에 데이터가 들어 있는 상태이다. NOT NULL 컬럼을 생성하면 데이터에 값을 넣어 주어야 하는데, 생성 직후 데이터를 추가하는 작업이 없기 때문에 NOT NULL 컬럼에 NULL이 들어가므로 오류가 발생하는 것이다.

이 경우에는 백업을 하고 테이블을 삭제한 뒤에 수정한 테이블을 생성하여 데이터블 다시 불러오는 작업이 필요하다.

경우에 따라 비용 차이가 발생한다는 것이 바로 이 상황을 두고 하는 말이다.

 

DROP TABLE tblEdit;

백업을 했다는 가정 하에, 테이블을 삭제한 뒤에 다시 테이블을 생성하고 구문을 실행하니 정상적으로 NOT NULL 컬럼이 생성되었다.

 

INSERT INTO tblEdit VALUES (1, '마우스', 1000, 1);
INSERT INTO tblEdit VALUES (2, '키보드', 2000, 1);
INSERT INTO tblEdit VALUES (3, '모니터', 3000, 1);

컬럼 삭제를 실행해 보기 위해 새로운 컬럼을 생성하고 데이터를 추가하였다.

 

DEFAULT

ALTER TABLE tblEdit
	ADD (color varchar2(30) DEFAULT 'white' NOT NULL );

NOT NULL임에도 불구하고 기존 데이터를 삭제하지 않고도 DEFAULT를 이용하면 테이블을 수정할 수 있다.

[컬럼명, 자료형, DEFAULT 넣을 값, 제약 사항] 순으로 작성한다.

 

Case 2. 컬럼 삭제하기

ALTER TABLE tblEdit
	DROP COLUMN color;

ALTER TABLE tblEdit
	DROP COLUMN qty;

ALTER TABLE tblEdit
	DROP COLUMN seq; --PK 삭제

컬럼을 삭제할 때 주의할 점은 기본키도 지울 수 있다는 점이다.
🚫기본키를 지우는 일은 절대 발생하지 않도록 주의한다.

 

Case 3. 컬럼 수정하기

1. 컬럼 길이 수정하기(확장/축소)

--ORA-12899: value too large for column "HR"."TBLEDIT"."DATA" (actual: 53, maximum: 20)
INSERT INTO tblEdit VALUES (4, '에이수스 ASUS VivoBook Pro 16X OLED N7600PC-L2007')

20바이트를 넘는 데이터를 추가하려고 하자 에러가 발생했다.

maximum의 길이를 늘이면 넣을 수 없는 데이터를 추가할 수 있다.

 

ALTER TABLE tblEdit
	MODIFY (DATA varchar2(100));

MODIFY로 데이터의 길이를 100byte로 확장하여 긴 문자열의 데이터를 추가할 수 있게 되었다.

 

--ORA-01441: cannot decrease column length because some value is too big
ALTER TABLE tblEdit
	MODIFY (DATA varchar2(20));

그런데 데이터의 길이를 축소하려고 하니 오류가 발생한다.

데이터의 길이를 확장하는 건 쉽지만, 축소할 때에는 이와 같이 오류 발생에 주의가 필요하다.

 

2. 컬럼의 제약 사항 수정하기

ALTER TABLE tblEdit
	MODIFY (DATA varchar2(100) NULL);

INSERT INTO tblEdit VALUES (5, NULL);

원래 DATA 컬럼은 NULL이 들어갈 수 없었지만, 제약 사항을 수정하여 NULL을 추가할 수 있게 되었다.

 

3. 컬럼의 자료형 바꾸기

--ORA-01439: column to be modified must be empty to change datatype
ALTER TABLE tblEdit
	MODIFY (DATA NUMBER);

컬럼의 자료형을 바꾸려고 하자 비어있는 경우에만 자료형을 바꿀 수 있다는 오류가 발생한다.
테이블의 데이터를 지우면 제대로 실행이 된다.

 

테이블 설계 예시

Microsoft SQL Database Design Template | DB Designer

Microsoft SQL Database Design Template, by U, Feb 25, 2021, Schema, Short Link, Template