CONSTRAINT - 제약조건

제약조건 = CONSTRAINT

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

해당 조건과 맞지 않는 데이터가 들어오면 걸러내기 때문에 데이터의 정확성이 올라간다.

종류는 총 5가지

NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN KEY / CHECK

1. NOT NULL

NULL 값 입력 불가

2. UNIQUE

중복 값 입력 불가

3. PRIMARY KEY

NOT NULL + UNIQUE 즉, 데이터들끼리의 유일성을 보장한느 칼럼에 설정

테이블당 딱 한개만 설정할 수 있음. (대신 칼럼 두개를 합쳐서 하나로 묶고 하나처럼 설정은 가능하다)

4. FOREIGN KEY = 외래키

다른 테이블의 칼럼을 참조해서 있는 데이터만 받아줌

다른 제약조건들과는 달리 두개의 테이블에 동시에 설정된다.

eg. 사원테이블에 데이터가 입력될 때 부서번호를 부서테이블에서 확인한 후 존재하면 입력을 허용 아니면 거부

이때 사원테이블은 자식테이블(정보를 받는 테이블), 부서테이블은 부모테이블(정보를 주는 테이블)

부모테이블에서 참조되는 칼럼에 설정되는 제약조건이 참조키 = REFERENCE KEY

자식테이블에서의 칼럼에서 제약조건이 외래키 = FOREIGN KEY

근데.. 잘못걸면 속도 완전 느려짐. 데이터를 참조해야되는데 데이터건 전부를 읽어야 하니까.

5. CHECK

뒤에 나오는 조건으로 설정된 값만 입력 허용

PRIMARY KEYY 빼고 제약조건은 여러 칼럼에 중복으로 설정될 수 있고, 

하나의 칼럼에도 여러개의 제약 조건들이 중복으로 설정될 수있음.

제약조건을 설정하는 방법은 두가지

테이블 생성할 때 같이 하덩가 아니면 테이블 생성도니 후에 추가해주는거랑

1. 테이블 생성시 제약조건도 같이 설정

문법: CREATE TABLE 테이블명 (칼럼명 데이터타입 CONSTRAINT 제약조건이름 제약조건)

약식문법: CREATE TABLE 테이블명 (칼럼명 데이터타입 제약조건)

  1  create table emp3

  2  (

  3  no number(4) constraint emp3_no_pk PRIMARY KEY,

  4  name varchar2(19) constraint emp3_name_nn NOT NULL,

  5  jumin varchar2(13) constraint emp3_jumin_nn NOT NULL

  6  constraint emp3_jumin_uk UNIQUE,

  7  area number(1) constraint emp3_area_ck CHECK (area < 5),

  8  deptno varchar2(6) constraint emp3_deptno_fk REFERENCES dept2(dcode)

  9* )

약식

  1  create table emp4

  2  (

  3  no number(4) primary key,

  4  name varchar2(10) not null,

  5  jumin varchar2(13) not null unique,

  6  area number(1) check (area < 5),

  7  deptno varchar2(6) references dept2(dcode)

  8* )

설명

콤마로 구분하지 않고 쭈욱 쓰기

jumin varchar2(13) CONSTRAINT emp3_jumin_nn not null CONSTRAINT emp3_jumin_uk unique,

이런식으로 콤마로 구분하는건 하나도 없음, 칼럼하나에 두개의 제약조건을 걸때도 마찬가지 쭈욱쭈욱 쓰며 된다.

제약조건이름은 마음대로 쓸 수 있음. 하지만 보통은 테이블명_칼럼명_제약조건약자 (emp3_jumin_nn)

약식으로 쓸때는 제약조건 이름을 쓰지 않기 때문에 오라클이 마음대로 지정한다.

그래서 나중에 관리할 때 불편함을 초래함. 우선 찾는데도 어려움

2. 테이블 생성후 제약조건 추가

문법: ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 제약조건(칼럼명) ;

다른 제약조건은 이 문법을 쓰는데 NOT NULL은 다름 

