데이터 무결성

이 장에서는 무결성 제약 조건이 데이터베이스와 연관된 업무 규칙을 시행하고 부적합한 정보를 테이블에 입력하지 못하도록 하는 방법에 대해 설명합니다.

데이터 무결성 소개

데이터는 데이터베이스 관리자 또는 애플리케이션 개발자가 결정한 비즈니스 규칙을 준수하는 데이터 무결성을 유지 관리해야 합니다.

비즈니스 규칙은 항상 참이거나 항상 거짓이어야 하는 조건 및 관계를 지정합니다. 예를 들어, 각 회사는 급여, 직원 번호, 재고 추적 등에 대한 자체 정책을 정의합니다.

데이터 무결성 보장 기법

데이터베이스 응용 프로그램을 설계할 때 개발자는 데이터베이스에 저장된 데이터의 무결성을 보장하는 여러 가지 옵션을 사용할 수 있습니다.

옵션은 다음과 같습니다.

참조:

무결성 제약 조건의 이점

무결성 제약 조건은 SQL을 사용하여 생성 및 삭제되는 스키마 객체입니다. 데이터 무결성을 적용하려면 가능하면 무결성 제약 조건 또는 검증을 사용하십시오.

데이터 무결성을 적용하기 위한 대안에 비해 무결성 제약 조건 및 검증의 이점은 다음과 같습니다.

참조:

무결성 제약 조건 유형

Oracle AI Database를 사용하면 테이블 및 열 레벨의 제약 조건과 여러 테이블이 포함된 검증을 모두 적용할 수 있습니다.

열 또는 속성 정의의 일부로 지정된 제약 조건은 인라인 사양입니다. 테이블 정의의 일부로 지정된 제약 조건은 Out-of-Line 사양입니다.

키는 특정 유형의 무결성 제약 조건 정의에 포함된 열 또는 일련의 열입니다. 키는 관계형 데이터베이스의 테이블과 열 간의 관계를 설명합니다. 키의 개별 값을 키 값이라고 합니다.

다음 표에서는 제약 조건 유형에 대해 설명합니다. 각각 인라인이어야 하는 NOT NULL를 제외하고 인라인 또는 아웃오브라인으로 지정할 수 있습니다.

테이블 1 무결성 제약 조건 유형

제약 조건 유형 설명 참조 항목
NOT NULL 지정된 열에 null이 포함된 행의 삽입 또는 갱신을 허용하거나 허용하지 않습니다. "NOT NULL Integrity Constraint"
고유 키입니다 같은 열 또는 열의 조합에서 여러 행이 같은 값을 가지지 못하도록 하지만 일부 값이 널인 것은 허용합니다. "고유 제한조건"
기본 키 NOT NULL 제약 조건과 고유 제약 조건을 결합합니다. 다중 행이 동일한 열 또는 열 조합에서 동일한 값을 가지지 못하도록 하고 값이 null이 되는 것을 금지합니다. "기본 키 제약 조건 "
외래키 열을 외래 키로 지정하고 외래 키와 참조된 키라는 기본 또는 고유 키 간의 관계를 설정합니다. "외래 키 제한 조건"
수표 지정된 조건을 준수하려면 데이터베이스 값이 필요합니다. "검사 제약 조건"
REF REF 열의 값에 허용되는 데이터 조작 유형 및 이러한 작업이 종속 값에 미치는 영향을 나타냅니다. 객체 관계형 데이터베이스에서 REF이라는 내장 데이터 유형은 지정된 객체 유형의 행 객체에 대한 참조를 캡슐화합니다. REF 열의 참조 무결성 제약 조건은 REF에 대한 행 객체가 있는지 확인합니다. REF 제약 조건에 대한 자세한 내용은 Oracle AI Database Object-Relational Developer's Guide를 참조하십시오.
Assertions 검증은 응용 프로그램의 데이터베이스 테이블에 대한 subquery를 포함하는 부울 표현식입니다. 검증에는 제약 조건의 의미가 있습니다. 즉, 트랜잭션이 데이터를 변경하고 이러한 변경 사항을 커밋하는 동안 데이터베이스는 이러한 부울 표현식의 진실성을 보장해야 합니다. 검증에 대해 알아보는 SQL 검증

참조:

NOT NULL 무결성 제약 조건

