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

Programming/Database

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

하더코 2024. 10. 28. 19:33

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

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 비밀번호;

저작자표시 비영리 동일조건