PostgreSQL에서 외래 키 제약 조건을 사용하여 계단식 상위 삭제를 구현하는 방법은 무엇입니까?

외래 키 제약 조건을 사용한 계단식 상위 삭제

관계형 데이터베이스 시스템에서 하위 행을 삭제하면 상위 행을 삭제해야 하는 경우가 많습니다. 더 이상 다른 어린이가 참조하지 않습니다. 이 작업은 데이터 수정 CTE(공통 테이블 표현식)를 사용하여 PostgreSQL 버전 9.1 이상에서 수행할 수 있습니다.

데이터 수정 CTE 접근 방식

<code class="sql">WITH del_child AS (
    DELETE FROM child
    WHERE  child_id = 1
    RETURNING parent_id, child_id
    )
DELETE FROM parent p
USING  del_child x
WHERE  p.parent_id = x.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = x.parent_id
   AND    c.child_id <> x.child_id   -- !
   );</code>

이것은 CTE는 먼저 지정된 하위 행을 삭제합니다. 그런 다음 상위 ID와 삭제된 하위 ID를 반환합니다. 두 번째 DELETE 문은 이 정보를 사용하여 다른 하위 항목이 없는 경우 상위 행을 삭제합니다. 조건 c.child_id <> x.child_id는 삭제되는 항목 이외의 하위 항목만 고려되도록 보장합니다.

경합 조건 제거

동시 트랜잭션으로 인해 예상치 못한 결과가 발생할 수 있는 잠재적인 경합 조건을 방지합니다. , 삭제 프로세스 전에 상위 행을 잠글 수 있습니다. 이는 CTE의 FOR NO KEY UPDATE 절을 사용하여 상위 행을 잠그는 방식으로 수행됩니다.

<code class="sql">WITH lock_parent AS (
   SELECT p.parent_id, c.child_id
   FROM   child  c
   JOIN   parent p ON p.parent_id = c.parent_id
   WHERE  c.child_id = 12              -- provide child_id here once
   FOR    NO KEY UPDATE                -- locks parent row.
   )
 , del_child AS (
   DELETE FROM child c
   USING  lock_parent l
   WHERE  c.child_id = l.child_id
   )
DELETE FROM parent p
USING  lock_parent l
WHERE  p.parent_id = l.parent_id
AND    NOT EXISTS (
   SELECT FROM child c
   WHERE  c.parent_id = l.parent_id
   AND    c.child_id <> l.child_id   -- !
   );</code>

이 접근 방식은 위험한 DELETE 작업의 실행을 단일 트랜잭션으로 제한하여 동시 발생 가능성을 효과적으로 제거합니다. 간섭.

위 내용은 PostgreSQL에서 외래 키 제약 조건을 사용하여 계단식 상위 삭제를 구현하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!