Constraint (제약 조건)
: 테이블에 올바른 데이터만 입력받고 잘못된 데이터는 들어 오지 못하도록 컬럼마다 정하는 규칙
종류
| 조건 이름 | 의미 |
| NOT NULL | 이 조건이 설정된 컬럼에는 NULL 값이 입력되지 못하도록 합니다. |
| UNIQUE | 이 조건이 설정된 컬럼에는 중복된 값이 입력되지 못하도록 합니다. |
| PRIMARY KEY | 이 조건은 NOT NULL + UNIQUE의 특징을 가지며, 테이블 내에서 데이터들끼리의 유일성을 보장하는 컬럼에 설정합니다. 그리고 테이블당 1개만 설정할 수 있습니다. |
| FOREIGN KEY | 이 조건은 다른 테이블의 컬럼을 참조해서 검사를 합니다. |
| CHECK | 이 조건에서 설정된 값만 입력을 허용하고 나머지는 거부됩니다. |
FOREIGN KEY
: 부모 테이블(Parent Table) - 정보를 제공해 주는 쪽
: 자식 테이블(Child Table) - 정보를 받는 쪽
: 기억해야할 사항
자식 테이블에 데이터가 입력되기 전에 사용자가 입력한 값이 부모 테이블에 있는지 먼저 조사한 후
해당 데이터가 있을 경우 입력이 허가되는 형태입니다.
부모 테이블에 있는 데이터를 변경하거나 삭제하려면 해당 부모 테이블의 데이터를 참조하는
자식 테이블의 데이터가 없어야 합니다.
참조키 제약 조건을 설정할 때는 자식 테이블과 부모 테이블의 해당 컬럼에 반드시 적절한 인덱스를 생성해 주어야 성능이 좋아집니다.
제약 조건 사용하기
: 테이블 생성 시에 지정하기1 (제약 조건 이름을 직접 지정)
: 테이블 생성 시에 지정하기2 (제약 조건 이름을 지정 안함)
: 테이블 생성 후 제약조건 추가하기
ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(컬럼명);
ex) ALTER TABLE new_table ADD CONSTRAINT name_uk UNIQUE(name);- NOT NULL 제약조건 추가는 기본값으로 허용되어있는 NULL을 NOT NULL로 변경하는 것이므로
ADD가 아니라 MODIFY로 변경(수정)해야 한다.
ALTER TABLE 테이블명 MODIFY (컬럼명 CONSTRAINT 제약조건명 NOT NULL);
ex) ALTER TABLE new_table MODIFY (name CONSTAINT name_nn NOT NULL); - 참조키(FOREIGN KEY) 설정
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 FOREIGN KEY(컬럼명)
REFERENCES 참조할테이블명(참조할컬럼명);
ex) ALTER TABLE new_table
ADD CONSTRAINT no_fk FOREIGN KEY(no)
REFERENCES old_table(oldno);: 주의사항
참조키 제약 조건을 설정할 때 부모 테이블 쪽에 설정되는 컬럼이 PRIMARY KEY이거나 UNIQUE가 설정되어 있어야 합니다. 그렇지 않을 경우 에러가 발생합니다.
CASECADE 옵션
: ON DELETE CASCADE - 부모 테이블의 데이터가 지워지면 자식 테이블의 데이터도 지우라는 의미
: ON DELETE SET NULL - 부모 테이블의 데이터가 지워질 경우 자식 테이블의 데이터 값을 NULL로 설정하라는 뜻
: 연습용 테이블 생성
ON DELETE CASCADE
: 부모 테이블인 c_test2에 먼저 INSERT
: 자식 테이블인 c_test1에 INSERT
: ON DELETE CASCADE 테스트 수행하기 위해 부모테이블에서 값을 삭제
: 자식 테이블이 참조하고 있는 부모테이블의 값을 삭제 하면 참조하고 있던 자식 테이블도 삭제됨
제약 조건 삭제
ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
ON DELETE SET NULL
: 현재 테이블
: ON DELETE SET NULL 테스트 수행하기 위해 부모테이블에서 값을 삭제
: 부모 테이블의 값을 삭제하면 참조하고 있던 자식 테이블의 값은 NULL로 바뀜
: 자식 테이블에 부모테이블을 참조하는 컬럼이 NOT NULL이 설정되면 오류가 발생함
(t_novalidate 테이블)
CREATE TABLE t_novalidate
( no number primary key ,
name varchar2(10) not null );
INSERT INTO t_novalidate VALUES(1,'AAA');
INSERT INTO t_novalidate VALUES(2,'BBB');
INSERT INTO t_novalidate VALUES(3,'CCC');
제약 조건 DISABLE 하기
: DISABLE NOVALIDATE 사용하기
- DISABLE NOVALIDATE로 제약조건을 DISABLE하게 되면 CONSTRAINT가 없는 것과 동일하게 작동
(t_validate)
CREATE TABLE t_validate
( no number
CONSTRAINT tv_no_pk PRIMARY KEY ,
name varchar2(10)
CONSTRAINT tv_name_nn NOT NULL) ;
INSERT INTO t_validate VALUES (1,'AAA');
INSERT INTO t_validate VALUES (2,'BBB');
INSERT INTO t_validate VALUES (3,'CCC');
CREATE TABLE t_validate
( no number
CONSTRAINT tv_no_pk PRIMARY KEY ,
name varchar2(10)
CONSTRAINT tv_name_nn NOT NULL) ;
INSERT INTO t_validate VALUES (1,'AAA');
INSERT INTO t_validate VALUES (2,'BBB');
INSERT INTO t_validate VALUES (3,'CCC');
: DISABLE VALIDATE 사용
- 해당 컬럼의 데이터를 변경할 수 없게 하는 옵션입니다.
: DISABLE VALIDATE 옵션 설정 후 다른 컬럼 내용 변경하기
DISABLE의 기본 옵션은 NOVALIDATE 입니다.
그리고 PRIMARY KEY나 UNIQUE 제약 조건을 DISABLE할 경우 생성되어 있던
UNIQUE INDEX가 자동으로 삭제됩니다.
ENABLE 종류
: ENABLE NOVALIDATE
- 제약 조건을 ENABLE하는 시점까지 해당 테이블에 들어 있는 데이터는 검사하지 않고
ENABLE한 시점부터 새롭게 테이블로 입력되는 데이터만 제약 조건을 적용하여 검사하는 옵션
: ENABLE VALIDATE
- 제약 조건을 ENABLE하는 시점까지 테이블에 입력되어 있던 모든 데이터를 전부 검사하며
신규로 입력되는 데이터도 전부 검사하는 옵션
- 오라클이 해당 테이블에 데이터가 변경되지 못하도록 LOCK이란 것을 설정하고 작업을 하게 되는데,
이런 특성 때문에 사용자들의 테이블의 데이터를 많이 변경하는 시점에는 절대로 이 작업을 하면 안됩니다.
- 만약 검사를 하다가 해당 제약 조건을 위반하는 값이 발견되면 "제약조건을 ENABLE할 수 없는 위반사항이 있다"
라는 메세지가 발생하면서 제약 조건 ENABLE을 취소한다.
- 이런 문제를 해결하기 위해 ENABLE VALIDATE일 경우에는
꼭 EXCEPTIONS라는 테이블을 사용해서 에러사항을 별도로 기록하게 만들어야 합니다.
(t_enable)
CREATE TABLE t_enable
( no number
CONSTRAINT te_no_pk PRIMARY KEY ,
name varchar2(10)
CONSTRAINT te_name_nn NOT NULL) ;
CREATE TABLE t_enable
( no number
CONSTRAINT te_no_pk PRIMARY KEY ,
name varchar2(10)
CONSTRAINT te_name_nn NOT NULL) ;
ENABLE 테스트
: NOT NULL 제약 조건이 걸려서 입력이 안됨을 확인
: 제약 조건을 DISABLE로 변경
: NULL 값도 입력 되는 것을 확인
ENABLE NOVALIDATE로 name 컬럼 제약 조건 ENABLE 하기
ENABLE VALIDATE로 name 컬럼 제약 조건 ENABLE하기
EXCEPTIONS 테이블을 사용하여 ENABLE VALIDATE하기 - SYS 계정 사용
: SYS 계정으로 exceptions table을 생성
: 테스트용 테이블(scott.tt550)을 5보다 큰 값만 오도록 제약 조건을 가지도록 설정
: 테스트용 테이블의 CHECK 속성을 사용 안함 모드로 변경
: 테스트용 데이터를 입력하는데, 5보다 작은 1값도 함께 입력
: NOT NULL 제약 조건을 사용하므로 변경하면서 exceptions table을 사용하게 설정
: exceptions table을 조회하여 에러 내역을 확인
: 테스트용 테이블 scott.tt500에서 문제가 되는 부분을 정상적인 값으로 업데이트
: 다시 5보다 작은 값을 입력해서 제약 조건의 작동 여부 테스트
EXCEPTIONS 테이블을 사용하여 ENABLE VALIDATE하기 - scott 계정 사용
: scott 사용자로 exceptions table을 생성
: 예제용 테이블을 생성한 후 데이터를 입력
: 제약 조건을 enable 시도하여 에러를 확인
: exceptions table에서 에러 내용을 확인한 후 원본 테이블을 수정
: 다시 제약 조건을 enable 시도
제약 조건 조회하기
SELECT owner, constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = '모두 대문자로 적은 테이블명'; 
| CONSTRAINT_TYPE | 의미 |
| P | PRIMARY KEY |
| R | FOREIGN KEY |
| U | UINQUE |
| C | CHECK NOT NULL |
제약 조건 삭제하기
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명;
