7. 제약조건 ★★★

[기타SQL]/oracle SQL

7. 제약조건 ★★★

JJ* 2014. 8. 28. 22:10

7장. 제약조건 ★★★

* 제약조건이란 : 테이블에 올바른 데이터만 입력받고 잘못된 데이터는 들어오지 못하도록 칼럼마다 정하는 규칙.

1. 제약조건의 종류★★

조건 이름	의 미
NOT NULL	이 조건 설정된 칼럼에는 NULL값이 입력되지 못함.
UNIQUE	이 조건 설정된 칼럼에는 중복된 값이 입력되지 못함.
PRIMARY KEY	이 조건은 NOT NULL + UNIQUE의 의미 가지며, 테이블 내에서 데이터들 끼리의 유일성을 보장하는 칼럼에 설정할 수 있으며, 테이블 당 1개만 설정할 수 있다.
FOREIGN KEY	이 조건은 다른 테이블의 칼럼을 참조해서 무결성 검사를 한다.
CHECK	이 조건으로 설정된 값만 입력을 허용하고 나머지는 거부된다.

* primary key를 제외한 나머지 제약조건들은 하나의 제약조건이 여러 칼럼에 중복으로 설정될 수 있으며, 또 하나의 칼럼에 제약조건들이 중복으로 설정 될 수 있다.

* UNIQUE와 Primary key 제약조건이 설정되면 오라클이 자동으로 해당칼럼에 UNIQUE INDEX를 생성해 관리하므로 데이터가 서로 중복되지 않음.

* FOREIGN KEY는 두 개의 테이블에 동시에 설정된다.

reference key(참조키)	부모 테이블 쪽에 참조되는 칼럼에 설정되는 제약조건.
foreign key(외래키)	자식 테이블 쪽 칼럼에 설정되는 제약조건.

- 만약 자식테이블에 데이터가 입력될 때 → 자식테이블에 데이터가 입력되기 전에 사용자가 입력한 값이 부모테이블에 있는지를 먼저 조사한 후 해당데이터가 있을 경우 입력이 허가되는 형태이다. 따라서, 부모테이블에 데이터가 10억건이 있으면 자식테이블에 데이터를 1건 입력하기 위해 부모테이블에 잇는 10억건의 데이터를 전부 읽어서 검사를 한 후에 작업을 할 수 있게되므로 비효율적이다.

- 반대로, 부모테이블의 데이터를 변경하거나 삭제할 때 → 부모테이블의 데이터를 변경하거나 삭제하기 전에 자식테이블에가서 해당 데이터가 있는지 없는지 찾아본 후 자식테이블에 해당 데이터가 없으면 데이터를 변경하게 된다. 따라서, 자식테이블에 데이터가 10억건 있으면 부모테이블에 데이터 1건 변경하려고 자식 테이블의 데이터 10억건을 모두 읽어야 하는 비 효율적인 상황이 생긴다.

2. 각 제약 조건의 설정 방법
1) 테이블 생성시 제약조건을 동시에 설정하기

[각 제약 조건의 이름을 직접 지정]

: 제약조건을 비활성화 하거나 활성화 하거나 삭제하는 등의 관리 작업시에 해당 제약조건의 이름을 알고 있어야 하므로, 제약조건의 이름을 직접 지정하는 것을 권장함.(제약조건들은 딕셔너리에 저장되어 있음.)

SCOTT> CREATE TABLE emp3
   2 ( no NUMBER(4)
   3         CONSTRAINT emp3_no_pk   PRIMARY KEY ,
   4    name VARCHAR2(10)
   5         CONSTRAINT    emp3_name_nn   NOT NULL,
   6    jumin VARCHAR2(13)
   7         CONSTRAINT    emp3_jumin_nn   NOT NULL   → 하나의 칼럼에 2개 이상의 제약조건 설정(7~8번줄)
   8       CONSTRAINT    emp3_jumin_uk   UNIQUE ,
   9    area NUMBER(1)
10        CONSTRAINT    emp3_area_ck   CHECK ( area < 5 ),
11    deptno VARCHAR2(6)
12        CONSTRAINT emp3_deptno_fk REFERENCES   dept2(dcode)  → deptno값은 dept2테이블의 dcode값을

13 ) ; 참조한다는 참조키 제약조건설정

[각 제약 조건의 이름을 지정하지 않음.]

