[ORACLE] 테이블 수정 문법 ALTER TABLE

 

 

---

이미 생성된 테이블의 필드와 제약조건을 수정, 추가, 삭제하는

ALTER TABLE 알아보기

---

 

 

0. ALTER 명령

---

Alter는 이미 생성되어 있는 테이블 또는 뷰, 사용자 등의 구조를 수정하기 위한 명령이다.

ALTER TABLE, ALTER USER ... 의 구조 중 ALTER TABLE을 집중적으로 다뤄볼 것이다.

 

 

1. 필드명의 수정

---

ALTER TABLE 테이블이름 RENAME COLUMN 변경전필드이름 TO 변경후필드이름;

 

 

예시1. emp 테이블에 name 이란 필드명을 emp_name으로 바꾸고자 한다면?

ALTER TABLE emp RENAME COLUMN name to emp_name;

 

예시2.

-- booklist 테이블의 subject 필드명을 title로 수정
ALTER TABLE booklist RENAME COLUMN subject TO title;

 

예시3.

-- rentlist 테이블의 rent_date 필드를 rentdate로 변경
ALTER TABLE rentlist RENAME COLUMN rent_date TO rentdate;

 

 

 

2. 필드의 자료형 변경

---

ALTER TABLE 테이블명 MODIFY 필드명 자료형 [디폴트값 또는 제약사항];

 

 

예시1. emp 테이블에서 emp_name 필드의 자료형을 VARCHAR2(15)로 변경한다면?

ALTER TABLE emp MODIFY emp_name VARCHAR2(15);

 

예시2.

-- varchar2(12)였던 memberlist 테이블의 name 필드를 varchar2(30)으로 변경
ALTER TABLE memberlist MODIFY name VARCHAR2(30);

 

예시3.

-- memberlist의 membernum 필드를 number(5)로 자료형 변경
ALTER TABLE memberlist MODIFY membernum MEMBER(5);

 

 

만약, 아래와 같이 외래키로 연결된 필드를 수정하려고 하면 어떻게 될까?

create table rentlist(
    rent_date date default sysdate,
    numseq number(4),
    bnum varchar2(5) not null,
    mnum varchar2(5) not null,
    discount number(4) default 0,
    
    constraint rent_pk primary key(rent_date, numseq),
    -- 현재테이블의 bnum 필드는 booklist 테이블의 booknum 필드값을 참조
    constraint fk1 foreign key(bnum) references booklist(booknum),
    -- 현재테이블의 mnum 필드는 memberlist 테이블의 membernum 필드값을 참조
    constraint fk2 foreign key(mnum) references memberlist(membernum)
);

-- memberlist의 membernum 필드를 number(5)로 자료형 변경
ALTER TABLE memberlist MODIFY membernum number(5); -- 수정 실패

-- rentlist의 bnum 필드를 number(5)로 자료형 변경
ALTER TABLE rentlist MODIFY bnum number(5); -- 수정 실패

-- rentlist의 mnum 필드를 number(5)로 자료형 변경
ALTER TABLE rentlist MODIFY mnum number(5); -- 수정 실패

 

외래키(foreign key)는 참조하는 필드와 참조되는 필드의 자료형이 같아야 한다. 위의 예시에서는 한 필드의 자료형을 변경하려고 하면 자료형이 달라지기 때문에 에러가 발생한다. 외래키 관계가 있는 필드의 자료형 수정은 외래키 제약사항을 삭제하고, 변경하려는 필드의 자료형을 변경한 후 다시 외래키를 설정하는 방법으로 진행한다. (5번 제약조건 수정, 추가, 삭제란 참고)

 

 

 

3. 필드의 추가

---

ALTER TABLE 테이블명 ADD 필드명 자료형;

 

 

예시1. emp 테이블에 직급을 나타내는 grade 필드를 varchar2(20)으로 추가한다면?

ALTER TABLE emp ADD grade VARCHAR2(20);

 

예시2.

