ORACLE / DDL - ALTER

ALTER

 

객체 구조를 수정하는 구문

    - 컬럼 추가/수장/삭제

    - 제약조건 추가/삭제 (제약조건의 수정은 불가)

    - 테이블명/컬럼명/제약조건명 수정

ALTER TABLE 테이블명 수정할내용;

 

 

컬럼추가(ADD)

 

DEFAULT값 생략가능

ADD 추가할컬럼명 자료형 DEFAULT 기본값

--CNAME컬럼 추가
ALTER TABLE DEPT_COPY ADD CNAME VARCHAR2(20);
--새로운 컬럼 생성 후 기본값인 NULL로 채워짐
ALTER TABLE DEPT_COPY ADD LNAME VARCHAR2(20) DEFAULT '기본값';
--새로운 컬럼 생성 후 DEFAULT값으로 설정한 "기본값"으로 채워짐
ALTER TABLE DEPT_COPY ADD DNAME DATE DEFAULT SYSDATE;
--DNAME이라는 컬럼을 추가하고 DATE타입 기본값은 SYSDATE로 입력하시오

 

 

컬럼 수정(MODIFY)

 

문자 -> NUMBER 변경 불가

사이즈 축소 불가

사이즈 확대 가능

//컬럼 자료형 수정
MODIFY 수정할컬럼명 바꿀자료형

//DEFAULT 값 수정
MODIFY 수정할컬럼명 DEFAULT 바꿀기본값

--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);


--한번에 여러개 컬럼 변경
--DEPT_TITLE컬럼의 데이터타입을 VARCHAR2(40)으로
--LOCATION_ID 컬럼의 데이터타입을 CARCHAR2(2)로
--LNAME 컬럼의 기본값을 '미국'으로 
ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE VARCHAR2(40)
MODIFY LOCATION_ID VARCHAR2(2)
MODIFY LNAME DEFAULT '미국'; 
--원래 있던값은 변경되지 않으며 새로들어오는값은 변경한 디폴트 '미국'으로 됨

 

 

컬럼삭제(DROP COLUMN)

 

ALTER TABLE 테이블명 DROP COLUMN 삭제할컬럼명

--DEPT_ID 컬럼 지우기
ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_ID;
ROLLBACK; --롤백 완료떠도 롤백 불가(DDL이여서)

--DEPT_COPY2 모든 컬럼 삭제해보자
ALTER TABLE DEPT_COPY2 DROP COLUMN DEPT_TITLE;
ALTER TABLE DEPT_COPY2 DROP COLUMN LOCATION_ID;
ALTER TABLE DEPT_COPY2 DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY2 DROP COLUMN LNAME;
ALTER TABLE DEPT_COPY2 DROP COLUMN DNAME; 
--마지막 삭제 명령문에서 오류발생! cannot drop all columns in a table 
--최소 한개 컬럼은 존재해야하기 때문에 모두 삭제할수 없다

 

 

제약조건 추가 / 삭제

 

• 제약조건 추가

 :  (기본상태가 NULL이 아니여서 변경해야함)
    나만의 재약조건명을 부여하고자 한다면

    -주의: 제약조건명은 고유해야한다

구분	표현법	비고
PRIMARY KEY	ADD PRIMARY KEY(컬럼명);
FOREIGN KEY	ADD FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명(참조컬럼명);	참조컬럼명은 생략 가능하며 생략시 기본키로 자동연결
UNIQUE	ADD UNIQUE(컬럼명)
CHECK	ADD CHECK(컬럼에대한조건)
NOT NULL	MODIFY 컬럼명 NOT NULL
	MODIFY 컬럼명 CONSTRAINT 제약조건명 제약조건	제약조건명은 고유해야함

--DEPT_COPY 테이블에
--DEPT_ID 컬럼에 PRIMARY KEY 추가
--DEPT_TITLE 컬럼에 UNIQUE 추가
--LNMAE 컬럼에 NOT NULL 추가
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;

--DEPT_COPY 테이블로부터 DCOPY_PK 삭제
ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_PK;

--DEPT_COPY 테이블로부터 LNAME NOT NULL, DEPT_TITLE UNIQUE 삭제
ALTER TABLE DEPT_COPY MODIFY LNAME NULL;
ALTER TABLE DEPT_COPY DROP CONSTRAINT DCOPY_UQ;

 

 

컬럼명 / 제약조건명 / 테이블명 변경 (RENAME)

 

• 컬럼명 변경

ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명

--DEPT_COPY 테이블에서 DEPT_TITLE컬럼을 DEPT_NAME으로 바꾸기
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;
--DEPT_COPY 테이블에서 DNAME 컬럼을 DDAY로 변경하기
ALTER TABLE DEPT_COPY RENAME COLUMN DNAME TO DDAY;

 

• 제약조건명 변경

ALTER TABLE 테이블명 RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명

--DEPT_COPY테이블에서 SYS_C007150를 DCOPY_ID로 변경
ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS_C007150 TO DCOPY_ID;
--DEPT_COPY테이블에서 LOCATION_ID에 걸려있는 제약조건명을 DCOPY_LID로 변경하기 SYS_C007151
ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS_C007151 TO DCOPY_LID;

 

• 테이블이름 변경

ALTER TABLE 테이블명 RENAME TO 바꿀테이블명

--DEPT_COPY 테이블 이름 DEPT_TEST로 변경
ALTER TABLE EMP_01 RENAME TO EMP_001;
--RENAME 기존테이블명 TO 바꿀테이블명
RENAME EMP_001 TO EMP_01;