무결성 제약 조건

Kiwisae 2022. 7. 27. 16:29

무결성 제약 조건

부적절한 자료가 입력되는 것을 방지하기 위해

테이블 생성 시 각 컬럼에 정의하는 여러가지 규칙

무결성 제약 조건	역할
NOT NULL	NULL을 허용하지 않는다. 반드시 값을 입력해야 한다. 필수 입력을 위한 제약 조건.
UNIQUE	중복된 값을 허용하지 않는다. NULL 값은 계속 입력 가능하다. 유일한 값을 위한 제약 조건.
PRIMARY KEY (기본 키)	NULL을 허용하지 않고 중복된 값을 허용하지 않는다. NOT NULL + UNIQUE 데이터 구분을 위한 제약 조건.
FOREIGN KEY (외래 키)	참조되는 테이블의 칼럼의 값이 존재하면 허용한다. 참조 무결성을 위한 제약 조건
CHECK	저장 가능한 데이터 값의 범위나 패턴을 지정하여 설정한 값만을 허용한다. 범위 설정을 위한 제약 조건
DEFAULT	값을 입력하지 않고 비워두었을 때, 디폴트 제약의 값이 자동으로 입력된다.

Primary Key, PK, 기본 키

Not null + Unique를 결합한 제약 조건이다.

반드시 값을 입력해야 하며, 중복되어서도 안된다.

테이블 당 하나 정도만 있으면 충분하며, 만약 이 제약 조건을 가진 컬럼이 없다면 데이터 제어가 힘들어진다.

emp 테이블의 empno (사원번호) 에 설정되어 있고, 게시판의 게시글 넘버나 회원 관리 시 ID에 설정을 많이 한다.

줄여서 PK라고 부르지만 SQL문 작성 시 정확하게 Primary Key라고 작성해야 한다.

Foreign Key, FK, 외래 키

먼저 정의되어야 하는 테이블이 부모 테이블, 나중에 정의하는 테이블이 자식 테이블이다.

부모 키 컬럼은 Unique, Primary Key 둘 중 하나는 반드시 설정되어 있어야만 한다.

이 제약 조건이 설정되어 있는 테이블은 부모 테이블의 부모 키 값만 참조할 수 있다.

(다른 값은 입력할 수 없다.)

dept.deptno 가 부모 키, emp.deptno가 자식 키라고 할 수 있다.

dept에서 부서 번호에 대한 정보가 먼저 정의되어야만

emp 테이블에서 각 사원에게 부서 번호를 부여할 수 있기 때문이다.

이 때, emp.deptno에 50 이나 60 같이 부모 키에 없는 값을 입력하려 하면 오류 메시지가 뜨면서 입력을 제한한다.

emp 테이블에 제약 조건을 위배하는 부서 번호 50 데이터를 입력하려 하면

아래와 같이 오류가 발생하며 입력을 제한한다.

다른 제약 조건은 단순히 제약 조건명을 명시하면 됐지만, 이 Foreign key를 설정하는 방법은 조금 다르다.

우선, 부모 테이블이 먼저 생성되어 있어야 하고

참조할 컬럼이 부모 키가 될 수 있는 조건을 만족하고 있어야 한다.

형식 : 자식컬럼 references 부모테이블(부모키컬럼) 

create table emp06(
        empno number(4) primary key,
        ename varchar2(10) not null,
        job varchar2(10),
        deptno number(2) references dept(deptno));
        
foreign key라고 적는것이 아니라 references로 형식에 맞춰 입력해야 한다.

외래 키 제약 조건을 위배한 값을 넣었을 때 (10, 20, 30, 40 대신 50을 넣었음)

외래 키에 맞는 값을 넣었을 때

Check

1) 데이터 값의 범위를 설정할 수 있다.

2) 대소문자도 구분해야 한다.

Default

아무 값을 입력하지 않았을 때, 디폴트 제약 조건을 설정할 때 입력한 값이 들어간다.

USER_CONSTRAINTS 데이터 딕셔너리

제약 조건에 대한 정보를 알려준다.

접속한 user가 만든 제약 조건의 정보를 조회할 수 있다.

약어 형태로 제약 조건이 표기 된다.

CONSTRAINT_TYPE	의미
P	PRIMARY KEY
R	FOREIGN KEY
U	UNIQUE
C	CHECK, NOT NULL

테이블 생성 시 제약 조건명 설정

