[Oracle] 제약사항 (Constraint)

 제약사항 (Constraint) 

- 테이블에 입력되는 값을 개발자가 원하는 범위에서 입력받기 위해 설정하는 것

- 제약사항명은 유일해야 함 (data dictionary에 등록되기 때문)

- key 종류 : primary key, foreign key, unique

  check 종류 : check, not null

- column level constraint(컬럼단위 제약사항)와 table level constraint(테이블단위 제약사항)로 설정할 수 있음

- alter를 가지고 편집할 수 있음

- 테이블이 drop(삭제)되면 제약사항은 같이 삭제됨

- 문법) 

  create table 테이블명 (

   컬럼명 테이터형(크기) constraint 제약사항명 제약사항종류, << 컬럼 단위 제약사항   

   ...

   constraint 제약사항명 제약사항종류(적용컬럼) << 테이블 단위 제약사항   ...  

   );

 

 제약사항 종류 

1) primary key (주키, 기본키) - PK

- null을 허용하지 않고, 컬럼의 값을 유일하게 저장해야할 때 (값이 반드시 있으면서 유일)

- 하나의 테이블에 하나의 PK만 존재할 수 있음

- PK는 n개의 컬럼으로 구성될 수 있음

- PK는 다른 테이블에서 foreign key로 참조할 수 있음

- 컬럼단위 제약사항과 테이블단위 제약사항으로 설정할 수 있음

- user_constraints에서 constraint_type : 'P'

- 테이블에 primary key를 설정하면 Oracle에서는 index가 자동 생성됨 (user_indexes data dictionary에서 확인 가능)

- 문법) 

• 컬럼단위 제약사항(column level constraints)
  - PK를 하나의 컬럼으로만 구성할 수 있음
  - 컬럼명 데이터형(크기) constraint pk_테이블명 primary key, 
     --> PK인 컬럼명을 식별할 수 있음
• 테이블단위 제약사항(table level constraints)
  - PK를 n개의 컬럼으로 구성할 수 있음
  - constraint pk_테이블명 primary key (컬럼명,,,) 
    --> 컬럼 정의와 제약사항 정의를 분리할 수 있음

* 테이블단위 제약사항에서 PK를 n개의 컬럼으로 구성하는 경우 

 - 생산물 추적(자동차의 리콜 LOT 번호 관리), 영화관 좌석 예매

     > 영화관의 좌석은 특정 시간에만 유일하다.

    > 1일에 1번 타임에 1좌석, 1일에 1타임에 2좌석은 OK . 1일에 1타임에 2좌석 또 하면 error

 

* constraint pk_테이블명 생략 가능 (하지만 PK,FK,UK는 다 준다고 생각하면 됨!!)

- Oracle이 제약사항명을 자동 생성하여 primary key를 설정함 (SYS_C...형태)

- 단, 제약사항명을 pk_테이블명으로 주지 않으면 무결성 제약조건 위배에 관한 error msg에서 오류 원인을 식별하기 힘듦

 

 

2) foreign key (외래키, 참조키) - FK

- 컬럼의 값을 다른 테이블(부모 테이블)의 값으로만 추가해야 할 때

- 하나의 테이블에서 여러 개의 컬럼에 FK를 설정할 수 있음

- null 허용

- user_constraints에서 constraint_type : 'R'

- foreign key가 참조하는 레코드가 존재하는 부모테이블의 레코드는 삭제되지 않음

  > 삭제 방법1) 참조하는 자식 레코드를 모두 삭제한 후 부모테이블의 레코드를 삭제

  > 삭제 방법2) 컬럼의 FK 부여시, on delete cascade 옵션 추가

- 컬럼 단위 제약사항과 테이블 단위 제약사항 두가지로 설정 가능

- foreign key로 설정하는 컬럼은 부모 테이블의 컬럼명과 같을 필요는 없고, 데이터형만 같으면 됨

- 문법) 

• 컬럼단위 제약사항(column level constraints)
  > 제약사항 종류 명시하지 않음
  > 컬럼명 데이터형(크기) constraint fk_컬럼명 references 부모테이블명(참조할컬럼명) [on delete cascade]  
    --> FK는 테이블 하나에 여러 컬럼을 설정할 수 있으니까 테이블명 대신 컬럼명으로 제약사항명을 작성함
    --> 다른 테이블에 동일 컬럼명의 fk가 존재한다면 충돌할 수 있으므로, 이때는 fk_테이블명_컬럼명 이렇게 작성
  
