SQL)강의- 제 12 장 테이블구조 변경과 제약조건 변경

출처 : http://naver.kaist.ac.kr/oracle_sql/s12.htm

 

1. Column의 추가

♣ Syntax

ADD절이 있는 ALTER TABLE 명령을 써서 테이블에 column을 추가할 수 있다.
◈새로운 column의 추가
◈새로운 column에 대한 Default 값을 정의
◈값을 갖고 있어야만 하는 column을 명시(NOT NULL 설정)

ALTER TABLE 테이블 명
ADD (새로운 column Datatype [DEFAULT 식] [NOT NULL]
[. 새로운 column Datatype] ...):

                DEFAULT 식은 새로운 column에 대한 default 값을 지정하며,
                NOT NULL은 새로운 column에 NOT NULL 제약조건을 추가한다.

◈기존의 column을 삭제할 수는 없다.
◈Column을 추가하거나 수정할 수 있지만 테이블에서 삭제할 수는 없다.
◈Column의 위치를 저장할 수 없습니다. 새로운 column은 테이블의 마지막 column이 된다.
 

♣ Column의 추가 : 예

◈새로운 column은 테이블의 마지막 column이 된다.

♠실습 :  S_REGION 테이블에 COMMENTS column을 추가

SQL> ALTER TABLE s_region      ADD(comments     VARCHAR2(255));

 

2. Column의 변경

♣ Syntax  :

ALTER TABLE 테이블 명
    MODIFY (column Datatype [DEFAULT 식] [NOT NULL]
    [. Column Datatype] ...):

◈Column의 크기, default 값, NOT NULL column 제약조건을 변경합니다.
◈column의 크기를 확장
◈column이 널 값만 가지고 있거나 테이블에 아무 행도 없으면 column의 폭을 축소
◈Default 값 변경 (다음 INSERT 부터 적용)
◈column에 NULL 값이 없을 경우에만 NOT NULL 제약 조건을 추가
 

♣ Column의 변경 : 예

♠실습 : S_EMP 테이블의 TITLE column의 최대 길이를 50 문자로 확장.

SQL> ALTER TABLE s_emp   MODIFY (title   VARCHAR2(50));

 

3. 제약조건 추가/삭제

♣ 제약조건 추가

◈Column의 크기, default 값, NOT NULL column 제약조건을 변경한다.
◈Syntax

ALTER TABLE 테이블 명
ADD [CONSTRAINT 제약조건] type (column):

◈제약조건을 수정하는 것이 아닌 추가나 삭제
◈MODIFY 절을 써서 NOT NULL 제약조건 추가

♠실습 : S_EMP 테이블ID column이 MANAGER_ID column을 참조하도록 S_EMP 테이블에
           foreign key 제약조건을 추가한다.

SQL> ALTER TABLE s_emp              ADD CONSTRAINT s_emp_manager_id_fk    FORGIGN KEY (manager_id)                    REFERENCES s_emp(id);

 

♣ 제약조건 삭제

◈제약조건을 삭제하기 위해서 USER_CONSTRAINTS와 USER_CONS_COLUMNS dictionary 뷰로
   부터 제약조건을 확인할 수 있다.
◈DROP절이 있는 ALTER TABLE명령을 사용한다.
◈DROP절의 CASCADE옵션은 종속적인 다른 제약조건도 삭제한다.
◈Syntax

ALTER TABLE 테이블 명
   DROP PRIMARY KEY|UNIQUE(column)
     CONSTRAINT 제약조건[CASCADE]:

◈제약조건을 수정하는 것이 아닌 추가나 삭제

♠실습 : S_EMP 테이블에서 관리자 제약조건을 삭제한다.

SQL> ALTER TABLE s_emp      DROP  PRIMARY KEY CASCADE;

 

 ♠실습 : S_DEPT 테이블의 PRIMARY KEY 제약조건을 삭제하고 S_EMP.DEPT_ID column의
            FORREIGN KEY 제약조건을 삭제한다.

SQL> ALTER TABLE s_region      ADD(comments VARCHAR2(255));

 

 4. 제약조건 ENABLE, DISABLE 시키기

◈ENABLE이나 DISABLE 절이 있는 ALTER TABLE 명령을 써서 삭제하거나 재생성하지 않고도
   제약조건을 사용가능이나 사용불가 상태로 만들 수 있다.
◈Syntax :
      ALTER TABLE 테이블 명
           DISABLE | ENABLE CONSTRAINT 제약조건 [CASCADE];
◈제약조건을 enalbe시키면 테이블의 모든 데이터에 적용된다. 테이블의 모든 데이터가 제약조건에
   맞아야 한다.