SCOTT> CREATE    TABLE    emp4
   2 (    no   NUMBER(4) PRIMARY KEY ,
   3    name   VARCHAR2(10) NOT NULL,
   4    jumin VARCHAR2(13)   NOT NULL UNIQUE ,
   5    area NUMBER(1) CHECK ( area < 5 ),             → 범위줄때 check옵션 / 콤마(,)는 다른테이블일때만 쓴다.
   6    deptno VARCHAR2(6) REFERENCES   dept2(dcode)
   7 ) ;

: foreign key라고 따로 지정해주지않고, "references 부모테이블(참조할칼럼)" 을 쓴게 foreign key이다.

2) 테이블 생성 후 제약조건 추가하기

* UNIQUE 제약조건 추가

ALTER TABLE 테이블명

ADD CONSTRAINT 제약조건명 UNIQUE(칼럼명);

* NULL ↔ NOT NULL 변경

ALTER TABLE 테이블명

MODIFY (컬럼명 constraint 제약조건명 NOT NULL 또는 NULL);

* 참조키(FOREIN KEY) 제약조건 설정

ALTER TABLE 자식테이블명

ADD CONSTRAINT 제약조건명 FOREIGN KEY(자식테이블의컬럼)

REFERENCES 부모테이블명(부모테이블컬럼) ; → 부모테이블컬럼이 primary key나 unique key가 설정되어 있어야 한다.

→ 주의사항 : 부모 테이블 쪽에 설정되는 컬럼이 Primary Key 이거나 Unique Key가 설정되어 있어야 한다. 그렇지 않을 경우 에러발생하므로 부모테이블에 unique key나 primary key를 설정해준 후 다시 참조키 제약조건을 설정해주면 된다.

* FOREIN KEY생성시 옵션 (제일아래에 써주면 됨) (FOREIGN KEY 를 설정 후 부모테이블의 데이터는 지울 수 없으므로)

ON DELETE CASCADE

부모 테이블의 데이터가 지워지면 자식 테이블의 데이터도 함께 지움.

ON DELETE SET NULL

부모테이블의 데이터가 지워질 경우 자식테이블의 값을 NULL 로 설정.

(만약 자식테이블에 not null조건이 걸려있을 경우, 부모테이블의 데이터도 지워지지 않는다.)

ex)

위) emp2 (부모)테이블의 name 컬럼에 UNIQUE KEY 제약조건이 없는 상태에서 참조키를 설정하려다가 에러가 남.

아래) emp2 테이블의 name 컬럼에 UNIQUE KEY 를 설정한 후 다시 시도하니까 정상적으로 추가 됨.

Step 1. 연습용 테이블을 생성합니다.

SCOTT>CREATE    TABLE    c_test1 (
   2 no      NUMBER ,
   3 name   VARCHAR2(6) ,
   4 deptno NUMBER );

Table created.

SCOTT>CREATE    TABLE    c_test2 (
   2 no      NUMBER ,
   3 name   VARCHAR2(10));

Table created.

Step 2. 두 개의 테이블에 제약조건을 설정하고 데이터를 입력합니다.

SCOTT>ALTER    TABLE    c_test1 →자식
   2 ADD   CONSTRAINT    ctest1_deptno_fk    FOREIGN   KEY(deptno)
   3 REFERENCES    c_test2(no); →부모

REFERENCES    c_test2(no)
                    *
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list →부모테이블에 unique나 primary key설정 안돼있음.

SCOTT>ALTER    TABLE    c_test2     → 부모테이블에 unique 제약조건 설정
   2 ADD   CONSTRAINT    ctest2_no_uk   UNIQUE(no);

Table altered.

SCOTT>ALTER    TABLE    c_test1
   2 ADD   CONSTRAINT    ctest1_deptno_fk    FOREIGN   KEY(deptno)
   3 REFERENCES    c_test2(no)
   4   ON   DELETE   CASCADE ;   → 참조키제약조건 설정시 옵션줌.

Table altered.

SCOTT>INSERT    INTO    c_test2   VALUES (10,'AAAA');   → 부모테이블에 값입력
1 row created.

SCOTT> INSERT    INTO    c_test2   VALUES (20,'BBBB');
1 row created.

SCOTT> INSERT    INTO    c_test2   VALUES (30,'CCCC');
1 row created.

SCOTT>COMMIT ;
Commit complete.

SCOTT>SELECT    *    FROM    c_test2 ;
        NO     NAME
   ---------- ----------
         10    AAAA
         20     BBBB
         30    CCCC

