9. 제약조건(Constraint) and 제약조건의 비활성화(Disable) 및 활성화(Enable)ㅊ

제약조건(Constraint)이란 

테이블에 올바른 데이터만 입력받고 잘못된 데이터는 들어오지 못하도록 컬럼마다 정하는 규칙을 의미

⊙Constraint 이름 설정과 함께 제약조건 명시하기

--1) 테이블 생성 시 제약 조건 명시
CREATE TABLE new_emp1(
No 	NUMBER(4) 		CONSTRAINT emp1_no_pk 		PRIMARY KEY,
Name 		VARCHAR(20) 	CONSTRAINT emp1_name_nn 	NOT NULL,
JuMin 		VARCHAR(13) 	CONSTRAINT emp1_jumin_uk 	UNIQUE
											CONSTRAINT emp1_jumin_nn 	NOT NULL,
Loc_Code NUMBER(1) 		CONSTRAINT emp1_area_ck 	CHECK( loc_code<5 ),
D_Code 	NUMBER(4,0) 		CONSTRAINT emp1_deptno_fk 	REFERENCES departments(department_id)
);

--제약조건 확인

SELECT * FROM user_constraints WHERE table_name = 'NEW_EMP1';

--a) PK  위배 체크 
INSERT INTO new_emp1(Name, JuMin, Loc_Code, D_Code) 
VALUES('park bo gum', '1922342', 1, 80);

--b) a의 오류 수정 
INSERT INTO new_emp1(No, Name, JuMin, Loc_Code, D_Code) 
VALUES(1, 'park bo gum', '1922342', 1, 80);

--c) No컬럼에 중복 데이터 입력
INSERT INTO new_emp1(No, Name, JuMin, Loc_Code, D_Code) 
VALUES(1, 'park bo gum', '1922342', 1, 80);

--d) name값의 제약조건 위배 체크
INSERT INTO new_emp1(No, Name, JuMin, Loc_Code, D_Code)
VALUES(2, '', '1922342', 1, 80);

--e) JuMin값의 제약조건 위배 체크 (nn and uk)
INSERT INTO new_emp1(No, Name, JuMin, Loc_Code, D_Code)
VALUES(2, 'park bo gum', '', 1, 80);
INSERT INTO new_emp1(No, Name, JuMin, Loc_Code, D_Code)
VALUES(2, 'park bo gum', '1922342', 1, 80);

 

--f) Loc_Code값의 제약조건 위배 체크
INSERT INTO new_emp1(No, Name, JuMin, Loc_Code, D_Code) 
VALUES(1, 'park bo gum', '1922342', 6, 80);

--g) D_Code 값의 제약조건 위배 체크
INSERT INTO new_emp1(No, Name, JuMin, Loc_Code, D_Code) 
VALUES(2, 'lee hong bin', '1232342', 1, 00);

⊙Constraint 이름 없이 제약조건 명시하기

--2) constraint 명 없이 제약조건 명시하기
CREATE TABLE new_emp2(
no number(4) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
jumin varchar(13) NOT NULL,
loc_code number(1) check(loc_code<5),
deptno number(4,0) REFERENCES departments(department_id)
);

SELECT * FROM user_constraints WHERE table_name = 'NEW_EMP2';
--참고로, 테이블 검색할 때는 항상 대문자로 테이블명 검색해야 함

⊙테이블 생성 후 제약조건 추가하기(null, not null은 제외)

ALTER TABLE new_emp2
ADD CONSTRAINT emp_jumin UNIQUE(jumin);

---

제약조건의 비활성화 및 활성화

DISABLE(비활성화)
- NOVALIDATE: 해당 제약 조건을 임시로 비활성화
- VALIDATE: 해당 컬럼의 값을 수정하지 못하도록 설정(INSERT, UPDATE, DELETE 문 사용할 수 없음)

DISABLE NOVALIDATE

create table t_novalidate(
no number constraint t_noval_no_pk PRIMARY key,
name varchar(10) constraint t_noval_name_nn NOT NULL);

insert into t_novalidate values(1, 'aaa');
insert into t_novalidate values(2, 'bbb');
insert into t_novalidate values(3, 'ccc');
COMMIT;

--1) 중복 오류 발생 
insert into t_novalidate values(1, 'aaa');

--2) 제약 조건을 DISABLE NOVALIDATE 속성으로 비활성화 시킨다
alter table t_novalidate DISABLE NOVALIDATE constraint t_noval_no_pk;
--3) 중복 데이터 재삽입
insert into t_novalidate values(1, 'aaa');

DISABLE VALIDATE

create table t_validate(
no number constraint t_val_no_pk PRIMARY key,
name varchar(10) constraint t_val_name_nn NOT NULL);

--4) t_validate 테이블에 데이터 삽입
insert into t_validate values(1, 'aaa');
insert into t_validate values(2, 'bbb');
insert into t_validate values(3, 'ccc');
commit;
select * from t_validate;

--5) t_validate 테이블에 disable validate 속성으로 제약조건 비활성화
alter table t_validate DISABLE VALIDATE CONSTRAINT t_val_no_pk;

--6) t_validate 테이블의 제약조건을 비활성화했음에도 불구하고 중복 데이터 삽입X
insert into t_validate values(1, 'aaa');

---

UNIQUE INDEX 생성

CREATE UNIQUE INDEX idx_emp2_employee_id ON emp2(employee_id); 

NON-UNIQUE INDEX 생성

CREATE INDEX idx_emp2_first_name ON emp2(first_name);

결합 인덱스(Composite INDEX) 생성

CREATE INDEX idx_emp2_comp ON emp2(first_name, department_id);

emp2에 설정된 index 확인 
select * from user_ind_columns where table_name = 'EMP2';
select * from user_indexes where table_name = 'EMP2';

INDEX MONITORING 

--인덱스 모니터링 시작(사용)
alter index idx_emp2_first_name MONITORING USAGE;

---> 모니터링 중지는 NOMONITORING 쓰면 된다.

INDEX 사용 유/무 확인

SELECT INDEX_name, used
from v$object_usage
WHERE index_name = 'IDX_EMP2_FIRST_NAME';