NOT NULL 제약 조건에서는 테이블의 열이 Null 값을 가질 수 없습니다. null은 값이 없는 경우입니다. 기본적으로 테이블의 모든 열은 null을 허용합니다.

NOT NULL 제약 조건은 값이 부족하지 않아야 하는 열을 위한 것입니다. 예를 들어, hr.employees 테이블에는 email 열의 값이 필요합니다. 전자 우편 주소 없이 직원 행을 삽입하려고 하면 다음과 같은 오류가 발생합니다.

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

테이블에 행이 없거나 기본값을 지정한 경우 NOT NULL 제약 조건이 있는 열만 추가할 수 있습니다.

참조:

고유 제약 조건

고유 키 제약 조건을 사용하려면 열 또는 열 집합의 모든 값이 고유해야 합니다. 테이블의 어떤 행도 단일 열(고유 키) 또는 고유 키 제약 조건이 있는 열 집합(조합 고유 키)에서 중복 값을 가질 수 없습니다.

주: 키라는 용어는 무결성 제약 조건에 정의된 열만 나타냅니다. 데이터베이스가 키 열에서 인덱스를 암시적으로 만들거나 재사용하여 고유 제약 조건을 적용하기 때문에 고유 키라는 용어는 고유 키 제약 조건 또는 고유 인덱스의 동의어로 잘못 사용되는 경우가 있습니다.

고유 키 제약 조건은 중복 값이 허용되지 않는 모든 열에 적합합니다. 고유 제약 조건은 각 테이블 행을 고유하게 식별하기 위한 목적으로 Primary Key 제약 조건과 다르며, 일반적으로 고유하지 않은 다른 의미가 없는 값을 포함합니다. 고유 키의 예는 다음과 같습니다.

예 4-1에 표시된 것과 같이, 고유 키 제약 조건은 hr.employees 테이블의 email 열에 존재합니다. 명령문의 관련 부분은 다음과 같습니다.

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn  NOT NULL ...
    , CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

emp_email_uk 제약 조건은 다음 예제와 같이 두 명의 사원이 동일한 전자 메일 주소를 가지지 않도록 합니다.

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

NOT NULL 제약 조건도 정의되지 않은 경우 널은 항상 고유 키 제약 조건을 충족합니다. 따라서 고유 키 제약 조건과 NOT NULL 제약 조건이 모두 있는 열이 일반적입니다. 이 조합을 사용하면 유저가 고유 키에 값을 입력해야 하며 새 행 데이터가 기존 행 데이터와 충돌할 가능성이 없어집니다.

주: 여러 열의 고유 키 제약 조건에 대한 검색 방식 때문에 부분적으로 널인 조합 고유 키 제약 조건의 널이 아닌 열에서 동일한 값을 가질 수 없습니다.

예제 7-1 고유 제약 조건

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

참조:

기본 키 제약 조건

기본 키 제약 조건에서 제약 조건이 적용되는 하나 이상의 열 그룹에 있는 값은 행을 고유하게 식별합니다. 각 테이블에는 하나의 기본 키가 있을 수 있습니다. 이 키는 실제로 행의 이름을 지정하고 중복 행이 존재하지 않도록 합니다.

기본 키는 자연 키이거나 대리 키일 수 있습니다. 자연 키는 테이블의 기존 속성으로 구성된 의미 있는 식별자입니다. 예를 들어, 자연 키는 룩업 테이블의 우편 번호일 수 있습니다.

반면, 대리 키는 테이블 내에서 고유성을 보장하는 시스템 생성 증분 식별자입니다. 일반적으로 시퀀스 또는 UUID는 대리 키를 생성합니다.

Oracle AI Database의 기본 키 제약 조건을 구현하면 다음 명령문이 적용됩니다.

기본 키를 호출하는 일반적인 상황은 사원의 숫자 식별자입니다. 각 사원은 고유한 ID를 가지고 있어야 합니다. 사원은 employees 테이블에서 한 행으로만 설명되어야 합니다.

고유 제약 조건의 예는 기존 사원의 사원 ID가 202이고 사원 ID가 기본 키임을 나타냅니다. 다음 예제는 동일한 사원 ID와 ID가 없는 사원을 추가하려는 시도를 보여줍니다.

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")