SCOTT>INSERT    INTO    c_test1   VALUES (1,'apple',10); → 자식테이블에도 값 입력
1 row created.

SCOTT> INSERT    INTO    c_test1   VALUES (2,'banana',20);
1 row created.

SCOTT> INSERT    INTO    c_test1   VALUES (3,'cherry',30);

1 row created.

Step 3. on delete cascade 테스트를 수행합니다.

SCOTT> INSERT INTO c_test1 VALUES (4,'peach',40); → 부모테이블의 deptno가 10,20,30까지밖에없으므로

insert into c_test1 values (4,'peach',40)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.CTEST1_DEPTNO_FK) violated - parent key
not found

SCOTT>SELECT    *    FROM    c_test1;

        NO   NAME     DEPTNO
    ---------- ------------ ----------
          1     apple             10
          2    banana            20
          3     cherry             30

SCOTT>DELETE    FROM    c_test2   WHERE    no=10 ;  →부모테이블의 no=10 데이터를 삭제하니까 자식테이블의 no=10 데이터가 같이 삭제됨. (옵션때문에)
1 row deleted.

SCOTT>SELECT    *    FROM   c_test1;
        NO   NAME     DEPTNO
    ---------- ------------- ----------
          2    banana         20
          3     cherry         30
위 결과에서 1 번 항목이 지워진 것이 확인됩니다.

Step 4. ON DELETE SET NULL 테스트를 수행합니다.

SCOTT>ALTER    TABLE c_test1 DROP   CONSTRAINT ctest1_deptno_fk; →부모테이블의 제약조건삭제

Table altered.

SCOTT>ALTER    TABLE    c_test1
   2 ADD   CONSTRAINT    ctest1_deptno_fk    FOREIGN   KEY(deptno)
   3 REFERENCES    c_test2(no)
   4 ON   DELETE    SET   NULL; → 참조키 제약조건 설정시 옵션주기

Table altered.

SCOTT>SELECT    *    FROM    c_test1;
        NO NAME     DEPTNO
    ---------- ----------- ----------
          2   banana        20
          3    cherry         30

SCOTT>SELECT    *    FROM   c_test2;
        NO   NAME
   ----------- ----------
         20   BBBB
         30   CCCC

SCOTT>DELETE  FROM c_test2   WHERE no=20 ; →부모테이블의 no=20 데이터 삭제하니 자식테이블의 no=20 데이터는 null값으로 바뀜. (옵션때문에)
1 row deleted.

SCOTT>SELECT    *    FROM   c_test1;

        NO NAME       DEPTNO
    -------------- ----------
        2    banana              <- 이 부분이 null 로 변경되었습니다.
        3     cherry          30

[ 만약 자식테이블의 deptno 컬럼에 not null 속성이 설정되어 있는 상태에서 Foreign Key가 on delete set null 로 생성될 경우]

Step 1. 자식테이블의 deptno 에 not null 속성을 설정합니다.

SCOTT>ALTER TABLE c_test1
   2 MODIFY (deptno CONSTRAINT ctest1_deptno_nn NOT NULL);
modify (deptno constraint ctest1_deptno_nn not null)
                            *
ERROR at line 2:                                                                        →기존에 null 값이 있으면 NOT NULL로 변경할 수 없다.
ORA-02296: cannot enable (SCOTT.CTEST1_DEPTNO_NN) - null values found

SCOTT>SELECT    *    FROM    c_test1;
       NO   NAME     DEPTNO
   ----- --------- ----------
           2   banana            <-null값
          3    cherry         30

SCOTT>UPDATE    c_test1    SET   deptno=30      → 자식테이블의 null값 없애기 위해 update
   2 WHERE   no = 2;
1 row updated.

SCOTT>COMMIT ;
Commit complete.

SCOTT>SELECT    *    FROM    c_test1;
      NO   NAME       DEPTNO
----------- -------------- ----------
          2   banana         30
          3    cherry          30

SCOTT>ALTER TABLE c_test1
   2 MODIFY (deptno CONSTRAINT    ctest1_deptno_nn   NOT   NULL ); →자식테이블의 컬럼에 not null설정
Table altered.

SCOTT>SELECT    *    FROM    c_test2;
       NO   NAME
   ----- ----------
        30   CCCC