-- memberlist에 성별(gender) 필드를 varchar2(3)으로 추가
ALTER TABLE memberlist ADD gender VARCHAR2(3);

 

예시3.

-- memberlist에 나이(age) 필드를 number(2)로 추가
ALTER TABLE memberlist ADD age NUMBER(2);

 

 

 

4. 필드의 삭제

---

ALTER TABLE 테이블명 DROP COLUMN 필드명;

 

 

예시1. emp 테이블에서 직급을 나타내는 grade 필드를 제거한다면?

ALTER TABLE emp DROP COLUMN grade;

 

예시2.

-- memberlist 테이블에서 성별 필드 제거
ALTER TABLE memberlist DROP COLUMN gender;

 

예시3.

-- memberlist 테이블에서 나이 필드 제거
ALTER TABLE memberlist DROP COLUMN age;

 

 

 

5. 제약 조건의 수정, 추가와 삭제

---

5-1. 제약 조건 수정

ALTER TABLE 테이블명 MODIFY 필드명 자료형 CHECK(조건);

 

 

예시1.

-- memberlist의 gender 필드에 'M', 'F' 둘 중 하나만 입력되게 제약 조건을 설정한다면?
ALTER TABLE memberlist MODIFY gender VARCHAR(3) CHECK(gender='M' or gender='F');

 

SQL문 안에서 WHERE 절이나 CHECK 절 내에서 사용한 ' = ' 은 같다는 의미를 갖는다.

 

 

예시2.

-- 같은 뜻, IN 사용 방식
ALTER TABLE memberlist MODIFY gender VARCHAR(3) CHECK(gender IN('M', 'F'));

 

gender 값이 IN 함수 안에 있는 'M' 혹은 'F' 둘 중 하나인 경우 true 라는 의미다.

 

 

 

5-2. 제약 조건 추가

ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건식

 

테이블 레벨의 제약조건은 제약조건 이름과 함께 추가한다.

 

 

예시1.

-- memberlist 테이블의 나이(age)필드에 10 미만 120살이 초과되는 나이는 입력되지 못하게 제약조건 추가
ALTER TABLE memberlist ADD CONSTRAINT check_age CHECK(age>=10 AND age<120);

 

예시2.

-- rentlist의 기본키(rent_pk)를 numseq로 설정
ALTER TABLE rentlist ADD CONSTRAINT rent_pk PRIMARY KEY(numseq);

 

예시3.

-- rentlist의 외래키 fk1 추가 (rentlist의 bnum과 booklist의 booknum)
ALTER TABLE rentlist ADD CONSTRAINT fk1 FOREIGN KEY(bnum) REFERENCES booklist(booknum);

 

예제4.

-- rentlist에 mnum 필드를 memberlist에 membernum 필드가 참조하게 함, 제약조건 이름은 fk2
ALTER TABLE rentlist ADD CONSTRAINT fk2
FOREIGN KEY(mnum) REFERENCES memberlist(membernum) ON DELETE CASCADE;

 

ON DELETE CASCADE는 부모레코드가 삭제되면 참조되는 자식레코드도 같이 삭제되로록 설정하는 일종의 트리거다. 위의 예시에서 memberlist에 membernum이 사라지면 rentlist에 해당 회원 넘버가 함께 사라진다.

 

 

 

5-3. 제약 조건 삭제

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

 

 

예시1.

-- rentlist 테이블의 bnum에 걸려있는 외래키 제약조건 제거(fk1)
ALTER TABLE rentlist DROP CONSTRAINT fk1;

 

예시2.

-- rentlist 테이블의 mnum에 걸려있는 외래키 제약조건 제거(fk2)
ALTER TABLE rentlist DROP CONSTRAINT fk2;

 

예시3.

-- rentlist 테이블의 기본키 제거(rent_pk)
ALTER TABLE rentlist DROP CONSTRAINT rent_pk;

 

 

 

6. 사용자 수정

---

ALTER USER 사용자이름 IDENTIFIED BY 비밀번호;