데이터베이스는 기본 키 제약 조건을 인덱스로 강제 적용합니다. 일반적으로 열에 대해 생성된 Primary Key 제약 조건은 고유 인덱스와 NOT NULL 제약 조건을 암시적으로 생성합니다. 이 규칙에 대한 다음 예외 사항을 유의하십시오.

기본적으로 암시적으로 생성되는 인덱스의 이름은 Primary Key 제약 조건의 이름입니다. 인덱스에 대한 사용자 정의 이름을 지정할 수도 있습니다. 제약 조건을 생성하는 데 사용되는 CREATE TABLE 또는 ALTER TABLE 문에 ENABLE 절을 포함하여 인덱스에 대한 저장 옵션을 지정할 수 있습니다.

참조: Oracle AI Database Get Started with Oracle AI Database Development 및 Oracle AI Database Development Guide를 통해 테이블에 기본 키 제약 조건을 추가하는 방법 학습

외래 키 제약 조건

두 테이블에 하나 이상의 공통 열이 포함될 때마다 Oracle AI Database는 외래 키 제약 조건(참조 무결성 제약 조건이라고도 함)을 통해 두 테이블 간의 관계를 적용할 수 있습니다.

Foreign Key 제약 조건에서는 제약 조건이 정의된 열의 각 값에 대해 지정된 다른 테이블 및 열의 값이 일치해야 합니다. 참조 무결성 규칙의 예로 사원은 기존 부서에 대해서만 작업할 수 있습니다.

다음 표에서는 참조 무결성 제약 조건과 연관된 용어를 나열합니다.

표 2 참조 무결성 제약 조건 용어

기간 정의
외래키

참조된 키를 참조하는 제약 조건 정의에 포함된 열 또는 열 집합입니다. 예를 들어, employees의 department_id 열은 departments의 department_id 열을 참조하는 외래 키입니다.

외래 키는 여러 열로 정의할 수 있습니다. 그러나 조합 Foreign Key는 동일한 수의 열과 동일한 데이터 유형을 가진 조합 Primary Key 또는 Unique Key를 참조해야 합니다.

Foreign Key 값은 참조되는 Primary Key 또는 Unique Key 값과 일치하거나 null일 수 있습니다. 조합 Foreign Key의 열이 null인 경우 키의 null이 아닌 부분은 상위 키의 해당 부분과 일치하지 않아도 됩니다.
참조 키 외래 키로 참조되는 테이블의 고유 키 또는 기본 키입니다. 예를 들어, departments의 department_id 열은 employees의 department_id 열에 대해 참조된 키입니다.
종속 또는 하위 테이블 외래 키를 포함하는 테이블입니다. 이 테이블은 참조된 고유 키 또는 기본 키에 있는 값에 따라 달라집니다. 예를 들어, employees 테이블은 departments의 하위입니다.
참조 또는 상위 테이블 하위 테이블의 외래 키에서 참조하는 테이블입니다. 하위 테이블에서 특정 삽입 또는 업데이트가 허용되는지 여부를 결정하는 이 테이블의 참조 키입니다. 예를 들어, departments 테이블은 employees의 상위 테이블입니다.

그림 7-1은 employees.department_id 열의 외래 키를 보여줍니다. 이 열의 모든 값이 departments.department_id 열의 값과 반드시 일치합니다. 따라서 employees.department_id 열에 잘못된 부서 번호가 존재할 수 없습니다.

그림 1: 참조 무결성 제약 조건

그림 cncpt056.gif에 대한 설명

자체 참조 무결성 제약 조건

자체 참조 무결성 제약 조건은 동일한 테이블의 상위 키를 참조하는 외래 키입니다.

다음 그림에서 자체 참조 제약 조건은 employees.manager_id 열의 모든 값이 employees.employee_id 열의 기존 값에 해당하도록 합니다. 예를 들어, 사원 102의 관리자는 employees 테이블에 있어야 합니다. 이 제약 조건은 manager_id 열에서 사원 번호가 잘못될 가능성을 없애줍니다.

그림 2: 단일 테이블 참조 제약 조건

그림 cncpt057.gif에 대한 설명

널 및 외래 키

관계형 모델은 외래 키 값이 참조된 기본 키 값이나 고유 키 값 또는 널과 일치하도록 허용합니다. 예를 들어, hr.employees의 행은 부서 ID를 지정하지 않을 수 있습니다.

