외부 키 제약 조건을 비활성화하지 않고 트랜잭션 내에서 참조 무결성을 잠시 중단하려면 어떻게 해야 합니까?
3개의 열이 있는 테이블이 있습니다.
ID, PARENT_ID, NAME
PARENT_ID는 와외키관있습다니가계부다▁key와 외부 키 있습니다.ID입니다.이 테이블은 계층 구조를 모델링하고 있습니다.
은 은끔가은.ID기록이 변경됩니다.레코드를 업데이트할 수 있습니다.ID 종속 레코드의 합니다.PARENT_ID 새운것가다키리를 ID.
문제는, 제가 업데이트를 시도할 때ID기록의 무결성을 깨뜨리고 즉시 실패합니다.
새 레코드를 새 레코드에 삽입할 수 있다는 것을 깨달았습니다.ID그런 다음 아이들을 업데이트하고 이전 기록을 삭제합니다. 하지만 제가 그렇게 하면 엉망이 될 트리거가 많이 있습니다.
외부 키를 잠시 비활성화하지 않고 자녀 업데이트 약속(분명히 커밋 시 실패함)으로 일시적으로 부모를 업데이트할 수 있는 방법이 있습니까?
당신이 원하는 것은 '지연된 제약'입니다.
지연 가능한 제약 조건의 두 가지 유형인 'INITIALIMITED'와 'INITIALISTED' 중에서 기본 동작을 수행할 수 있습니다. 데이터베이스가 모든 문 이후에 제약 조건을 검사하도록 기본 설정해야 하는지 아니면 트랜잭션 끝에 제약 조건만 검사하도록 기본 설정해야 하는지 여부를 선택할 수 있습니다.
Chi보다 답변이 느리지만 SO에서 답을 찾을 수 있도록 코드 샘플을 포함하면 좋겠다고 느꼈습니다.
Chi가 대답했듯이, 지연 가능한 제약이 이것을 가능하게 합니다.
SQL> drop table t;
Table dropped.
SQL> create table T (ID number
2 , parent_ID number null
3 , name varchar2(40) not null
4 , constraint T_PK primary key (ID)
5 , constraint T_HIREARCHY_FK foreign key (parent_ID)
6 references T(ID) deferrable initially immediate);
Table created.
SQL> insert into T values (1, null, 'Big Boss');
1 row created.
SQL> insert into T values (2, 1, 'Worker Bee');
1 row created.
SQL> commit;
Commit complete.
SQL> -- Since initially immediate, the following statement will fail:
SQL> update T
2 set ID = 1000
3 where ID = 1;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint (S.T_HIREARCHY_FK) violated - child record found
SQL> set constraints all deferred;
Constraint set.
SQL> update T
2 set ID = 1000
3 where ID = 1;
1 row updated.
SQL> update T
2 set parent_ID = 1000
3 where parent_ID = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from T;
ID PARENT_ID NAME
---------- ---------- ----------------------------------------
1000 Big Boss
2 1000 Worker Bee
SQL> -- set constraints all deferred during that transaction
SQL> -- and the transaction has commited, the next
SQL> -- statement will fail
SQL> update T
2 set ID = 1
3 where ID = 1000;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint S.T_HIREARCHY_FK) violated - child record found
저는 지연 가능성이 제약 조건 생성 시 정의되며 나중에 수정할 수 없다고 생각하지만 참조를 찾을 수 없습니다.기본값은 연기할 수 없습니다.연기 가능한 제약 조건으로 변경하려면 한 번의 드롭을 수행하고 제약 조건을 추가해야 합니다. (적절한 예약, 제어 등)
SQL> drop table t;
Table dropped.
SQL> create table T (ID number
2 , parent_ID number null
3 , name varchar2(40) not null
4 , constraint T_PK primary key (ID)
5 , constraint T_HIREARCHY_FK foreign key (parent_ID)
6 references T(ID));
Table created.
SQL> alter table T drop constraint T_HIREARCHY_FK;
Table altered.
SQL> alter table T add constraint T_HIREARCHY_FK foreign key (parent_ID)
2 references T(ID) deferrable initially deferred;
Table altered.
이와 같은 시나리오에서 일반적인 조언은 연기 가능한 제약 조건을 사용하는 것입니다.그러나 이러한 상황은 거의 항상 애플리케이션 로직 또는 데이터 모델의 실패라고 생각합니다.예를 들어 자식 레코드와 부모 레코드를 동일한 트랜잭션에 삽입하는 것은 다음 두 가지 명령문으로 실행할 경우 문제가 될 수 있습니다.
내 테스트 데이터:
SQL> select * from t23 order by id, parent_id
2 /
ID PARENT_ID NAME
---------- ---------- ------------------------------
110 parent 1
111 parent 2
210 110 child 0
220 111 child 1
221 111 child 2
222 111 child 3
6 rows selected.
SQL>
잘못된 방법:
SQL> insert into t23 (id, parent_id, name) values (444, 333, 'new child')
2 /
insert into t23 (id, parent_id, name) values (444, 333, 'new child')
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found
SQL> insert into t23 (id, parent_id, name) values (333, null, 'new parent')
2 /
1 row created.
SQL>
그러나 Oracle은 상위 및 하위 레코드를 동일한 문에 삽입할 수 있는 다중 테이블 INSERT synatx를 지원하므로 연기 가능한 제약 조건이 필요하지 않습니다.
SQL> rollback
2 /
Rollback complete.
SQL> insert all
2 into t23 (id, parent_id, name)
3 values (child_id, parent_id, child_name)
4 into t23 (id, name)
5 values (parent_id, parent_name)
6 select 333 as parent_id
7 , 'new parent' as parent_name
8 , 444 as child_id
9 , 'new child' as child_name
10 from dual
11 /
2 rows created.
SQL>
부모 레코드의 기본 키를 업데이트하고 싶지만 자식 레코드가 있기 때문에 업데이트할 수 없습니다.그리고 부모 키가 없기 때문에 자녀 기록을 업데이트할 수 없습니다.캐치-22:
SQL> update t23
2 set id = 555
3 where id = 111
4 /
update t23
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.T23_T23_FK) violated - child record found
SQL> update t23
2 set parent_id = 555
3 where parent_id = 111
4 /
update t23
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found
SQL>
다시 한 번, 솔루션은 다음과 같은 단일 문으로 이를 수행하는 것입니다.
SQL> update t23
2 set id = decode(id, 111, 555, id)
3 , parent_id = decode(parent_id, 111, 555, parent_id)
4 where id = 111
5 or parent_id = 111
6 /
4 rows updated.
SQL> select * from t23 order by id, parent_id
2 /
ID PARENT_ID NAME
---------- ---------- ------------------------------
110 parent 1
210 110 child 0
220 555 child 1
221 555 child 2
222 555 child 3
333 new parent
444 333 new child
555 parent 2
8 rows selected.
SQL>
UPDATE 문의 구문은 약간 투박하지만 보통 투박합니다.요점은 기본 키 열을 자주 업데이트할 필요가 없다는 것입니다.실제로 불변성은 "기본 키"의 특성 중 하나이므로 실제로 업데이트할 필요가 없습니다.그렇게 할 필요가 있는 것은 데이터 모델의 실패입니다.이러한 실패를 방지하는 한 가지 방법은 가상(대리) 기본 키를 사용하고 고유한 제약 조건으로 자연(일명 비즈니스) 키의 고유성을 적용하는 것입니다.
그렇다면 Oracle이 연기 가능한 제약 조건을 제공하는 이유는 무엇입니까?데이터 마이그레이션이나 대량 데이터 업로드를 수행할 때 유용합니다.테이블을 준비하지 않고 데이터베이스의 데이터를 정리할 수 있습니다.일반적인 애플리케이션 작업에는 이러한 기능이 필요하지 않습니다.
대리 키를 사용하는 것이 좋습니다, IMO.
일반적으로 이 테이블의 문제는 기본 키가 없다는 것입니다.기본 키는 다음 세 가지여야 합니다.
- 독특한
- null이 아닌
- 불변의
제가 아는 데이터베이스는 (1)과 (2)를 시행하지만 (3)을 시행하지는 않을 것으로 생각합니다. 불행한 일입니다.그리고 그것이 바로 여러분의 엉덩이를 걷어차는 것입니다. 만약 여러분이 "기본 키"를 바꾼다면, 여러분은 그 키 필드에 대한 모든 참조를 추적하고 무결성을 깨지 않으려면 동등한 변경을 해야 합니다.다른 사람들이 말했듯이 해결책은 고유하고 null이 아니며 변경되지 않는 진정한 기본 키를 갖는 것입니다.
이 모든 작은 규칙들에는 이유가 있습니다.이것은 기본 키 규칙의 "변하지 않는" 부분을 이해할 수 있는 좋은 기회입니다.
공유하고 즐기세요.
Oracle 이외의 다른 데이터베이스일 경우 외부 키를 다음과 같이 선언할 수 있습니다.ON UPDATE CASCADE그런 다음 부모 ID를 변경하면 자녀의 parent_id에 변경 내용이 원자적으로 전파됩니다.
안타깝게도 Oracle은 계단식 삭제를 구현하지만 계단식 업데이트는 구현하지 않습니다.
(이 답변은 실제로 문제를 해결하지 않으므로 정보 제공용으로만 제공됩니다.)
지연 가능한 제약 조건을 사용해야 합니다(Chi의 답변 참조).
그렇지 않으면 외부 키 제약 조건을 충족하지 못하는 값을 추가하려면 외부 키 제약 조건을 사용하지 않도록 설정하거나 삭제한 후 다시 만들어야 합니다.
이러한 상황에서는 참조 무결성에 영향을 주지 않고 필요에 따라 사용자가 변경할 수 있는 대리 키를 사용합니다.이 아이디어를 확장하기 위해 현재 설정은 다음과 같습니다.
- 아이디(pk)
- PARENT_ID(외래 키, 참조 ID 열 -- 자체 참조)
..비즈니스 규칙은 ID를 변경할 수 있다는 것입니다.기본 키는 불변이고 고유하며 null일 수 없습니다.따라서 데이터 모델을 구축할 때의 상황에 대한 해결책은 다음과 같습니다.
- 아이디(pk)
- PARENT_ID(외래 키, 참조 ID 열 -- 자체 참조)
- SERGATORY_KEY(고유 제약 조건)
SERGATION_KEY는 참조 무결성에 영향을 주지 않고 변경을 지원하는 열입니다. 상위 및 하위 관계는 그대로 유지됩니다.즉, 사용자는 지연된 제약 조건 없이 대리 키를 사용자의 마음에 맞게 조정할 수 있으며, 외래 키 제약 조건을 활성화/비활성화하거나 삭제/재작성할 수 있습니다. ON UPDATE CASCADE...
일반적으로 데이터 모델링에서는 이러한 상황 때문에 사용자에게 기본 키 값을 표시하지 않습니다.예를 들어, 한 고객이 자신의 일자리 번호를 연도의 시작과 함께 변경하기를 원합니다(IE: 201000001은 2010년에 처음 만들어진 일자리입니다).고객이 회사를 매각하고 새 소유자가 회계 처리를 위해 다른 계획을 필요로 하면 어떻게 됩니까?또는 다른 데이터베이스 공급업체로 전환하는 동안 번호를 유지할 수 없는 경우에는 어떻게 해야 합니까?
언급URL : https://stackoverflow.com/questions/3105730/how-can-i-break-referential-integrity-briefly-within-a-transaction-without-dis
'it-source' 카테고리의 다른 글
| Vue Router + Vuex: 각 경로 가드가 Vuex 상태 변경을 대기하기 전에 만드는 방법은 무엇입니까? (0) | 2023.07.10 |
|---|---|
| 쿠베르네테스에서 마리아DB를 폭로하는 방법은? (0) | 2023.07.10 |
| 내장된 Tomcat 서버에 context.xml 파일을 추가하는 방법 (0) | 2023.07.10 |
| 합이 상수 K - Excel인 N개의 난수 생성 (0) | 2023.07.10 |
| 3.2의 MongoDB 덤프, 3.4로 복원, 오류 인덱스 safe = null (0) | 2023.07.10 |