[sql] 지연 불가 대 초기 즉시 지연 가능

Program / 글쓴이

Database Systems-The Complete BookDEFERRABLE 에서 SQL 키워드 에 대해 읽었습니다 .

후자의 [NOT DEFERRABLE] 이 기본값이며 데이터베이스 수정 명령문이 실행될 때마다 수정이 외래 키 제약 조건을 위반할 수있는 경우 제약 조건이 즉시 확인됩니다.

그러나 제약 조건을 DEFERRABLE로 선언하면 제약 조건을 확인하기 전에 트랜잭션이 완료 될 때까지 기다리는 옵션이 있습니다.

INITIALLY DEFERRED 또는 INITIALLY IMMEDIATE에 의해 DEFERRABLE 키워드를 따릅니다 . 전자의 경우 각 트랜잭션이 커밋되기 직전에 검사가 연기됩니다. 후자의 경우 각 진술 직후에 확인됩니다.

와 어떻게 NOT DEFERRABLE다른 DEFERRABLE INITIALLY IMMEDIATE가요? 두 경우 모두 각 개별 문 후에 제약 조건이 확인되는 것 같습니다.

답변

으로 DEFERRABLE INITIALLY IMMEDIATE당신이 그것을 필요로 할 때 필요에 따라 제약 조건을 지연시킬 수 있습니다.

이는 일반적으로 명령문 시간에 제약 조건을 확인하려는 경우 유용하지만 예를 들어 일괄로드가 커밋 시간까지 확인을 연기하려는 경우에 유용합니다.

제약 조건을 연기하는 방법은 다양한 DBMS에 따라 다릅니다.

함께 NOT DEFERRABLE혹시 시간이 커밋 될 때까지 검사를 연기 할 수 없을 것입니다.

답변

다른 (정확한) 답변 외에도 PostgreSQL에 대해 말할 때 다음과 같이 명시해야합니다.

  • 와 NOT DEFERRABLE 각 행은 삽입 / 업데이트시 체크

  • 와 DEFERRABLE (현재 IMMEDIATE ) 모든 행은 삽입 / 업데이트의 마지막에 체크

  • 와 DEFERRABLE (현재 DEFERRED ) 모든 행이 트랜잭션의 끝에서 확인됩니다

따라서 DEFERRABLE 제약 조건이 IMMEDIATE로 설정 될 때 NOT DEFERRABLE 제약 조건처럼 작동한다고 말하는 것은 올바르지 않습니다.

이 차이점에 대해 자세히 설명하겠습니다.

CREATE TABLE example(
    row integer NOT NULL,
    col integer NOT NULL,
    UNIQUE (row, col) DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO example (row, col) VALUES (1,1),(2,2),(3,3);

UPDATE example SET row = row + 1, col = col + 1;

SELECT * FROM example;

그러면 다음이 올바르게 출력됩니다.

산출

그러나 DEFERRABLE INITIALLY IMMEDIATE 명령을 제거하면

오류 : 중복 키 값이 “example_row_col_key”고유 제약 조건을 위반합니다. DETAIL : 키 ( “row”, col) = (2, 2)가 이미 있습니다. ********** 오류 **********

오류 : 중복 키 값이 고유 제약 조건 “example_row_col_key”를 위반합니다. SQL 상태 : 23505 세부 정보 : 키 ( “row”, col) = (2, 2)가 이미 있습니다.

부록 (2017 년 10 월 12 일)

이 동작은 실제로 여기 “호환성”섹션에 설명되어 있습니다 .

또한 PostgreSQL은 표준이 제안하는 것처럼 명령문의 끝이 아니라 지연 불가능한 고유성 제약 조건을 즉시 확인합니다.

답변

연기 할 수 있다는 것이 명백한 것 외에도 차이점은 실제로 성능입니다. 성능 저하가 없다면 지연 가능 여부를 선택할 수있는 옵션이 필요하지 않습니다. 모든 제약은 단순히 지연 가능할 것입니다.

성능 패널티는 데이터가 제한되는 방식에 대한 지식이 주어지면 데이터베이스가 수행 할 수있는 최적화와 관련이 있습니다. 예를 들어, Oracle에서 고유 한 제약 조건을 뒷받침하기 위해 생성 된 인덱스는 제약 조건이 연기 가능한 경우 일시적으로 중복을 허용해야하므로 고유 인덱스가 될 수 없습니다. 그러나 제약 조건이 지연 가능하지 않은 경우 인덱스는 고유 할 수 있습니다.

답변

나는 파티에 매우 늦었지만 2018 년 12 월 현재 내가 아는 두 개의 데이터베이스 (더있을 수 있음)만이이 표준 SQL 기능 의 어느 정도 구현 수준을 제공한다는 사실을 추가하고 싶었습니다 .

Database    NOT DEFERRABLE  DEFERRABLE           DEFERRABLE
                            INITIALLY IMMEDIATE  INITIALLY DEFERRED
----------  --------------  -------------------  ------------------
Oracle      N/A *1          Yes (default)        Yes
PostgreSQL  Yes (default)   Yes                  Yes
DB2         -               -                    -
SQL Server  -               -                    -
MySQL       -               -                    -
MariaDB     -               -                    -
SAP Sybase  -               -                    -
HyperSQL    -               -                    -
H2          -               -                    -
Derby       -               -                    -

* 1 Oracle 12c가 NOT DEFERRABLE 제약 조건을 수락하더라도 실제로는이를 무시하고 다음과 같이 작동합니다 DEFERRABLE INITIALLY IMMEDIATE.

보시다시피 Oracle은 첫 번째 유형 ( NOT DEFERRABLE)을 구현하지 않으므로 Oracle (이 경우 OP)을 사용하는 개발자가 혼란스러워하고 처음 두 유형을 동등한 것으로 간주 할 수 있습니다.

흥미롭게도 Oracle과 PostgreSQL은 기본 유형이 다릅니다. 성능에 영향을 미칠 수 있습니다.

답변

NOT DEFERRABLE-제약 조건 검사를 변경할 수 없습니다. 오라클은 각 문 다음에 (즉, 삽입 문 바로 뒤에)이를 확인합니다.

DEFERRABLE INITIALLY IMMEDIATE-오라클은 각 문 다음에 제약 조건을 확인합니다. 그러나 각 트랜잭션 후 (즉, 커밋 후)로 변경할 수 있습니다.

set constraint pk_tab1 deferred;

답변