조합 Foreign Key의 열이 null인 경우 키의 null이 아닌 부분은 상위 키의 해당 부분과 일치하지 않아도 됩니다. 예를 들어, reservations 테이블은 table_id 및 date 열에 복합 외래 키를 포함할 수 있지만 table_id는 널입니다.

상위 키 수정 및 외래 키

외래 키와 상위 키 간의 관계는 상위 키 삭제와 관련이 있습니다. 예를 들어, 사용자가 이 부서의 레코드를 삭제하려고 하면 이 부서의 사원에 대한 레코드는 어떻게 됩니까?

상위 키가 수정되면 참조 무결성 제약 조건은 하위 테이블의 종속 행에 대해 수행할 다음 작업을 지정할 수 있습니다.

표 7-3에서는 상위 테이블의 키 값에 대한 여러 참조 작업에서 허용하는 DML 문과 하위 테이블의 외래 키 값에 대해 간략히 설명합니다.

테이블 3 업데이트 및 삭제 작업에서 허용되는 DML 문

DML 명령문 상위 테이블에 대해 발행됨 하위 테이블에 대해 발행됨
INSERT 상위 키 값이 고유한 경우 항상 확인 외래 키 값이 상위 키에 있거나 일부 또는 모두 null인 경우에만 확인
UPDATE NO ACTION 명령문이 참조된 상위 키 값 없이 하위 테이블의 행을 남기지 않는 경우 허용됩니다. 새 외래 키 값이 여전히 참조된 키 값을 참조하는 경우 허용됩니다.
DELETE NO ACTION 하위 테이블에 상위 키 값을 참조하는 행이 없는 경우 허용됩니다. 항상 확인
DELETE CASCADE 항상 확인 항상 확인
DELETE SET NULL 항상 확인 항상 확인

주: Oracle Database의 FOREIGN KEY 무결성 제약 조건에서 지원하지 않는 기타 참조 작업은 데이터베이스 트리거를 사용하여 적용할 수 있습니다. "트리거 개요"를 참조하십시오.

참조: ON DELETE 절에 대해 알아보려면 Oracle AI Database SQL Language Reference를 참조하십시오.

인덱스 및 Foreign Key

일반적으로 외래 키는 인덱스화되어야 합니다. 유일한 예외는 일치하는 고유 키 또는 기본 키가 업데이트되거나 삭제되지 않는 경우입니다.

하위 테이블의 외래 키를 인덱스화하면 다음과 같은 이점이 있습니다.

참조:

검사 제약 조건

열 또는 열 집합의 check constraint를 사용하려면 모든 행에 대해 지정된 조건이 true 또는 unknown이어야 합니다.

DML로 인해 제약 조건의 조건이 false로 평가되는 경우 SQL 문이 롤백됩니다. Check 제약 조건의 주요 이점은 매우 구체적인 무결성 규칙을 시행할 수 있다는 것입니다. 예를 들어, check 제약 조건을 사용하여 hr.employees 테이블에서 다음 규칙을 적용할 수 있습니다.

다음 예제는 employees에 최대 급여 제약 조건을 생성하고 명령문이 최대값을 초과하는 급여를 포함하는 행을 삽입하려고 시도할 때 발생하는 결과를 보여줍니다.

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);
SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)
  1  VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

단일 열은 해당 정의에서 열을 참조하는 여러 개의 check 제약 조건을 가질 수 있습니다. 예를 들어, salary 열에는 10000보다 큰 값을 방지하는 하나의 제약 조건과 500보다 작은 값을 방지하는 별도의 제약 조건이 있을 수 있습니다.

열에 대해 여러 개의 Check 제약 조건이 있는 경우 해당 제약 조건이 충돌하지 않도록 설계해야 합니다. 조건 평가 순서는 가정할 수 없습니다. 데이터베이스는 검사 조건이 함께 사용될 수 없는지 확인하지 않습니다.

참조: Oracle AI Database SQL Language Reference에서 Check 제약 조건에 대한 제한 사항 알아보기

사전 검사 가능한 CHECK 제약 조건

PRECHECK로 표시된 check 제약 조건은 데이터베이스 외부에서 검사할 수 있습니다.

check 제약 조건을 PRECHECK 상태로 설정하면 테이블을 생성하거나 변경하는 동안 수행됩니다. PRECHECK 상태는 제약 조건의 ENABLE 및 VALIDATE 상태를 보완하며 세 상태를 모두 동시에 활성화할 수 있는 제약 조건을 보완합니다.