◈UNIQUE나 PRIMARY KEY 제약조건을 enable시키면 UNIQUE나 PRIMARY KEY 인덱스가 자동으로
   생성된다.
◈TABLE 명령과 ALTER TABLE 명령 모두 ENABLE과 DISABLE 절을 쓸 수 있다.
◈CASCAED 절은 종속적인 무결성 제약조건을 disable 상태로 만든다.
 

♣ 제약조건 ENABLE

◈ENABLE 절을 써서 테이블의disable된 제약조건을 enable시킬 수 있다.

SQL> ALTER TABLE s_emp  ENABLE CONSTRAINT S_DEPT_ID_PK;

◈UNIQUE나 PRIMARY KEY 제약조건을 enable시키면 UNIQUE나 PRIMARY KEY Index가 자동으로 생성된다.

♣ 제약조건 DISABLE

◈무결성 제약조건을 disable시키기 위해 ALTER TABLE 명령의 DISABKE를 이용한다.
◈종속적인 무결성 제약조건을 disable시키기 위해 CASCADE 옵션을 적용한다.

SQL> ALTER TABLE s_emp   DISABLE CONSTRAINT S_DEPT_ID_PK CASCADE;

 

5. 테이블 삭제

◈해당테이블의 모든 인덱스가 삭제된다.
◈CASCADE CONSTRAINTS 옵션은 종속 무결성 제약조건을 삭제한다.
◈DROP TABLE 명령은 Oracle 테이블의 정의를 삭제한다.
◈테이블을 삭제하면 테이블의 모든 데이터 및 관련된 모든 인덱스가 없어진다.
◈CASCADE CONSTRAINTS 옵션은 종속적인 참조 무결성 제약조건도 삭제할 것이다.
◈모든 데이터가 테이블에서 삭제된다.
◈테이블로 참조하는 뷰, 함수, 패키지는 남아있지만 부적합하게 된다.
◈트랜잭션은 자동 커밋된다.
◈DROP ANY TABLE권한을 가진 사용자나 테이블을 만든 사람만이 테이블을 삭제할 수 있다.
◈Syntax : DROP TABLE 테이블  [CASCADE CONSTRAINT];

◈DROP TABLE 명령은 한 번 실행하면 되돌릴 수 없다. Oracle7 Sever는 DROP TABLE명령을 쓸 때
   아무 질문도 하지 않는다. 해당 테이블의 소유자이거나 높은 레벨의 권한이 있으면 테이블이 즉시
   삭제된다. 모든 DDL명령은 자동 커밋을 한다.

♠실습 : TEST2 테이블을 완전히 삭제하시오.

SQL> DROP  TABLE test2;

 

6. TRUNCATE와 테이블 이름 변경(RENAME)

♣ 테이블 이름 변경 - RENAME 명령

◈RENAME 명령은 케이블, 뷰, sequence, synonym 의 이름을 변경하는 데에 사용한다.
◈이름을 바꾼 객체의 소유자이어야 한다.
◈Syntax : RENAME 이전이름 TO 새로운 이름;

 

♣ TRUNCATE 명령

◈TRUNCATE명령은
      -테이블의 모든 행을 삭제하고
      -테이블이 사용한 저장 공간을 사용가능하도록 하는
      -DDL명령이다.
◈TRUNCATE를 쓰면 행의 삭제를 롤백할 수 없다.
◈행을 삭제하는 데에 사용할 수 있는 명령은 DELETE명령이다.
◈테이블의 소유자이거나 DELETE TABLE권한이 이어야 한다.
◈DELETE명령은 테이블의 모든 행을 삭제할 수 있지만 저장 공간을 해제하지는 못한다.
◈Syntax  : TRUNCATE TABLE테이블;

 

7. 테이블에 주석 입력

COMMENT 명령을 써서 column,테이블, 뷰, 대한 주석을 2000 바이트까지 추가할 수 있다.
주석은 data dictionary에 저장되고 아래와 같은 dictionary뷰를 이용하여 입력된 값을 볼 수 있다.
◈ ALL_COL_COMMENTS
◈ USER_COL_COMMENTS
◈ALL_TAB_COMMENTS
◈ USER_TAB_COMMENTS
◈Syntax  : COMMENT ON TABLE테이블 명 COLUMN 테이블 명.column IS '텍스트'

                 여기서, 텍스트는 주석이다.


♠실습 : S_EMP테이블에 주석을 추가하십시오.

SQL> COMMENT ON TABLE s_emp IS 'Employee Information';

 

♠실습 : column의 주석을 삭제하십시오.

SQL> COMMENT ON COLUMN s_emp.last_name IS ''