본문 바로가기

.주제별/SQL,DB

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 ''