JSON을 사용하여 검사 제약 조건을 표현하는 경우 Oracle AI Database를 사용하면 JSON 스키마를 익스포트하고 애플리케이션 클라이언트 내의 JSON 스키마 검증기에 대해 JSON 데이터를 검증할 수 있습니다. 제약 조건이 PRECHECK 상태로 설정된 경우 제약 조건에 제약 조건의 의미가 보존되는 동등한 JSON 스키마가 있음을 나타냅니다. 클라이언트 응용 프로그램 개발자는 클라이언트 응용 프로그램 내에서 제약 조건을 미리 검증할 수 있습니다. 데이터베이스는 제약 조건을 검사하고 JSON 스키마에서 제약 조건을 표현할 수 없는 경우 오류가 생성됩니다.

관련 항목

검증

검증은 응용 프로그램의 데이터베이스 테이블에 대한 subquery를 포함하는 부울 표현식입니다.

검증에는 제약 조건의 의미가 있습니다. 즉, 트랜잭션이 데이터를 변경하고 이러한 변경 사항을 커밋하는 동안 데이터베이스는 이러한 부울 표현식의 진실성을 보장해야 합니다.

다음 SQL 문은 NO_TRAINERS_IN_BOSTON라는 검증을 생성하여 보스턴 기반 부서에 고용된 트레이너가 없도록 요구합니다. 최상위 레벨 NOT EXISTS는 EMP 테이블과 DEPT 테이블 간의 조인 결과 집합이 비어 있도록 요구하는 부울 표현식을 나타냅니다.

CREATE ASSERTION no_trainers_in_boston CHECK
(NOT EXISTS
   (SELECT 'trainer in Boston'
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
      AND  e.job = 'TRAINER'
      AND  d.loc = 'BOSTON'));

검증은 스키마 레벨 객체입니다. 즉, 다른 스키마의 테이블이 부울 표현식에서 참조되는 경우 해당 테이블에 액세스하려면 ASSERTION REFERENCES 객체 권한이 필요합니다. 이는 두 스키마에 걸쳐 있는 외래 키와 다르지 않습니다. 즉, 다른 스키마에 있는 테이블의 기본 키를 "점"하는 외래 키를 성공적으로 생성하려면 REFERENCES 객체 권한이 있어야 합니다.

검증은 일반 데이터 무결성 제약 조건입니다. CHECK, PRIMARY KEY, UNIQUE 및 FOREIGN KEY 제약 조건을 대체하는 데 사용될 수 있습니다.

예를 들어, EMP 테이블에서 다음 제약 조건을 가정합니다.

이러한 제약 조건은 다음 검증으로 구현될 수 있습니다.

CREATE ASSERTION commission_not_greater_than_salary CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  e.comm > e.sal));
CREATE ASSERTION emp_empno_pk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.empno = e2.empno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_ename_deptno_uk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.ename = e2.ename
     AND  e1.deptno = e2.deptno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_fk_dept CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  NOT EXISTS
     (SELECT ''
      FROM   dept d
      WHERE  d.deptno = e.deptno)));

그러나 가능한 한 항상 제약 조건을 항상 사용하고 제약 조건을 사용할 수 없는 경우에만 검증을 사용하도록 강력하게 권고합니다.

관련 항목

무결성 제약 조건 및 검증 상태

제약 조건 또는 검증 정의의 일부로, Oracle AI Database가 제약 조건 또는 검증을 강제 적용하는 방법과 시기를 지정하여 제약 조건 또는 검증 상태를 결정할 수 있습니다.

수정된 데이터와 기존 데이터 확인

데이터베이스를 사용하여 제약 조건 또는 검증이 기존 데이터에 적용되는지 아니면 미래 데이터에 적용되는지를 지정할 수 있습니다. 제약 조건 또는 검증이 활성화된 경우 데이터베이스는 입력 또는 갱신될 때 새 데이터를 검사합니다. 제약 조건 또는 검증을 준수하지 않는 데이터는 데이터베이스에 들어갈 수 없습니다.

예를 들어, employees.department_id에서 NOT NULL 제약 조건을 활성화하면 모든 미래 행에 부서 ID가 지정됩니다. 제약 조건이 비활성화된 경우 테이블은 제약 조건을 위반하는 행을 포함할 수 있습니다.

