데이터베이스 - 제약조건

제약조건 = CONSTRAINT

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

 

종류

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

 

1. NOT NULL

: NULL 값 입력 불가( 값을 무조건 입력해야함 )

 

2. UNIQUE

: 중복 값 입력 불가

 

3. PRIMARY KEY

NOT NULL + UNIQUE 즉, 데이터들끼리의 유일성을 보장하는 컬럼에 설정

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

 

4. FOREIGN KEY = 외래키

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

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

 

5. CHECK

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

 

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

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

 

---

 

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

=> 테이블 생성할 때

=> 테이블 생성된 후에 추가

 

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

 

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

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

 

 

create table test
(
no number(4) constraint pk_testno PRIMARY KEY,
name varchar2(19) constraint nn_testname NOT NULL,
jumin varchar2(13) constraint nn_testjumin NOT NULL
constraint uk_testjumin UNIQUE,
area number(1) constraint ck_test_area CHECK (area < 5),
deptno varchar2(6) constraint fk_test_deptno REFERENCES dept2(dcode)
);

 

 

* 제약조건명은 보통 제약조건약자_테이블명_칼럼 // 나중에 찾기 편하다.

 

약식

create table test
(
no number(4) primary key,
name varchar2(10) not null,
jumin varchar2(13) not null unique,
area number(1) check (area < 5),
deptno varchar2(6) references dept2(dcode)
);

 

 

 

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. 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를 깨고 중복된 데이터를 입력할수있어

 

alter table test_novalidate

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 제약조건이름 ;

 

alter table test_enable

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

 

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 제약조건이름 ;