[SQL] 오라클의 Constraint(제약조건)

오라클은 종속된 테이블의 삭제를 방지하고 테이블에 유효하지 않은 데이타가 입력되는 것을 방지하기 위하여 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