제약 조건 및 검증을 다음 검증 모드 중 하나로 설정할 수 있습니다.

VALIDATE 및 NOVALIDATE의 동작은 항상 제약 조건 또는 검증이 사용으로 설정되었는지 아니면 사용 안함으로 설정되었는지에 따라 다릅니다. 다음 표에서는 관계를 요약합니다.

표 4 수정된 데이터와 기존 데이터에 대한 검사

수정된 데이터 기존 데이타 요약
ENABLE VALIDATE 기존 데이터와 미래 데이터는 제약 조건 또는 검증을 준수해야 합니다. 채워진 테이블에 새 제약 조건 또는 검증을 적용하려고 시도하면 기존 행이 제약 조건 또는 검증을 위반하는 경우 오류가 발생합니다.
ENABLE NOVALIDATE 데이터베이스는 제약 조건 또는 검증을 검사하지만 모든 행에 대해 true일 필요는 없습니다. 따라서 기존 행은 제약 조건이나 검증을 위반할 수 있지만 새 행이나 수정된 행은 규칙을 따라야 합니다. 이 모드는 무결성이 이미 확인된 기존 데이터가 포함된 데이터 웨어하우스에서 자주 사용됩니다.
DISABLE VALIDATE 데이터베이스는 제약 조건을 비활성화하고 인덱스를 삭제하며 제약 조건이 있는 열의 수정을 방지합니다. 검증이 이 상태일 수 없습니다.
DISABLE NOVALIDATE 제약 조건 또는 검증은 검사되지 않으며 반드시 사실일 필요는 없습니다.

참조: Oracle AI Database SQL Language Reference에서 제약 조건 상태에 대해 알아보기

데이터베이스에서 제약 조건 및 검증의 유효성을 검사하는 경우

모든 제약 조건 및 검증은 지연 불가능(기본값) 또는 지연 가능 상태입니다. 이 상태는 Oracle AI Database가 제약 조건 또는 검증의 유효성을 검사하는 시기를 결정합니다.

다음 그림은 지연 가능한 제약 조건 및 검증에 대한 옵션을 보여줍니다.

그림 3: 지연 가능한 제약 조건 및 검증 옵션

그림 cncpt313.gif에 대한 설명

Nondeferrable 제약 조건 및 검증

Nondeferrable Constraint에서 Oracle AI Database는 제약 조건 또는 검증의 유효성 검사를 트랜잭션 끝까지 지연시키지 않습니다. 대신 데이터베이스는 각 명령문의 끝에 있는 제약 조건이나 검증을 검사합니다. 제약 조건 또는 검증을 위반하면 명령문이 롤백됩니다.

예를 들어, employees.last_name 열에 대해 Nondeferrable NOT NULL 제약 조건이 있습니다. 세션에서 성이 없는 행을 삽입하려고 하면 NOT NULL 제약 조건을 위반했기 때문에 데이터베이스가 명령문을 즉시 롤백합니다. 행이 삽입되지 않았습니다.

Deferrable 제약 조건 및 검증

지연 가능한 제약 조건 또는 검증은 트랜잭션이 SET CONSTRAINT 절을 사용하여 COMMIT 문이 실행될 때까지 이 제약 조건 또는 검증의 검사를 지연하도록 허용합니다. 제약 조건이나 검증을 위반할 수 있는 데이터베이스를 변경하는 경우 이 설정을 사용하면 모든 변경이 완료될 때까지 제약 조건이나 검증을 효과적으로 비활성화할 수 있습니다.

데이터베이스가 deferrable 제약 조건 또는 검증을 검사하는 경우의 기본 동작을 설정할 수 있습니다. 다음 속성 중 하나를 지정할 수 있습니다.

employees.last_name에서 지연 가능한 NOT NULL 제약 조건이 INITIALLY DEFERRED로 설정되어 있다고 가정합니다. 유저는 100개의 INSERT 문을 사용하여 트랜잭션을 생성하고, 그 중 일부는 last_name에 대해 null 값을 가집니다. 유저가 커밋을 시도하면 데이터베이스는 100개의 모든 명령문을 롤백합니다. 하지만 이 제약 조건이 INITIALLY IMMEDIATE로 설정된 경우 데이터베이스는 트랜잭션을 롤백하지 않습니다.