문법: ALTER TABLE 테이블명 MODIFY (칼럼명 CONSTRAINT 제약조건이름 NOT NULL) ;

이유는 칼럼에 기본값으로 허용되어 있는 NULL 값을 NOT NULL로 변경하는 것이라서

MODIFY를 사용해서 설정

그러면 참조키 REFERENCE KEY는?

부모테이블 쪽에 설정되는 칼럼이 PRIMARY KEY 나 UNIQUE KEY가 설정되어 있어야 함!

그래서 REFERENCE KEY  제약조건을 추가하려면 먼저 

1. 부모테이블쪽에 설정되는 칼럼을 UNIQUE KEY로 설정

--> ALTER TABLE 부모테이블명 ADD CONSTRAINT 제약조건이름 UNIQUE(부모테이블칼럼) ;

2. ALTER TABEL 자식테이블명 ADD CONSTRAINT 제약조건이름 FOREIGN KEY(자식테이블의칼럼) REFERENCES 부모테이블명(부모테이블의칼럼) ;

부모테이블의 데이터를 지우고 싶은데, 

자식테이블이 부모테이블의 해당 데이터를 참조하고 있기 때문에 지울수가 없음

그래서 FOREIGN KEY 생성할때 아래 옵션 추가하면 가능 

ON DELETE CASCADE 옵션을 주면 부모테이블의 데이터가 지워지면 자식 테이블의 데이터도 함께 지워짐

ON DELETE SET NULL 옵션도 가능한데 부모테이블의 데이터가 지워질 경우 자식테이블의 값을 NULL로 설정

제약조건 관리하기

제약조건들을 DISABLE 했다가 ENABLE 했다가 하는 것, 제약조건을 조회, 삭제하는것

데이터옮기는 작업을 마이그레이션(Migration) 이라고 하는데

이미 검증된 대량의 데이터를 옮길 때 제약조건을 다시 검사할 필요가 없음 이미 검증된거니까.

이럴때 제약조건을 임시로 풀고 다 끝나면 다시 설정해준다. 이게 DISABLE/ENABLE

이때 DISABLE 안하게되면 데이터 한건씩 게속 물어봄 만약 1억건의 데이터라고 하면.. 끔찍함.

1. DISABLE

일시적으로 제약조건을 사용하지 않는것

두가지로 나눌 수 있음.

NOVALIDATE / VALIDATE

NOVALIDATE: 해당 제약 조건이 없어서 데이터가 전부 들어옴, 제약조건 걸려있는걸 파괴시키고 들어오는거임. 

기본값 alter table test_enable disable constraint te_name_nn << 이렇게 novalidate랑 validate 안쓰면 novalidate로 간주 즉, 기본값이다.

문법: ALTER TABLE 테이블명 DISABLE NOVALIDATE CONSTRAINT 제약조건이름 ;

예를들어, Primary key가 설정되어있는는 칼럼에 중복된 데이터를 입력하고 싶다.

근데 당연히 안되지. 이럴때 novalidate를 서서 pk를 깨고 중복된 데이터를 입력할수있어

 

  1  alter table test_novalidate

  2* disable novalidate constraint sys_c0011490

VALIDATE: 데이터 변경이 안되게끔 하는 옵션, 해당칼럼의 내용을 변경할 수없다.

insert, update, delete 작업을 수행할 수 없음

11g 에서의 read only의 개념과 같음

문법: ALTER TABLE 테이블명 DISABLE VALIDATE CONSTRAINT 제약조건이름 ;

2. ENABLE

역시 두가지 NOVALIDATE / VALIDATE

ENABLE NOVALIDATE