• 테이블단위 제약사항(table level constraints)
  > 제약사항 종류를 명시함
  > constraint fk_컬럼명 foreign key (적용 컬럼) references 부모테이블명(참조할컬럼명) [on delete cascade]
  --> FK 적용컬럼의 수와 참조하는 부모의 참조컬럼의 수가 같아야 함! PK처럼 한 번에 여러개 설정 불가!

 

3) unique 

- null을 허용하면서, 유일한 값을 추가해야할 때 (null은 몇 개든 넣을 수 있음)

  (Ex. email 주소, 휴대폰 번호, 카드 번호 - 없을 수도 있지만 있다면 유일함)

- 하나의 테이블에 여러 개의 unique를 설정할 수 있음

- user_constraints에서 constraint_type : 'U'

- unique를 설정하면 index가 자동 생성됨

- 컬럼단위 제약사항, 테이블단위 제약사항 두 가지로 사용 가능

- 문법) 

• 컬럼단위 제약사항(column level constraints)
  > 컬럼명 데이터형(크기) constraint uk_컬럼명 unique,
    --> UK도 테이블 하나에 여러 개의 컬럼을 설정하므로 컬럼명으로 제약사항명을 작성함
          (uk_테이블명_컬럼명 이렇게 해도 됨)
• 테이블단위 제약사항(table level constraints)
  > constraint uk_컬럼명 unique (적용 컬럼)

 

4) check 조건

- 컬럼에 원하는 범위의 값으로만 추가해야 할 때 설정 (연령 제한, 성별 제한 등)

- not null과 check 조건은 컬럼단위 제약사항으로, 제약사항명을 부여하지 않고 설정함

  > 둘다 테이블단위 제약사항으로 정의할 수 있으나 컬럼단위로 많이 함

  > 제약사항명 부여할 수 있으나 부여 X

- user_constraints에서 constraint_type : 'C'

- check 조건은 key가 아니라 check 종류

- 문법) 기본 : 컬럼명 데이터형(크기) check (컬럼명 조건) 

     > constraint 키워드 안 씀. 시스템에서 부여한 제약사항명이 부여됨

     > 컬럼명 : check 조건이 설정된 컬럼만(자신만) 정의할 수 있음 (다른 컬럼을 참조하여 설정할 수 없음)
     > 컬럼명 조건 ex. age between 1 and 10

        --> 조건 : 연산자 사용 (> < => ,,,,)

* 제약사항명 부여 : 컬럼명 데이터형(크기) constraint 제약사항명 check (컬럼명 조건)

    > constraint 키워드를 사용하면 됨 
    > ex) 성별컬럼에 M 또는 F만 입력 가능
       gender char(1) constraint chk_gender check (gender in (‘M’,’F’))
 

 

5) not null 조건

- 컬럼에 값을 반드시 입력해야 하는 경우 (정의되는 모든 컬럼에는 null이 생략되어 있음)

- 컬럼단위 제약사항으로 설정

- create subquery로 복사되는 제약사항

   > not null만 제약사항 중에 유일하게 복사됨

   > pk의 기본 사항인 not null은 복사 안됨. 직접 not null이라는 check 조건을 부여해야 복사됨

- user_constraints에서 constraint_type : 'C' 

- not null을 nn으로 줄여쓰기도 함

- 문법) 컬럼명데이터형(크기) not null 

 

 

6) default

- 컬럼에 null이 입력되는 상황에서 null 대신에 들어갈 값 설정

  > null 입력은 컬럼 생략으로만 가능
      (문자열도 컬럼생략만 가능. ‘’empty 쓰면 default가 아닌 그냥 null이 입력됨)

- user_tab_cols data dictionary에서 확인 : data_default column에서 확인 가능

> default는 제약사항이 아니므로 user_constraints에서 확인 불가

> 컬럼정보를 가진 data dictionary에서 확인 가능

- 컬럼 단위의 문법으로만 설정 가능

- 문법) 컬럼명 데이터형(크기) default 기본값

 

 

** 제약사항 관련 data dictionary

- user_constraints data dictionary : 테이블에 걸려있는 constraints 확인 가능

- user_cons_columns data dictionary : 제약사항이 설정된 컬럼 확인 가능
                                                                               > constraints name 및 컬럼을 볼 수 있음

-  user_indexes data dictionary : 인덱스 조회 가능 (PK, UK는 생성하면 index가 자동생성됨)