제약 조건으로 인해 action이 발생할 경우 데이터베이스는 이 작업을 발생시킨 명령문의 일부로, 제약 조건이 지연되는지 아니면 즉시 실행되는지 고려합니다. 예를 들어, departments에서 행을 삭제하면 삭제된 부서 행을 참조하는 employees의 모든 행이 삭제됩니다. 이 경우 employees에서 삭제는 departments에 대해 실행된 DELETE 문의 일부로 간주됩니다.

참조: 제약 조건 속성 및 해당 기본값에 대한 자세한 내용은 Oracle AI Database SQL Language Reference

제약 조건 및 검증 검사 예제

다음 예제는 Oracle AI Database가 제약 조건 및 검증 검사를 수행하는 시기를 보여줍니다.

다음을 가정합니다.

예: 상위 키 값이 없는 경우 외래 키 열에 값 삽입

이 예제는 employees 테이블에 첫번째 행을 삽입하는 것과 관련됩니다. 현재 행이 없으므로 manager_id 열의 값이 employee_id 열의 기존 값을 참조할 수 없는 경우 행을 입력하려면 어떻게 해야 합니까?

몇 가지 가능성은 다음과 같습니다.

기본값은 명령문이 구문 분석되기 전에 INSERT 문의 일부로 포함됩니다. 따라서 기본 열 값은 모든 무결성 제약 조건 검사에 적용됩니다.

예: 모든 외래 키 및 상위 키 값 업데이트

이 예제에서 자체 참조 제약 조건은 employee_id 열의 값에 따라 employees의 manager_id 열에 있는 항목을 만듭니다.

회사는 판매되었습니다. 이 판매로 인해 모든 직원 번호를 현재 값에 5000을 더한 값으로 업데이트하여 새 회사의 직원 번호와 조정해야 합니다. 다음 그림에 나오는 것처럼 일부 사원은 관리자이기도 합니다.

그림 4: 업데이트 전 직원 표

그림 cncpt058.gif에 대한 설명

관리자 번호도 사원 번호이므로 관리자 번호도 5000씩 증가해야 합니다. 다음 SQL 문을 실행하여 값을 갱신할 수 있습니다.

UPDATE employees SET employee_id = employee_id + 5000,
  manager_id = manager_id + 5000;

각 manager_id 값이 employee_id 값과 일치하는지 확인하기 위해 제약 조건이 정의되어 있더라도 데이터베이스는 명령문이 완료된 후 제약 조건을 효과적으로 검사하므로 앞의 명령문은 유효합니다. 그림 7-5는 제약 조건을 검사하기 전에 데이터베이스가 전체 SQL 문의 작업을 수행함을 보여줍니다.

그림 5: 제약 조건 검사

그림 cncpt059.gif에 대한 설명

이 섹션의 예에서는 INSERT 및 UPDATE 문 중 제약 조건 검사 방식을 보여 주지만 데이터베이스는 모든 유형의 DML 문에 대해 동일한 방식을 사용합니다. 데이터베이스는 자체 참조 제약 조건뿐만 아니라 모든 유형의 제약 조건에 대해 동일한 방식을 사용합니다. 주: 뷰 또는 동의어에 대한 작업은 기본 테이블에 정의된 무결성 제약 조건을 따릅니다.

예제: 모든 관리자는 동일한 부서에 담당 직원이 있어야 함

이 예제는 모든 관리자가 동일한 부서에 직원을 두고 있는지 확인하는 검증을 생성합니다.

이 예제에서는 먼저 담당자를 삽입한 다음 관리자를 삽입해야 합니다. 제약 조건과 마찬가지로 여러 행 삽입을 실행할 때 각 INSERT 문을 실행한 후 검증이 검증됩니다. 이 검증을 DEFERRABLE로 설정하면 먼저 관리자를 삽입한 다음 담당자를 삽입할 수 있습니다. 검증은 각 INSERT 문 대신 COMMIT에서 수행됩니다.

CREATE ASSERTION manager_has_clerk check
(NOT EXISTS
  (SELECT 'manager without clerk'
   FROM   employees m
   WHERE  m.job_id = 'ST_MAN'
     AND  NOT EXISTS
            (SELECT 'a clerk in same department'
             FROM   employees c
             WHERE  c.job_id = 'ST_CLERK'
               AND  c.department_id = m.department_id)));