[Oracle/기본]11. 제약조건 포함

● 제약조건

 

   오라클 서버는 부적합한 데이터가 테이블에 삽입되는 것을 방지하기 위해 CONSTRAINT(제약조건)를 사용한다.

 

* 데이터 무결성 제약조건

- NOT NULL : 이열은 NULL값을 포함하지 않음을 지정한다.

- UNIQUE Key : 테이블의 모든 행에 대해 유일해야 하는 값을 가진 열 또는 열의 조합을 지정한다.

- PRIMARY KEY : 유일하게 테이블의 각 행을 식별한다.

- FOREIGN KEY : 열과 참조된 테이블의 열 사이의 외래키 관계를 적용하고 설정한다.

- CHECK : 참이어야 하는 조건을 지정한다.

 

1. NOT NULL 제약조건

   열 레벨에서 정의된다.

   해당 열의 데이터는 NULL값을 가질 수 없다.   

 

   CREATE TABLE emp(empno NUMBER(4)

                             ,ename VARCHAR2(10) NOT NULL

                             ,job      VARCHAR2(9)

                             ,mgr     NUMBER(4) NOT NULL);

 

2. UNIQUE Key 제약조건

   테이블 또는 열 레벨에서 정의된다.

   지정된 열 또는 열의집합에서 중복 값을 가지는 테이블의 두 행은 없다.

   UNIQUE Key가 하나 이상의 열을 포함하다면, 열 그룹은 composite unique Key라고 부른다.

   UNIQUE Key 제약조건은 동일 열에 대해 NOT NULL 제약조건을 정의하지 않는다면 NULL의 출력을 허용하고,

   이 때 NULL은 항상 UNIQUE Key 제약조건을 만족한다.

 

   CREATE TABLE dept(deptno         NUMBER(2)

                             ,dname          VARCHAR2(14)

                             ,loc               VARCHAR2(13)

                             ,CONSTRAINT dept_dname_uk UNIQUE(dname));

 

UNIQUE Key는 그 테이블에서 중복되는 행이 없는 고유한 값을 가진 열이다.

여러 열이 있을 수 있으며, 이 경우에는 UNIQUE INDEX라 한다.

UNIQUE Key는 PRIMARY Key를 포함한다.

 

3. PRIMARY Key 제약조건

   테이블 레벨 또는 열 레벨에서 정의된다.

   테이블에서 각 행을 유일하게 식별하는 열 또는 열의 집합이다.

   UNIQUE INDEX는 자동으로 PARIMARY Key열에 대해 생성된다.

 

   CREATE TABLE dept(deptno NUMBER(2)

                             ,dname VARCHAR2(14)

                             ,loc VARCHAR2(13)

                             ,CONSTRAINT dept_deptno_pk PRIMARY Key(deptno);

 

PRIMARY Key는 외부 FOREIGN key와 반드시 이어진다.

하나의 열만 해당된다.

 

4. FOREIGN Key 제약조건

 

   1.반드시 자식 테이블에서 생성

   2.부모테이블의 고유 키 또는 기본 키 참조

 

   테이블 레벨 또는 열 레벨에서 정의된다.

   외래 키 값은 부모 테이블에서 존재하는 값과 일치해야 하거나 NULL이 되어야 한다.

 

   CONSTRAINT 외래키명 FOREIGN KEY(자식테이블컬럼)

    REFERENCES 부모테이블명(부모테이블고유컬럼);

 

   CREATE TABLE emp(sal NUMBER(7,2)

                             ,comm NUMBER(7,2)

                             , ...

                             ,CONSTRAINT emp_deptno_fk FOREIGN Key (deptno)

                               REFERENCES dept (deptno));

   ☞ FOREIGN Key

       테이블 제약조건 레벨에서 자식 테이블 열을 정의한다.

   ☞ REFERENCES

       부모 테이블에서 테이블과 열을 식별한다.

   ☞ ON DELETE CASCADE

       자식 테이블에서 행의 종속을 삭제하고 부모 테이블에서 삭제를 허용한다.

       ON DELETE CASCADE 옵션이 없다면, 부모 테이블에 있는 행을 자식 테이블에서 참조할 경우 삭제될 수 없다. 

      

       Ex. CONSTRAINT emp2_fk FOREIGN Key(deptno)

            REFERENCES 테이블명(컬럼명) ON DELETE CASCADE;

 

5. CHECK 제약조건

   각 행을 만족해야 하는 조건을 정의한다.

 

   ..., deptno NUMBER(2),

        CONSTRAINT emp_deptno_ck

                CHECK (deptno BETWEEN 10 AND 99), ...          

   

       ☞ WHERE 문과 비슷한 역할이다.    

 

● 제약조건 생성, 삭제

    제약조건의 수정은 불가능하다. (삭제 후, 생성해야 함.)

 

- 제약조건 생성

 

          ALTER TABLE emp

  ADD CONSTRAINT emp_mgr_fk

         FOREIGN Key (mgr) REFERENCES emp(empno); 

 

- 제약조건 삭제

 

  ☞ EMP테이블에서 관리자 제약조건을 제거한다.

               ALTER TABLE emp

      DROP CONSTRAINT emp_mgr_fk;

 

   ☞ DEPT 테이블에서 PRIMARY Key 제약조건을 제거하고 EMP.DEPTNO열에서 관련 FOREIGN Key제약조건을 삭제한다.

                ALTER TABLE dept

       DROP PRIMARY Key CASCADE;

 

       DROP의 CASCADE 옵션은 모든 종속적인 제약조건이 삭제되게 한다.

 

● 제약조건 사용불가

    DISABLE 절을 가진 ALTER TABLE 문장을 사용하여 삭제 또는 재생성 없이 제약조건을 비활성화할 수 있다.

 

                ALTER TABLE emp

    DISABLE CONSTRAINT emp_empno_pk CASCADE; 

 

    CREATE TABLE 문장과 ALTER TABLE 문장으로 DISABLE 절을 사용할 수 있다.

    CASCADE절은 종속적인 무결성 제약조건을 비활성화한다.

 

● 제약조건 사용가능

    ENABLE절을 가진 ALTER TABLE 문장을 사용하여 삭제 또는 재생성 없이 제약조건을 활성화할 수 있다.

 

               ALTER TABLE emp

    ENABLE CONSTRAINT emp_empno_pk;

 

    CREATE TABLE 문장과 ALTER TABLE 문장으로 ENABLE 절을 사용할 수 있다.

    UNIQUE Key 또는 PRIMARY Key 제약조건이 활성화 된다면, UNIQUE 또는 PRIMARY Key 인덱스는 자동으로 생성된다.

 

● 제약조건 보기

 

    SELECT *

      FROM user_constraints

    WHERE table_name = 'EMP';

 

● 제약조건과 연관된 열 보기

 

    SELECT *

      FROM user_cons_columns

     WHERE table_name = 'EMP';