무결성 제약 조건 *

무결성(Integrity)이란?

---

데이터의 정확성, 일관성을 나타낸다.

데이터에 결함이 없는 상태, 즉 데이터를 정확하고 일관되게 유지하는것을 의미한다

 

 

무결성 제약조건 (Integrity Constraint)

---

무결성 제약 조건이란 데이터베이스의 정확성, 일관성을 보장하기 위해 저장, 삭제, 수정 등을 제약하기 위한 조건을 뜻한다

 

무경성 제약 조건	역할
NOT NULL	NULL을 허용하지 않는다.
UNIQUE	중복된 값을 허용하지 않는다. 항상 유일한 값을 갖도록 한다.
PRIMARY KEY	(NOT NULL+UNIQUE=PRIMARY KEY) NULL을 허용하지 않는다. 중복된 값을 허용하지 않는다.
FOREIGN KEY	참조되는 테이블의 칼럼의 값이 존재하면 허용한다.
CHECK	저장 가능한 데이터의 값의 범위나 조건을 지정하여 설정한 값만을 허용한다.

 

▶ NOT NULL 제약조건

: NULL을 허용하지 않음

 

⦁ 사원번로, 사원명, 직급, 부서번호 4개의 칼럼으로 구성된 아무런 제약조건없이 EMP01 테이블 생성과 NULL 삽입 결과

-> NOT NULL 제약조건을 지정하지 않았기 때문에 NULL 값이 저장된다.

CREATE TABLE EMP01( 
  EMPNO NUMBER(4), 
  ENAME VARCHAR2(10), 
  JOB VARCHAR2(9),
  DEPTNO NUMBER(4)
);

인서트 삽입

INSERT INTO EMP01
VALUES(NULL, NULL, 'SALESMAN', 30);

테이블 조회

SELECT * FROM EMP01;

⦁ NOT NULL 제약조건을 지정

-- 테이블 생성
CREATE TABLE EMP01( 
  EMPNO NUMBER(4) NOT NULL, 
  ENAME VARCHAR2(10) NOT NULL, 
  JOB VARCHAR2(9),
  DEPTNO NUMBER(4)
);

-- 인서트 삽입

--> 실행 가능
INSERT INTO EMP01
VALUES(7499, 'ALLEN', 'SALESMAN', 30);

--> !에러 발생: NOT NULL 제약조건을 지정하였기에 사원번호와 사원명은 필수 입력란이 됨 NULL값 저장 못함
INSERT INTO EMP01
VALUES(NULL, NULL, 'SALESMAN', 30);

 

 

▶ UNIQUE 제약 조건

: 특정 칼럼에 대해 자료가 중복되지 않게 해야한다 즉, 지정된 칼럼에는 유일한 값이 수록되게 하는것이다

 

: UNIQUE와 NULL의 관계

UNIQUE는 NULL 값을 예외로 간주한다. 만약 NULL 값마저도 입력되지 않게 제한하려면 테이블 

생성시 EMPNO NUMBER(4) NOT NULL UNIQUE처럼 두 가지 제약 조건을 기술해야 한다.

 

 

⦁ 사원번호가 사원들을 구분하기 위한 칼럼인데도 불구하고 동일한 사번을 갖게 되면 문제가 생긴다

위와 같은 결과를 막고자 할 때는 UNIQUE KEY 제한조건을 사용한다.

CREATE TABLE EMP02( 
  EMPNO NUMBER(4) UNIQUE,
  ENAME VARCHAR2(10) NOT NULL, 
  JOB VARCHAR2(9),
  DEPTNO NUMBER(4)
);

-- 1번째 인서트 삽입
INSERT INTO EMP02(EMPNO, ENAME, JOB, DEPTNO) 
VALUES(7499, 'ALLEN', 'SALESMAN', 30);

-- 2번째 인서트 삽입
--> !사원번호가 중복돼 에러 발생
NSERT INTO EMP02(EMPNO, ENAME, JOB, DEPTNO) 
VALUES(7499, 'ALLEN', 'SALESMAN', 30);

-- 유니코드 제약조건을 지정한곳에 NULL값을 넣으면?
--NULL은 값(VALUE)에서 제외되므로 유일한 조건인지를 체크하는 값에서 제외된다 (실행가능)
INSERT INTO EMP02(EMPNO, ENAME, JOB, DEPTNO) 
VALUES(NULL, 'JONES', 'MANAGER', 20);

 

 

▶ PRIMARY KEY(기본키) 제약 조건

데이터의 구분을 위한 제약조건

식별 기능을 갖는 칼럼이 중복되지 않아야하면서+NULL값 허용되지 않는다

NOT NULL + UNIQUE = PRIMARY KEY

 

⦁ 사원 테이블의 사원번호를 기본키(PRIMARY KEY)로 지정

CREATE TABLE EMP03( 
  EMPNO NUMBER(4) PRIMARY KEY,
  ENAME VARCHAR2(10) NOT NULL, 
  JOB VARCHAR2(9),
  DEPTNO NUMBER(4)
);

INSERT INTO EMP03
VALUES(7499, 'ALLEN', 'SALESMAN', 30);

