17. Chapter11 Data Definition(20081104)

Alter Tables (P11-2)

• 테이블의 구조를 변경할 때 사용
• 추가(ADD), 수정(MODIFY), 삭제(DROP)할 수 있음
• DB 운영중에 삭제를 하면 문제가 생길 수 있다. (시간 등) -> UNUSED 사용해서 없는 것처럼 해놓는다~

 

 

Add Columns (P11-4)

• 테이블에 데이터가 하나라도 있는 상태에서는 'NOT NULL' 제약조건을 사용할 수 없다.
• 기존 테이블에 있는 데이터(레코드)에도 새로운 칼럼이 추가되는데 이때 기존 레코드의 새로추가된 컬럼에는 초기값으로 null이 들어가므로 문제가 됨~(NOT NULL 제약조건을 쓴다면~)

 

 

Unused Columns (P11-7)

• Unused 시킨 컬럼은 다시 복구할 수 없다.(DROP 시킨거라고 생각하면 됨)
• Unused 컬럼 확인 방법

1. SQL> DESC user_unused_col_tabs;
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
2.  TABLE_NAME                                NOT NULL VARCHAR2(30)
    COUNT                                              NUMBER

 

• Unused 컬럼의 데이터는 있어도 확인방법이 없고, 같은 이름의 컬럼은 새로 만들 수 있음.

1. // loc 컬럼 unused 시킴
2. SQL> ALTER TABLE dept
     2  SET UNUSED(loc);
3.  
4. Table altered.
5.  
6. // loc 컬럼 출력안됨.
7. SQL> SELECT * FROM dept;
8.     DEPTNO DNAME          COMMENTS
   ---------- -------------- ------------------------------
           10 ACCOUNTING
           20 RESEARCH
           30 SALES
           40 OPERATIONS
           50 Local Branch
9.  
10. // unused된 loc컬럼명으로 add
11. SQL> ALTER TABLE dept
      2  ADD(loc varchar2(20));
12. Table altered.
13.  
14. // loc 컬럼 출력됨~
15. SQL> SELECT * FROM dept;
16.     DEPTNO DNAME          COMMENTS                       LOC
    ---------- -------------- ------------------------------ --------------------
            10 ACCOUNTING
            20 RESEARCH
            30 SALES
            40 OPERATIONS
            50 Local Branch

 

 

Add Constraints (11-9)

• NOT NULL 조건은 제외~
• 기본키 제약조건이랑 유니크 제약조건은 자동으로 인덱스가 생성됨.

1. SQL> ALTER TABLE emp
     2  ADD CONSTRAINT emp_mgr_fk
     3     FOREIGN KEY (mgr)
     4     REFERENCES EMP(empno);
2. Table altered.
3.  
4. SQL> SELECT CONSTRAINT_NAME
     2  FROM user_constraints
     3  WHERE table_name='EMP';
5. CONSTRAINT_NAME
   ------------------------------
   EMP_MGR_FK   // 새로 추가한 제약조건
   PK_EMP
   FK_DEPTNO
6.  
7. SQL>  SELECT CONSTRAINT_NAME, COLUMN_NAME
     2   FROM user_cons_columns
     3   WHERE TABLE_NAME = 'EMP';
8. CONSTRAINT_NAME
   ------------------------------
   COLUMN_NAME
   --------------------------------------------------------------------------------
9. FK_DEPTNO
   DEPTNO
10. PK_EMP
    EMPNO
11. EMP_MGR_FK
    MGR

 

 

Drop Constraints (P11-10)

• PK-FK 제약조건이 걸린 것은 그냥 삭제가 안되고, CASCADE를 통해서 삭제해야 된다.
• 이때 삭제된 PK에 해당되는 FK도 같이 삭제됨

1. // DEPT.DEPTNO는 그냥 삭제 안됨~
2. SQL> ALTER TABLE dept
     2  DROP PRIMARY KEY;
   ALTER TABLE dept
   *
   ERROR at line 1:
   ORA-02273: this unique/primary key is referenced by some foreign keys
3.  
4. // FK_DEPTNO -> DEPT.DEPTNO의 기본키를 참조하고 있음(삭제안되는 이유)
5. SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
     2  FROM user_constraints
     3  WHERE TABLE_NAME = 'EMP';