제약 조건에 이름을 설정할 수도 있다.

단순히 not null, unique, primary key 등으로 지칭해서 제약 조건을 설정한 컬럼을 생성한다면,

다음과 같이 sys_ 가 붙은 이름으로 임의 생성된다.

그런데 제약 조건을 위배한다면, 오라클이 부여한 제약 조건명 만으로는 알기가 힘들어서

반드시 명령어로 데이터 딕셔너리를 검색해야만 확인이 가능하다.

만약 사용자가 제약 조건명을 명시하여 테이블을 생성한다면, 어떤 제약 조건을 위배했는지 알 수 있게 된다.

↓ 제약 조건 명을 명시하지 않고

단순히 not null과 primary key 제약 조건만을 설정하여 생성한 테이블이다.

실습으로 테이블을 하나 생성해본다.

제약 조건 이름을 설정하는 형식은 다음과 같다.

형식 : 테이블명_컬럼명_제약조건유형

CREATE TABLE EMP04( 
		EMPNO NUMBER(4) CONSTRAINT EMP04_EMPNO_UK UNIQUE,
		ENAME VARCHAR2(10) CONSTRAINT EMP04_ENAME_NN NOT NULL, 
		JOB VARCHAR2(9),
		DEPTNO NUMBER(2)
		);

위 캡쳐의 sys_ 로 시작되는 이름 대신 형식에 맞춰 설정한 이름이 제약조건명으로 들어가있다.

제약 조건 지정 방식

1) 컬럼 레벨 제약 조건

컬럼 단위로 제약 조건을 지정한다.

지금까지 상술한 모든 방식들이 컬럼 레벨 방식이다.

2) 테이블 레벨 제약 조건

컬럼을 모두 정의하고 나서 마지막으로 괄호를 닫기 전 한꺼번에 제약 조건을 지정하는 방식이다.

보통은 컬럼 레벨 방식을 사용하지만, 다음과 같은 경우에는 테이블 레벨 제약 조건 방식을 사용해야 한다.

1) Primary Key를 복합키로 사용하는 경우 : 2개 이상의 Primary Key 사용

2) Alter table로 제약 조건을 추가할 때

alter 제약조건 추가

alter table 테이블명 add primary key(empno);

alter table 테이블명 add unique(ename);

alter table 테이블명 add foreign key(deptno) references dept(deptno);

alter table 테이블명 modify 컬럼명 not null;
-- not null은 조금 특이하다...
-- null을 허용하는 상태 -> 허용하지 않는 상태로 수정하겠다는 의미로 이해

alter table 테이블명 add constraint dept01_deptno_pk primary key(deptno);

alter 제약조건 삭제

alter table 테이블명 drop primary key;

alter table 테이블명 drop unique(ename);

alter table 테이블명 drop constraint constraint_name;

제약 조건의 비활성화, CASCADE

emp.deptno는 dept.deptno를 참조하고 있다.

만일 dept.deptno의 20번 부서 데이터를 삭제해야 할 때, 막상 삭제하려고 하면 오류가 나고 실행되지 않는다.

이것은 자식 테이블과 참조 관계로 엮인 데이터이기 때문인데,

emp에서 참조하지 않고 있는 40번 데이터 같은 경우에는 삭제가 가능하다.

그러나 나머지 10, 20, 30번 데이터를 지워야 할 때에는

dept를 부모 테이블로 삼고 있는 자식 테이블 emp를 지워야만 dept도 지울 수가 있다.

이럴 때에는 삭제나 수정을 곤란하게 하는 제약 조건을 잠시 비활성화 할 수 있다.

다음과 같이 테이블 생성시 마지막 문단에 on delete cascade를 붙이면 비활성화/활성화를 할 수 있는 기능이 생긴다.

create table emp01 (
                        empno number(4) primary key,
                        ename varchar2(10) not null,
                        job varchar2(10) unique,
                        deptno number(2) references dept(deptno)
                        on delete cascade);

활성화 / 활성화

alter table emp01 enable constraint sys_c007099; 활성화
alter table emp01 disable constraint sys_c007099; 비활성화

비활성화 시

활성화

Foreign key로 참조중인 부모 키 컬럼의 조건으로 Primary key여야 한다는 조건이 있어서,

만일 부모 테이블에서 Primary key 조건을 해제한다면, 자식 테이블의 Foreign key 도 자동으로 해제된다.