--> ! 에러 발생 (사원번호가 중복됨)
INSERT INTO EMP03
VALUES(7499, 'JONES', 'MANAGER', 20);

--> ! 에러 발생 (NULL값 허용안됨)
INSERT INTO EMP03
VALUES(NULL, 'JONES', 'MANAGER', 20)

 

 

▶ FOREGIN KEY 제약 조건

참조 무결성을 위한 제약조건

참조 무결성은 두 테이블 사이의 주종 관계에서 설정된다

 

먼저 존재해야 하는 테이블이 주체가 되는 테이블이므로 부서 테이블이 부모 테이블이 되고, 이를 참조하는 테이블인 사원 테이블이 자식 테이블이 된다.

 

소속이란 관계는 두 테이블 간의 참조 무결성이란 개념을 포함한 외래 키 제약 조건을 명시해야만 설정된다. 

외래 키 제약 조건은 자식 테이블인 사원 테이블의 부서번호(DEPTNO) 칼럼에 부모 테이블인 부서테이블의 부서번호를 부모키로 설정하는 것이다.

 

⦁ 부모키가 되기 위한 칼럼은 반드시 부모 테이블의 기본 키나 유일키로 설정되어 있어야 한다.

CREATE TABLE DEPT01(    
           DEPTNO NUMBER(2) PRIMARY KEY, 
           DNAME VARCHAR2(14) NOT NULL, 
           LOC VARCHAR2(13)
);

Insert into DEPT01 (DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
Insert into DEPT01 (DEPTNO,DNAME,LOC) values (20,'RESEARCH','DALLAS');
Insert into DEPT01 (DEPTNO,DNAME,LOC) values (30,'SALES','CHICAGO');
Insert into DEPT01 (DEPTNO,DNAME,LOC) values (40,'OPERATIONS','BOSTON');

 

외래 키 제약 조건에 지정하지 않은 EMP03 테이블에 부서 테이블에 존재하지 않은 50번 부서번호를 저장해 보도록 하자.

NSERT INTO EMP03
VALUES(7566, 'JONES', 'MANAGER', 50);

SELECT * FROM EMP03;
SELECT * FROM DEPT01;

외래키 제약 조건은 EMP04 테이블을 생성시 칼럼명과 자료형을 기술한 후에 REFERENCES를 기술하면 된다. DEPTNO 칼럼을 참조하게 외래키 제약조건을 설정한다.

CREATE TABLE EMP04( 
  EMPNO NUMBER(2) PRIMARY KEY,
  ENAME VARCHAR2(10) NOT NULL, 
  JOB VARCHAR2(9),
  DEPTNO NUMBER(2) REFERENCES DEPT01(DEPTNO)
);
--> Table EMP04이(가) 생성되었습니다

INSERT INTO EMP04
VALUES(7499, 'ALLEN', 'SALESMAN', 30);

--> ! 50번이 존재하지 않기 때문에 사원 정보가 추가되지 못하고 오류가 발생
INSERT INTO EMP04
VALUES(7566, 'JONES', 'MANAGER', 50);

 

 

▶ CHECK 제약 조건

: 입력되는 값을 체크하여 설정된 값 이외의 값이 들어오면 오류 메시지와 함께 명령이 수행되지 못하게 한다.

 

 

 

 

 

 

 

 

 

 

 

 

데이터 딕셔너리란?

1) 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블이다.

2) 사용자가 테이블을 생성하거나 사용자를 변경하는 등의 작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블이다.

3) 사용자는 데이터 딕셔너리의 내용을 직접 수정하거나 삭제할 수 없다.

4) 데이터 딕셔너리를 사용자가 조회해 보면 시스템이 직접 관리하는 테이블이기에 암호 같은 기호만 보여질 뿐 내용을 알 수 없다.

5) 의미 있는 자료 조회가 불가능하기에 오라클은 사용자가 이해할 수 있는 데이터를 산출해 줄 수 있도록 데이터 딕셔너리에서 파생한 데이터 딕셔너리 뷰를 제공한다.

 

• 데이터 딕셔너리 뷰는 접두어 따라 다음의 세 종류가 있다.

접두어	의 미
DBA_XXXX	데이터베이스 관리자만 접근 가능한 객체 등의 정보 조회
ALL_XXXX	자신 계정 소유 또는 권한을 부여받은 객체 등에 관한 정보 조회
USER_XXXX	자신의 계정이 소유한 객체 등에 관한 정보 조회

HR 사용자가 생성한 테이블의 이름을 조회한다

SELECT TABLE_NAME FROM USER_TABLES
ORDER BY TABLE_NAME DESC;

 

 

• 제약조건 확인하기

USER_CONSTRAINTS 데이터 딕셔너리

- 제약조건(CONSTRAINTS)의 에러 메시지에 대한 정확한 원인을 알기 위해 오라클에서 제공해 주는 딕셔너리

- 제약조건의 정보를 위해 많은 칼럼으로 구성되어 있지만 ①제약조건명(CONSTRAINT_NAME) ②제약조건유형(CONSTRAINT_TYPE) ③제약조건이 속한 테이블명(TABLE_NAME)만을 알아본다.

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

 

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME='EMP02';