6.  
7. // P = 기본키 제약조건
8. // R = 외래키 제약조건
9. CONSTRAINT_NAME                C
   ------------------------------ -
   EMP_MGR_FK                     R
   PK_EMP                         P
   FK_DEPTNO                      R
10.  
11. // CASCADE를 이용해서 삭제함
12. SQL> ALTER TABLE dept
      2  DROP PRIMARY KEY CASCADE;
13. Table altered.
14.  
15. // FK_DEPTNO까지 같이 삭제됨
16. SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
      2  FROM user_constraints
      3  WHERE TABLE_NAME = 'EMP';
17. CONSTRAINT_NAME                C
    ------------------------------ -
    EMP_MGR_FK                     R
    PK_EMP                         P
18.  

 

  

Enable Constraints (P11-11)

• 테이블 복사시 많이 사용
• 기존 데이터 -> 새로 복사시 : 제약조건 확인하면서 복사함
• 기존 데이터는 정상적으로 작동되던 것이기 때문에 제약조건에 문제없음
• 잠시 Disable 시켜놓고 복사하면 제약조건 확인을 안하기 때문에 속도가 빠름
• Enable : Disable 되있는 제약조건을 다시 활성화 시킴 (데이터 상태가 만족되어야 다시 enable 가능 -> PK쪽을 disable 시킨후 데이터 추가후 다시 enable 시키려고 했는데 PK 조건이 안맞으면 에러~)

 

 P11-12 예제

1. // alter2 테이블 별다른 제약조건 없이 생성
2. SQL> CREATE TABLE alter2(a NUMBER, b CHAR, c DATE);
3. Table created.
4.  
5. // 데이터 2개 삽입 (a컬럼의 값이 중복되서 들어가는 것에 주의~)
6. SQL> INSERT INTO alter2 VALUES(10, 'x', sysdate);
7. 1 row created.
8. SQL> INSERT INTO alter2 VALUES(10, 'y', sysdate);
9. 1 row created.
10.  
11. // a컬럼에 PK제약조건을 추가하려고 했는데 현재 테이블내 데이터 중 a값이 중복되서 오류~(10이 중복~)
12. SQL> ALTER TABLE alter2 ADD CONSTRAINT alter2_a_pk PRIMARY KEY(a);
    ALTER TABLE alter2 ADD CONSTRAINT alter2_a_pk PRIMARY KEY(a)
                                      *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.ALTER2_A_PK) - primary key violated
13. 
    
14. // 중복된 것 확인~
15. SQL> SELECT * FROM alter2 ORDER BY a;
16.          A B C
    ---------- - --------
            10 x 08/11/04
            10 y 08/11/04
17.  
18. // 중복된 데이터 중 한개 제거
19. SQL> DELETE FROM alter2 WHERE a=10 AND b='y';
20. 1 row deleted.
21.  
22. // 중복데이터 제거후 PK 제약조건 추가~
23. SQL> ALTER TABLE alter2 ADD CONSTRAINT alter2_a_pk PRIMARY KEY (a);
24. Table altered.
25.  
26. // PK 제약조건이 걸려있으므로 중복데이터 추가 불가~
27. SQL> INSERT INTO alter2 VALUES(10, 'y' , sysdate);
    INSERT INTO alter2 VALUES(10, 'y' , sysdate)
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.ALTER2_A_PK) violated
28. 
    
29. // PK 제약조건 비활성화(Disable)
    SQL> ALTER TABLE alter2 DISABLE CONSTRAINT alter2_a_pk;
30. Table altered.
31.  
32. // PK 제약조건이 비활성화 되있으므로 중복데이터 추가 가능~
33. SQL> INSERT INTO alter2 VALUES(10, 'y', sysdate);
34. 1 row created.
35. SQL> /
36. 1 row created.
37.  
38. // 현제 테이블에 중복데이터가 있으므로 PK제약조건 활성화(Enable) 불가
39. SQL> ALTER TABLE alter2 ENABLE CONSTRAINT alter2_a_pk;
    ALTER TABLE alter2 ENABLE CONSTRAINT alter2_a_pk
    *
    ERROR at line 1:
    ORA-02437: cannot validate (SCOTT.ALTER2_A_PK) - primary key violated
40.  
41. // 중복데이터 다시 제거~
    SQL> DELETE FROM alter2 WHERE a=10 AND b='y';
42. 2 rows deleted.
43.  
44. // 중복데이터 제거후 제약조건 Enable 가능~
45. SQL> ALTER TABLE alter2 ENABLE CONSTRAINT alter2_a_pk;
46. Table altered.

 

 

