|
오라클은 종속된
테이블의 삭제를 방지하고 테이블에 유효하지 않은 데이타가 입력되는 것을 방지하기
위하여 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
* iWiz님에 의해서 게시물 이동되었습니다 (2010-02-03 17:06)
|
