오라클은 종속된 테이블의 삭제를 방지하고 테이블에 유효하지 않은 데이타가 입력되는 것을 방지하기 위하여 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
'Programming > DB매니아' 카테고리의 다른 글
| MySQL 예약어 (0) | 2008.02.26 |
|---|