ALTER
: 객체 구조를 수정하는 구문이다.(테이블 수정)
- ALTER TABLE 테이블명 수정할 내용;
- 수정할 내용
① 컬럼 추가 및 수정, 삭제
② 제약조건 추가 및 삭제 (수정X → 수정하려면 삭제 후 새로 추가)
③ 테이블명/컬럼명/제약조건명 수정
(1) 컬럼 추가 및 수정, 삭제
① 컬럼 추가(ADD)
: ADD 추가할 컬럼명 자료형 DEFAULT 기본값 (DEFAULT값 생략 가능)
-- DEPARTMENT 테이블을 복사한 테이블 만들기
SELECT * FROM DEPT_COPY;
ALTER TABLE DEPT_COPY ADD CNAME VARCHAR2(20);
-- CNAME 컬럼을 추가하고 기본값인 NULL로 입력된다.
ALTER TABLE DEPT_COPY ADD LNAME VARCHAR2(20) DEFAULT '기본값';
-- LNAME 컬럼을 추가하고 DEFAULT값으로 설정한 '기본값'으로 입력된다.
ALTER TABLE DEPT_COPY ADD DNAME VARCHAR2(20) DEFAULT SYSDATE;
-- DNAME 컬럼을 추가하고 DATE 타입 기본값이 SYSDATE로 입력된다.
② 컬럼 수정(MODIFY)
: MODIFY 수정할 컬럼명 바꾸려는 자료형
- DEFAULT값 수정: MODIFY 수정할 컬럼명 DEFAULT 바꾸려는 기본값
- 문자 타입에서 NUMBER 타입으로 변경할 수 없다.
- 기존 컬럼보다 작은 크기로 변경할 수 없다. (큰 크기로 변경할 수 있다.)
-- DEPT_COPY 테이블의 DEPT_ID 컬럼 자료형을 CHAR(3)로 변경하기
ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3);
ALTER TABLE DEPT_COPY MODIFY DEPT_ID NUMBER;
-- 수정할 컬럼에 담겨있는 타입과 완전히 다른 타입으로 변경할 수 없다.
-- 오류('column to be modified must be empty to change datatype') 발생.
ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(1);
-- 수정할 컬럼에 담겨있는 값보다 작은 크기로 변경할 수 없다.
-- 오류('cannot decrease column length because some value is too big') 발생.
ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(5);
-- 정상적으로 변경됨.
-- 수정할 컬럼에 담겨있는 값보다 큰 크기로 변경할 수 있다.
③ 컬럼 삭제(DROP COLUMN)
: DROP COLUMN 삭제하려는 컬럼명
- ROLLBACK은 안된다.
-- DEPT_COPY 테이블에서 DEPT_ID 컬럼 삭제하기
ALTER TABLE DEPT_COPY DROP COLUMN DEPT_ID;컬럼을 삭제한 후 ROLLBACK할 수 없다. 그리고 DEPT_COPY 테이블에 있는 모든 컬럼들을 삭제하려고 하면 오류('cannot drop all columns in a table')가 발생한다. 모든 컬럼들을 삭제하면 테이블이 존재하는 의미가 없으므로 컬럼이 최소 하나는 있어야 한다.
(2) 제약조건 추가 및 삭제
① 제약조건 추가
- PRIMARY KEY : ADD PRIMARY KEY(컬럼명)
- FOREIGN KEY : ADD FOREIGN KEY(컬럼명) REFERENCES 참조할 테이블명(참조할 컬럼명)
참조할 컬럼명은 생략 가능하고, 생략 시 기본키로 자동 연결된다.
- UNIQUE : ADD UNIQUE(컬럼명)
- CHECK : ADD CHECK(컬럼에 대한 조건)
- NOT NULL : MODIFY 컬럼명 NOT NULL (← 기본값이 NULL이라서 변경해야 한다.)
* 제약조건명을 부여할 땐, CONSTRAINT 제약조건명 제약조건
이때, 제약조건명은 고유해야 한다.
ALTER TABLE DEPT_COPY
ADD CONSTRAINT DCOPY_PK PRIMARY KEY(DEPT_ID)
ADD CONSTRAINT DCOPY_UQ UNIQUE(DEPT_TITLE)
MODIFY LNAME CONSTRAINT DCOPY_NN NOT NULL;
② 제약조건 삭제
- PRIMARY KEY와 FOREIGN KEY, UNIQUE, CHECK : DROP CONSTRAINT 제약조건명
- NOT NULL : MODIFY 컬럼명 NULL
ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_PK;
ALTER TABLE DEPT_COPY MODIFY LNAME NULL;
ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_UQ;
(3) 컬럼명/제약조건명/테이블명 변경(RENAME)
① 컬럼명 변경
: RENAME COLUMN 기존 컬럼명 TO 바꿀 컬럼명
ALTER TABLE DEPT_COPY RENAME DEPT_TITLE TO DEPT_NAME;
② 제약조건명 변경
: RENAME CONSTRAINT 기존 제약조건명 TO 바꿀 제약조건명
ALTER TABLE DEPT_COPY RENAME CONSTRAINT DCOPY_NO TO DCOPY_ID;
③ 테이블명 변경
: RENAME 기존 테이블명 TO 바꿀 테이블명
- 기존 테이블명은 생락 가능하다. (이미 기술되어 있기 때문이다.)
ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;
DROP
-- DEPT_TEST 테이블의 DEPT_ID 컬럼에 PRIMARY KEY를 추가하기
ALTER TABLE DEPT_TEST ADD CONSTRAINT DT_PK PRIMARY KEY(DEPT_ID);
-- EMPLOYEE 테이블의 DEPT_CODE 컬럼에 FOREIGN KEY를 추가하고,
-- DEPT_TEST 테이블의 DEPT_ID 컬럼을 참조하기
ALTER TABLE EMPLOYEE
ADD CONSTRAINT EMP_FK FOREIGN KEY(DEPT_CODE) REFERENCES DEPT_TEST(DEPT_ID);
DROP TABLE DEPT_TEST;
-- 오류('unique/primary keys in table referenced by foreign keys') 발생.
-- 참조하고 있는 테이블이 있어서 삭제할 수 없다.참조하는 테이블이 있는 경우 해당 테이블은 삭제할 수 없다. 이런 경우에 해당 테이블을 삭제할 수 있는 두 가지 방법이 있는데, 첫 번째로는 참조하고 있는 자식 테이블을 삭제하고 부모 테이블도 같이 삭제하는 방법이 있다. 두 번째로는 부모 테이블만 삭제하되 자식 테이블에 걸려있는 외래키 제약조건을 삭제하는 방법이 있다.
-- 첫 번째 방법
-- 자식 테이블 삭제 후 부모 테이블도 삭제하기
DROP TABLE EMPLOYEE; -- 자식 테이블
DROP TABLE DEPT_TEST; -- 부모 테이블
-- 두 번째 방법
-- 부모 테이블만 삭제하되 자식 테이블에 걸려있는 외래키 제약조건까지 삭제하기
DROP TABLE DEPT_TEST CASCADE CONSTRAINT;
+ 2024.09.03 추가
- 현재 접속 중인 데이터베이스의 모든 테이블 삭제하기
BEGIN
FOR rec IN (SELECT table_name FROM user_tables) LOOP
EXECUTE IMMEDIATE 'DROP TABLE ' || rec.table_name || ' CASCADE CONSTRAINTS';
END LOOP;
END;
- 현재 접속 중인 데이터베이스의 모든 뷰 삭제하기
BEGIN
FOR rec IN (SELECT view_name FROM user_views) LOOP
EXECUTE IMMEDIATE 'DROP VIEW ' || rec.view_name;
END LOOP;
END;
- 현재 접속 중인 데이터베이스의 모든 시퀀스 삭제하기
BEGIN
FOR rec IN (SELECT sequence_name FROM user_sequences) LOOP
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || rec.sequence_name;
END LOOP;
END;