Drop Table (P11-13)

• 다른 테이블과의 제약조건(PK-FK)이 있으면 삭제안됨 (다른 테이블에서 현재 삭제할 테이블의 컬럼을 참조하고 있는 경우)
• 'CASCADE CONSTRAINT' 사용해서 삭제해야함

1. // test00계정
2. SQL> DROP TABLE 교수;
   drop table 교수
              *
   ERROR at line 1:
   ORA-02449: unique/primary keys in table referenced by foreign keys
3. 
   SQL> DROP TABLE 교수 CASCADE CONSTRAINTS;
4. Table dropped.

 

 

Rename (P11-15)

• 테이블명 변경

1. SQL> RENAME emp to employees;
2. Table renamed.
3.  
4. SQL> SELECT * FROM EMP;
   SELECT * FROM EMP
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist
5. 
   
6. // DDL 문이기 때문에 롤백이 안되므로 다시 바꾼것~
7. SQL> RENAME employees to emp;
8. Table renamed.

 

• 컬럼명 변경

1. SQL> ALTER TABLE emp
     2  RENAME COLUMN sal TO salary;
2. Table altered.
3.  
4. SQL> SELECT * FROM salary;
   SELECT * FROM salary
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist

 

 

Truncate (P11-17)

• DROP TABLE emp -> 데이터 모두 삭제, emp table 정의 삭제
• Delete from emp -> rollback 가능 -> rollback 정보 백업하느라 속도 느림
• Truncate table emp -> 자동커밍 -> rollback 가능 -> 속도빠름
• P11-18 예제

1. SQL> SELECT count(*) FROM alter2;
2.   COUNT(*)
   ----------
            1
3.  
4. SQL> DELETE FROM alter2;
5. 1 row deleted.
6. SQL> SELECT count(*) FROM alter2;
7.   COUNT(*)
   ----------
            0
8.  
9. SQL> ROLLBACK;
10. Rollback complete.
11.  
12. SQL> SELECT count(*) FROM alter2;
13.   COUNT(*)
    ----------
             1
14. SQL>
    SQL>
    SQL> TRUNCATE TABLE alter2;
15. Table truncated.
16.  
17. SQL> SELECT count(*) FROM alter2;
18.   COUNT(*)
    ----------
             0
19.  
20. SQL> ROLLBACK;
21. Rollback complete.
22.  
23. SQL> SELECT count(*) FROM alter2;
24.   COUNT(*)
    ----------
             0

 

 

Comment (P11-19)

• Comment를 달면 딕셔너리에서 확인가능~

1. SQL> comment on table emp is 'This table is for employee';
2. Comment created.
3.  
4. SQL> SELECT comments FROM user_tab_comments
     2  WHERE table_name = 'EMP';
5. COMMENTS
   -------------------------------------------
6. This table is for employee

 

 

• FORMAT

1. // JOB 컬럼을 30자로 출력~
2. SQL> COL JOB FORMAT a30
   SQL> select job from emp;
3. JOB
   ------------------------------
   CLERK
   SALESMAN
   SALESMAN
   MANAGER
   SALESMAN
   MANAGER
   MANAGER
   ANALYST
   PRESIDENT
   SALESMAN
   CLERK
4. JOB
   ------------------------------
   CLERK
   ANALYST
   CLERK
5. 14 rows selected.
6.  
7.  
8. // '0'갯수 기준으로 남은 공백을 0으로 채움
   SQL> COL sal FORMAT 000000
   SQL> SELECT sal FROM emp;
9.     SAL
   -------
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
    005000
10.     SAL
    -------
     005000
     005000
     005000
11. 14 rows selected.
12.  
13.  
14. // '9' 갯수 기준으로 남은 것을 공백으로 채움
15. SQL> COL sal FORMAT 999999
    SQL> SELECT sal FROM emp;
16.     SAL
    -------
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
       5000
17.     SAL
    -------
       5000
       5000
       5000
18. 14 rows selected.
19.  
20. // '$' 랑 ',' 형식 맞춰서~
21. SQL> COL sal FORMAT $99,999,999
    SQL> /
22.          SAL
    ------------
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
          $5,000
23.          SAL
    ------------
          $5,000
          $5,000
          $5,000
24. 14 rows selected.
25.  

 

이 글은 스프링노트에서 작성되었습니다.