728x90

오라클은 종속된 테이블의 삭제를 방지하고 테이블에 유효하지 않은 데이타가 입력되는 것을 방지하기 위하여 constraint를 사용합니다.


1. constraint 지침

    1) 제약조건에 이름을 지정하지 않으면 Oracle server가 SYS_Cn의 형식으로 자동으로 이름을 생성합니다.

    2) 제약조건은 크게 테이블 레벨과 열 레벨로 정의할 수 있습니다.


2. constraint 정의 예제

    SQL> CREATE TABLE EXAMPLE(

                ID               NUMBER(6),

                NAME         VARCHAR2(20)    [CONSTRAINT NAME_CTR] NOT NULL,    

                                                                     ----> 열 레벨(제약조건 이름을 생략하면 시스템이 자동으로 이름 생성

                ...

               

                CONSTRAINT EXAMPLE_ID_PK PRIMARY KEY(ID));                          

                                                                     ----> 테이블 레벨(제약조건 이름이 EXAMPLE_ID_PK로 생성)


3. constraint 유형

    1) NOT NULL : 해당열에 널값이 없도록 하기 위한 제약조건

    2) UNIQUE : 지정된 열에 대해 널값은 허용하나, 동일한 값은 허용되지 않는 제약조건

                       오라클 서버는 지정된 열에 대해 자동으로 인덱스를 생성

                       [예제] SQL> ...CONSTRAINT EXAMPLE_ID_UK UNIQUE(ID)

    3) PRIMARY KEY : 테이블의 각 행을 식별하기 위해 테이블당 하나의 기본키를 생성

                                중복된 값과 널값을 가질 수 없다

                                오라클 서버는 지정된 열에 대해 자동으로 인덱스를 생성

    4) FOREIGN KEY : 동일한 테이블 또는 다른 테이블에서 기본키 또는 고유키를 참조하는 제약조건

                                부모 테이블의 값과 일치하거나 널값을 가져야 한다.

                                ON DELETE CASCADE : 부모 테이블의 행이 삭제되는 경우 자식 테이블의 종속 행을 삭제

                                ON DELETE SET NULL : 부모 테이블의 행이 삭제되는 경우 종속 외래키 값을 널로 변환

                                [예제] ...CONSTRAINT EXAMPLE_ID_FK FOREIGN KEY(ID) REFERENCES DEPT(ID)

    5) CHECK : 각 행이 만족시켜야 하는 조건을 정의

                      [예제] SAL       NUMBER(10)    CONSTRAINT EXAMPLE_SAL_CK

                                                                      CHECK(SAL > 1000)


4. constraint를 조회하는 데이타 딕셔너리

    1) USER_CONSTRAINTS

        테이블 생성 후 DESC로 테이블 구조를 보면 NOT NULL제약조건만 보이고, 다른 제약 조건은 보이지 않게 된다.

        이때 다른 제약조건을 보기 위해 USER_CONSTRAINTS라는 데이타 딕셔너리가 제공된다.

        여러 필드중 CONSTRAINT_TYPE이라는 필드만 살펴보도록 하겠다. 나머지는 쉽게 이해가 될 것이다.

        P : PRIMARY KEY, R : FOREIGN KEY, C : CHECK 또는 NOT NULL, U : UNIQUE

    2) USER_CONS_COLUMNS

        제약 조건 이름과 연관된 컬럼을 볼 수 있게 하는 데이타 딕셔너리


5.  constraint 추가 / 삭제

    1) constraint 추가

        EMP_TEST테이블의 DEPT_ID필드가  DETP_TEST의 ID값을 참조하는 제약조건을 추가하는 예문

        SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                 TABLE_NAME                     CONSTRAINT_NAME

                 ------------------------------ -------------

                 EMP_TEST                         EMP_TEST_PK

 

        1) DETP_TEST테이블의 ID필드를 PRIMARY KEY값으로 설정

 

 

           SQL> ALTER TABLE DETP_TEST

                    ADD CONSTRAINT DETP_TEST_PK PRIMARY KEY(ID);

                    Table altered.

        2) EMP_TEST테이블의 DEPT_ID필드를 DETP_TEST의 ID값을 참조하는 FOREIGN KEY값으로 설정

                    ALTER TABLE EMP_TEST

                    ADD CONSTRAINT EMP_TEST_FK FOREIGN KEY(DEPT_ID) REFERENCES DETP_TEST(ID);

                    Table altered.

        3) constraint 추가 여부 확인

            SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                     WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                     TABLE_NAME                     CONSTRAINT_NAME

                     ------------------------------ ------------------------------

                     DETP_TEST                      DETP_TEST_PK

                     EMP_TEST                        EMP_TEST_FK

                     EMP_TEST                        EMP_TEST_PK

 

    2) constraint 삭제

        EMP_TEST테이블의 DEPT_ID(FOREIGN KEY)필드의 제약조건을 삭제

        SQL> ALTER TABLE EMP_TEST

                 DROP CONSTRAINT EMP_TEST_FK;

                 Table altered.

       

        SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                 TABLE_NAME                     CONSTRAINT_NAME

                 ------------------------------ ------------------------------

                 DETP_TEST                       DETP_TEST_PK

                 EMP_TEST                        EMP_TEST_PK

 

        DETP_TEST테이블의 ID필드의 제약조건(PRIMARY KEY)을 삭제

        SQL> ALTER TABLE DETP_TEST

                 DROP PRIMARY KEY CASCADE;     ----> CASCADE를 붙이면 FOREIGN KEY까지 동시에 삭제가 됨

                 Table altered.


        SQL> SELECT TABLE_NAME, CONSTRAINT_NAME FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                 TABLE_NAME                     CONSTRAINT_NAME

                 ------------------------------ ------------------------------

                 EMP_TEST                         EMP_TEST_PK

 

6. constraint의 enable/disable : 제약조건을 삭제/생성하지 않고도 제약조건을 비활성화 할 수 있다.

    1) constraint의 disable

        DETP_TEST테이블의 ID필드의 제약조건(PRIMARY KEY)을 disable

        SQL> ALTER TABLE DETP_TEST

                 DISABLE CONSTRAINT DETP_TEST CASCADE; ---> CASCADE는 FOREIGN KEY까지 동시에 disable됨

                 Table altered.


        SQL> SELECT TABLE_NAME, STATUS FROM USER_CONSTRAINTS

                 WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                 TABLE_NAME                     STATUS

                 ------------------------------ --------

                 DETP_TEST                      DISABLED

                 EMP_TEST                       DISABLED

                 EMP_TEST                       ENABLED


    2) constraint의 enable

        disable된 제약조건을 enable시키기 위해서는 PRIMARY KEY를 enable시킨 후, FOREIGN KEY를 enable 시켜

        야 한다.l

        SQL> ALTER TABLE DETP_TEST

                 ENABLE CONSTRAINT DETP_TEST;

                 Table altered.

        SQL> ALTER TABLE EMP_TEST

                  ENABLE CONSTRAINT EMP_TEST_FK;

                  Table altered.

        SQL> SELECT TABLE_NAME, STATUS FROM USER_CONSTRAINTS

                  WHERE TABLE_NAME IN('EMP_TEST','DETP_TEST');

                  TABLE_NAME                     STATUS  

                  ------------------------------ --------

                  DETP_TEST                      ENABLED

                  EMP_TEST                       ENABLED

                  EMP_TEST                       ENABLED

728x90

'Programming > DB매니아' 카테고리의 다른 글

MySQL 예약어  (0) 2008.02.26

+ Recent posts