È possibile (o desiderabile, se non lo è) fare in modo che un motore di database organizzi l'ordine delle istruzioni SQL per mantenere l'integrità referenziale?

Espandendo la risposta di BillThor, almeno in Oracle, è possibile creare vincoli rinviabili che vengono convalidati al momento del commit e quindi passare liberamente tra l'aver convalidato immediatamente il vincolo e averlo convalidato al momento del commit.

Ad esempio, posso creare una tabella padre, una tabella figlio con un vincolo rinviabile, inserire una riga nel figlio che fa riferimento a una riga padre che non esiste e non ricevo un errore fino a quando non impegno.

SQL> create table parent (
  2    parent_id number primary key,
  3    parent_name varchar2(100)
  4  );

Table created.

SQL> create table child (
  2    child_id number primary key,
  3    parent_id number references parent(parent_id)
  4                     initially deferred deferrable,
  5    child_name varchar2(100)
  6  );

Table created.

SQL> insert into child values( 10, 1, 'Child 1' );

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.SYS_C0017089) violated - parent key not
found

Se creo il genitore dopo aver creato il figlio all'interno della stessa transazione, il commit ha successo

SQL> insert into child values( 10, 1, 'Child 1' );

1 row created.

SQL> insert into parent values( 1, 'Parent 1' );

1 row created.

SQL> commit;

Commit complete.

Se voglio che il vincolo venga immediatamente applicato, posso farlo anche

SQL> ALTER SESSION SET CONSTRAINTS = IMMEDIATE;

Session altered.

SQL> insert into child values( 20, 2, 'Child 2' );
insert into child values( 20, 2, 'Child 2' )
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0017089) violated - parent key not
found

oppure posso reimpostare il vincolo per essere deferito con il comando

ALTER SESSION SET CONSTRAINTS = DEFERRED

Mi aspetto che altri database abbiano funzionalità simili.

Semplicemente non funziona in questo modo ... All'avvio di una transazione, in realtà vengono eliminate. In caso di rollback, annulla le eliminazioni leggendo il log. L'intero meccanismo di questo dovrebbe cambiare per fare quello che stai chiedendo. Dovrebbe simulare l'intera transazione, quindi verificare l'integrità, quindi eseguire la transazione (tenendo bloccati tutti i record effettuati per tutto il tempo)

Se vuoi eliminare automaticamente tutti i bambini, usa eliminato in sequenza. Questa è l'unica volta in cui eliminerai il genitore prima dei bambini.

Il database non può riordinare il passaggio della transazione mentre li riceve in ordine. Non sa che hai intenzione di eliminare da B quando hai richiesto le eliminazioni da A. Esistono comunque due approcci per la gestione dell'integrità referenziale all'interno di una transazione. L'operazione Delete Cascade potrebbe risolvere il tuo problema, ma preferisco limitarne l'uso.

Esistono due approcci alla convalida:

• Convalida immediata: verifica l'integrità con ogni azione eseguita. Ciò richiede che i record vengano aggiunti nell'ordine corretto. Questa sembra essere l'impostazione predefinita per la maggior parte dei database.

• Convalida differita: verifica l'integrità dei dati al momento del commit dei dati. Ciò consente di aggiungere record in qualsiasi ordine. Nelle transazioni di grandi dimensioni questo può rallentare i commit. Se si aggiungono set di dati con riferimenti circolari, è richiesta la convalida posticipata.

Alcuni (molti) database potrebbero non supportare entrambi gli approcci di validazione. Su alcuni database potrebbe essere possibile cambiare gli approcci di convalida su base per transazione.

Vuoi una discussione, ecco qua.

Immagina un utente in un sito Web che elabora un acquisto e invece di passare attraverso il processo graduale per riempire il carrello della spesa ed elaborare il pagamento e la spedizione, vogliono saltare le informazioni sull'indirizzo della carta di credito perché puoi semplicemente utilizzare la spedizione indirizzo una volta che lo riempiono alla fine. Chiedete prima di compilare l'indirizzo di fatturazione, ma hanno semplicemente digitato quel lungo numero di carta e hanno bisogno di una pausa. Non possono semplicemente saltare questo indirizzo e inserire un indirizzo più tardi? Puoi tornare indietro e ottenere quell'indirizzo. Fattura alla spedizione o spedizione alla fatturazione; importa davvero? Ho inserito un indirizzo, ovviamente si applica a entrambi.

I programmatori possono essere frustrati a lavorare con i database perché fanno cose al di fuori del nostro controllo. Per lo più soddisfano i nostri bisogni, ma c'è sempre una situazione in cui non obbedisce. Il tuo suggerimento è invece di usare le impostazioni e le funzionalità per fare esattamente quello che vuoi nel database, vuoi violare una semplice regola, ma fare in modo che il database risolva l'intera transazione solo per vedere se c'è un modo in cui potrebbe funzionare. Una volta esaurite tutte le opzioni, si ottiene un errore.

Se una transazione non funziona, non lo sapresti prima? È possibile controllare ciò che si invia al database, perché non sfruttarlo e programmarlo in accordo?

L'integrità referenziale è sufficiente per un problema di prestazioni, non è necessario agire in modo più intelligente per risolvere una violazione delle regole che potrebbe ottenere un passaggio se viene gestita all'interno di una transazione. O imposta il database per far rispettare le regole che desideri o non hai regole e lascia che sia la tua applicazione a gestirle.

Molti database possono ottimizzare e stabilire un piano sulla prima richiesta nella speranza di ottenere risultati migliori nelle richieste successive. È fantastico quando funziona. Quello che stai proponendo potrebbe complicarsi con più tabelle (cioè una situazione molti-a-molti) o transazioni nidificate. Alcune cose semplici potrebbero essere prepianificate, ma niente di troppo complesso. Juat è grato che elabora le transazioni nell'ordine in cui le hai inviate; ti sta salvando un incubo di debug.