SCOTT>DELETE    FROM    c_test2;   →부모테이블을 삭제하려니 자식테이블이 not null로 설정되어 있어서 null로 update할 수 없다고 에러남.(제약조건 설정시 "부모테이블을 삭제하면 자식테이블의 값을 null로 설정하도록" 옵션줘서)
DELETE    FROM    c_test2
             *
ERROR at line 1:
ORA-01407: cannot update ("SCOTT"."C_TEST1"."DEPTNO") to NULL

SCOTT>SELECT    *    FROM    c_test2;
       NO   NAME
------------ ----------
         30   CCCC

3. 제약조건 관리하기
테이블의 컬럼에 설정되는 각 제약조건들은 어떤 작업이나 필요에 의해 일시적으로 DISABLE / ENABLE 할 수 있다.

ex) 이미 검증된 대량의 데이터를 다른 테이블로 가져와서 입력할 경우 제약조건을 다시 검사할 필요가 없으므로, 제약조건을 임시로 dsable 해 둔 후 빠르게 데이터를 입력하고 입력이 완료되면 다시 enable 하는등의 작업을 하기도 한다.

1) 제약조건 DISABLE하기

NOVALIDATE (기본)

제약조건이 없는 것처럼 동일하게 작동.

ALTER TABLE 테이블명

DISABLE NOVALIDATE CONSTRAINT 제약조건명;

VALIDATE

해당 컬럼의 데이터를 변경할 수 없게 하는 옵션.

(insert, update, delete 작업을 수행할 수 없다.)

→ 다른컬럼에만 데이터를 입력하려고 해도 안됨.

→ 즉, 테이블의 내용을 변경하지 못하게 한다.

ALTER TABLE 테이블명

DISABLE VALIDATE CONSTRAINT 제약조건명;

→ primary key나 unique 제약조건을 disable하면 생성되어 있던 unique index가 자동으로 삭제된다. ★

ex1) novalidate 옵션

ex2) validate 옵션

2) 제약조건 ENABLE하기

NOVALIDATE	제약조건을 ENABLE하는 시점까지 해당 테이블에 들어 있는 데이터는 검사하지 않고, ENABLE한 시점 이후부터 새롭게 입력되는 데이터만 제약조건을 적용하여 검사하는 옵션.
	ALTER TABLE 테이블명 ENABLE NOVALIDATE CONSTRAINT 제약조건명;
VALIDATE(기본값)	제약조건을 ENABLE하는 시점까지 테이블에 입력되어 있던 모든 데이터를 전부 검사하여 신규로 입력되는 데이터도 전부 검사하는 옵션. → 기존 데이터도 전부 검사해야 하므로, 오라클이 해당 테이블에 데이터가 변경되지 못하도록 Lock을 설정하고 작업한다.(데이터를 많이 변경하는 시점에는 절대로 이 작업을 하면 안된다.) * 문제점 : 해당 제약조건을 위반하는 값이 발견되면 제약조건을 enable할 수 없는 위반사항이 있다는 메세지를 발생하면서 enable작업이 취소된다. 문제는 그 위반되는 데이터를 사람이 일일이 찾아 조치후 enable작업을 반복 시도해야 하므로 시간이 아주 오래걸린다. * 해결법: EXCEPTIONS라는 테이블을 사용해 에러사항을 별도로 기록하게 해야함. (약 10줄 아래 참고)
	ALTER TABLE 테이블명 ENABLE VALIDATE CONSTRAINT 제약조건명;

(실습 예) (자세한 실습은 pdf참조)

- NOT NULL 제약조건이 걸려있는 테이블에 null 데이터를 입력하기 위해, 해당 제약조건을 DISABLE로 변경후 입력함.

- enable novalidate 로 제약조건을 변경후 테이블을 조회해보니 잘못된데이터(null값)이 들어가 있다. (novalidate옵션은 새로입력되는 데이터만 제약조건을 적용해 검사하는 옵션이므로 기존 데이터는 검사하지 않아서)

- 테이블의 제약조건을 다시 disable로 변경후에 enable validate로 활성화시키려고 했더니, 잘못된 데이터(null값)이 들어가 있어서 enable 할 수 없다. (validate옵션은 기존데이터도 검사하므로)

3) EXCEPTIONS 테이블 생성해 enable validate시 에러사항 기록.(SYS계정)

step1. sys계정으로 exceptions table을 생성합니다.

sys> @?/rdbms/admin/utlexcpt.sql → ?는 $ORACLE_HOME 디렉토리를 의미

step2.