enalbe 하는 시점까지 테이블에 들어있던 데이터 (기존 데이터들은 검사하지 않고

enable 하는 시점 이후부터 입력되는 데이터만 제약조건을 적용해서 검사함

문법: ALTER TABLE 테이블명 ENABLE NOVALIDATE CONSTRAINT 제약조건이름 ;

  1  alter table test_enable

  2  disable constraint te_name_nn ; <<  not null 제약조건의 칼럼을 disable 시킨후 null 값을 입력

SCOTT>insert into test_enable values(3,null) ; << null 값을 입력

  1  alter table test_enable 

  2  enable novalidate constraint te_name_nn ; <<다시 enable

SCOTT>select * from test_enable;

        NO NAME

---------- ----------

         1 AAA

         2 BBB

         3 <<<< 아까 diable 시키고 넣은 null 값, 원래는 이칼럼 제약조건은 not null 즉, 잘못된 데이터가 들어가있는것이다. 

  1  insert into test_enable values(4,null) ;  << null값을 넣으려고 했더니 에러가 뜸.

insert into test_enable values(4,null)

                                 *

ERROR at line 1:

ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ENABLE"."NAME")

이 실습에서 알수있듯이 ENABLE NOVALIDATE는 ENABLE 하고 나서 입력되는 데이터만 검사한다는 것을 알수있다.

ENABLE VALIDATE 

이거는 기존데이터든 이후에 입력되는 신규 데이터든 모든 데이터를 전부 검사, 기본값

그래서 enalbe validate를 하게 되면 오라클이 해당 테이블에 데이터가 변경되지 못하도록 lock을 설정함 왜냐? 기존 데이터를 검사해야하니까.

검사 도중에 제약조건을 위반하는 값 not null인데 null값이 있다던지 이런게 발견되면 에러를 발생하면서 enable 작업을 취소함.

문법: ALTER TABLE 테이블명 ENABLE VALIDATE CONSTRAINT 제약조건이름 ;

SCOTT>alter table test_enable

  2  disable constraint te_name_nn ;

Table altered.

SCOTT>alter table test_enable

  2  enable validate constraint te_name_nn ;

enable validate constraint te_name_nn

                           *

ERROR at line 2:

ORA-02293: cannot validate (SCOTT.TE_NAME_NN) - check constraint violated << 아까 3,NULL 값 들어가서 이런 에러가 뜸 NULL값 들어가면 안되는데 들어가있으니까!

그래서 하려면 어떻게 해야하나?

제약조건을 위반하는 값을 직접 찾아서 조치 후 다시 enable 작업을 시도해야함

근데 일일이 다 찾아야 하는게 문제...

그래서 EXCEPTIONS TABLE 을 쓴다는 거임 

ENABLE VALIDATE 할 때 위반사항을, 문제가 되는 행을 저장해 쉽게 찾을 수 있도록 해준다.

문법: ALTER TABLE 테이블명 ENABLE VALIDATE CONSTRAINT 제약조건이름 EXCEPTIONS INTO SYS.EXCEPTIONS ;

EXCEPTIONS TABLE 조회할때는

SELECT ROWID, NO FROM 테이블명 WHERE ROWID IN (SELECT ROW_ID FROM EXCEPTIONS) ;

문제가 되는 부분을 정상적인 값으로 업데이트 해야함

UPDATE 테이블명 SET 칼럼=값 WHERE ROWID='에러난위치' ;

업데이트 했으면 수정완류한 에러내역을 삭제해야 나중에 또 에러났을 때 안 헤갈리니까 에러내역 삭제해주기

TRUNCATE TABLE SYS.EXCEPTIONS ;

다시 ENABLE VALIDATE 해주고

확인차 제약조건 걸려있는지 다시 한번 확인해 주기

p295 실습첨부

3. 조회

테이블에 제약조건 설정하면 그 내용들은 딕셔너리에 저장되어있음

그래서 테이블에 걸려있는 제약조건들을 보려면 딕셔너리의 내용을 조회해야지 볼수 있다.

사용되는 딕셔너리는 USER_CONSTRAINTS / USER_CONS_COLUMN

데이터베이스 전체의 제약조건을 조회하려면 DBA_CONSTRAINTS / DBA_CONS_COLUMN

문법: SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, STATUS FROM USER_CONSTRAINT WHERE TABLE_NAME='대문자테이블명'

CONSTRAINT_TYPE 값이 출력될때

P = Primary Key

U = Unique

C = Check

R = 외래키 foreign key

4. 삭제

제약조건 삭제

문법: ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름 ;