- 테스트용 테이블을 5보다 큰 값만 갖도록 제약조건을 가지도록 생성합니다.(5보다 작은 값 오면 null값임)

- 테스트용 테이블의 NOT NULL 속성을 disable 모드로 변경한 후, 데이터를 입력할때 5보다 작은 값을 입력하고 commit합니다.

step3. not null 제약조건을 enable 모드로 변경하면서 exceptions table을 사용하게 설정합니다.

ALTER TABLE 테이블명

ENABLE VALIDATE CONSTRAINT 제약조건명

EXCEPTIONS INTO sys.exceptions;

- 이렇게 하면, 아까 5보다 작은 값을 입력해놓은 것 때문에 null값이 들어가 있어 에러가 발생합니다.

step4. exceptions table 조회 (에러내역 확인)

SELECT rowid, 컬럼명

FROM 테이블명

WHERE rowid in (SELECT row_id FROM exceptions);

step5. no의 값이 null임이 확인되면 조회된 rowid를 where절에 이용해 문제가 되는 부분을 정상적인 값으로 업데이트한 후 commit합니다.

step6. 수정완료한 에러내역을 삭제해줍니다.

TRUNCATE TABLE sys.exceptions;

step7. 다시 not null 제약조건을 enable모드로 변경하면서 exceptions table을 사용하게 설정한 뒤 확인해봅니다.

4) EXCEPTIONS 테이블 생성해 enable validate시 에러사항 기록.(scott계정)

- scott사용자로 exceptions table을 생성합니다.

- 테스트용 테이블을 unique 제약조건을 걸면서 생성한 후, 제약조건에 위반되는 여러건의 데이터를 입력해주고 commit합니다.

- 제약조건을 enable하면 에러가 뜨고, exception table에서 에러 내용을 확인한 후, rowid를 이용해 원본 테이블을 update한 후 commit합니다.

- 다시 제약조건을 enable모드로 변경하면서 exception table을 사용하게 설정한 뒤 확인해봅니다.

5) 제약조건 조회하기.

* 테이블에 제약조건을 설정하면 그 내용이 딕셔너리에 저장되어 있다.

- 데이터베이스 전체의 제약조건 조회 : DBA_CONSTRAINTS , DBA_CONS_COLUMNS

테이블에 설정되어 있는 제약조건 조회 : USER_CONSTRAINTS

SCOTT>SELECT owner , constraint_name , constraint_type , status
2 FROM user_constraints
3 WHERE table_name='테이블명대문자';

특정 테이블의 특정 컬럼에 설정되어 있는 모든 제약조건 조회 : USER_CONS_COLUMNS

SCOTT>select owner , constraint_name , table_name , column_name
2 from user_cons_columns
3 where table_name='테이블명대문자';

FOREIGN KEY 조회

SCOTT> SELECT a.table_name    "Child_Table",
   2               c.column_name    "Child_Column",
   3              a.constraint_name    "Child_Cons_name",
   4             b.table_name    "Parent_Table" ,
   5              a.r_constraint_name    "Parent_Cons_name" ,
   6             d.column_name    "Parent_Column"
   7   FROM   user_constraints    a , user_constraints    b , user_cons_columns    c,
   8             (SELECT constraint_name, column_name, table_name
   9              FROM user_cons_columns) d
10 WHERE    a.r_constraint_name=b.constraint_name
11 AND       a.constraint_name=c.constraint_name
12   AND     a.r_constraint_name=d.constraint_name
13   AND       a.constraint_type='R' ;

Child_Table    Child_Column  Child_Cons_name   Parent_Table   Parent_Cons_nam Parent_Column
--------------- ----------- ------------------ -------------- ------------------ ---------------
    EMP             DEPTNO       FK_DEPTNO             DEPT             PK_DEPT              DEPTNO
    EMP4            DEPTNO      SYS_C0014411          DEPT2           SYS_C0014275        DCODE
    EMP3            DEPTNO      EMP3_DEPTNO_FK    DEPT2          SYS_C0014275        DCODE
    EMP4            NO              EMP4_NO_FK            EMP2           SYS_C0014273         EMPNO
    EMP8            NO            EMP8_NO_FK         EMP2           SYS_C0014273        EMPNO
    EMP4            NAME         EMP4_NAME_FK        EMP2           EMP2_NAME_UK     NAME

6) 제약조건 삭제

ALTER TABLE 테이블명

DROP CONSTRAINT 제약조건명;

저작자표시 변경금지