Integrità dei dati

Questo capitolo spiega in che modo i vincoli di integrità applicano le regole business associate a un database e impediscono l'inserimento di informazioni non valide nelle tabelle.

Introduzione all'integrità dei dati

È importante che i dati mantengano l'integrità dei dati, ovvero il rispetto delle regole aziendali determinate dall'amministratore del database o dallo sviluppatore dell'applicazione.

Le regole business specificano condizioni e relazioni che devono essere sempre vere o false. Ad esempio, ogni azienda definisce le proprie politiche relative a stipendi, numeri di dipendenti, monitoraggio del magazzino e così via.

Tecniche per garantire l'integrità dei dati

Quando si progetta un'applicazione di database, gli sviluppatori hanno diverse opzioni per garantire l'integrità dei dati memorizzati nel database.

Di seguito sono riportate le opzioni disponibili.

Vedere anche:

Vantaggi dei vincoli di integrità

Un vincolo di integrità è un oggetto schema creato ed eliminato mediante SQL. Per applicare l'integrità dei dati, utilizzare vincoli di integrità o asserzioni quando possibile.

I vantaggi dei vincoli di integrità e delle asserzioni rispetto alle alternative per l'applicazione dell'integrità dei dati includono:

Vedere anche:

Tipi di vincoli di integrità

Oracle AI Database consente di applicare vincoli a livello di tabella e colonna e asserzioni che coinvolgono più tabelle.

Un vincolo specificato come parte della definizione di una colonna o di un attributo è una specifica in linea. Un vincolo specificato come parte della definizione della tabella è una specifica non in linea.

Una chiave è la colonna o l'insieme di colonne incluse nella definizione di determinati tipi di vincoli di integrità. Le chiavi descrivono le relazioni tra le varie tabelle e colonne di un database relazionale. I singoli valori di una chiave sono denominati valori chiave.

Nella tabella seguente vengono descritti i tipi di vincoli. Ognuno può essere specificato in linea o out-of-line, ad eccezione di NOT NULL, che deve essere in linea.

Tabella 1 Tipi di vincoli di integrità

Tipo di vincolo Descrizione Vedere anche:
NOT NULL Consente o non consente l'inserimento o l'aggiornamento di righe contenenti un valore nullo in una colonna specificata. "Vincoli di integrità NOT NULL"
Chiave univoca Impedisce a più colonne di avere lo stesso valore nella stessa colonna o combinazione di colonne, ma consente di avere alcuni valori nulli. "Vincoli unici"
Chiave primaria Combina un vincolo NOT NULL e un vincolo univoco. Impedisce a più righe di avere lo stesso valore nella stessa colonna o combinazione di colonne e impedisce che i valori siano nulli. "Vincoli chiave primaria"
Chiave esterna Designa una colonna come chiave esterna e stabilisce una relazione tra la chiave esterna e una chiave primaria o univoca, denominata chiave di riferimento. "Vincoli PEREIGN KEY"
Controlla Richiede un valore di database per rispettare una condizione specificata. "Vincoli di controllo"
REF Definisce i tipi di manipolazione dei dati consentiti sui valori in una colonna REF e il modo in cui queste azioni influiscono sui valori dipendenti. In un database relazionale agli oggetti, un tipo di dati incorporato denominato REF incapsula un riferimento a un oggetto riga di un tipo di oggetto specificato. I vincoli di integrità referenziale sulle colonne REF garantiscono che esista un oggetto riga per REF. Oracle AI Database Object-Relational Developer's Guide per ulteriori informazioni sui vincoli REF
Assertions Le asserzioni sono espressioni booleane che coinvolgono subquery nelle tabelle di database di un'applicazione. Le asserzioni hanno la semantica di un vincolo, cioè il database ha il compito di garantire la verità di queste espressioni booleane mentre le transazioni cambiano i dati ed eseguono il commit di queste modifiche. Asserzioni SQL per informazioni sulle asserzioni

Vedere anche:

Vincoli di integrità NOT NULL

Un vincolo NOT NULL richiede che una colonna di una tabella non contenga valori nulli. Un valore nullo è l'assenza di un valore. Per impostazione predefinita, tutte le colonne di una tabella consentono valori nulli.

I vincoli NOT NULL sono destinati a colonne che non devono mancare di valori. Ad esempio, la tabella hr.employees richiede un valore nella colonna email. Un tentativo di inserire una riga dipendente senza un indirizzo e-mail genera un errore:

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")

È possibile aggiungere una colonna con un vincolo NOT NULL solo se la tabella non contiene righe o se si specifica un valore predefinito.

Vedere anche:

Vincoli UNIQUE

Un vincolo chiave univoco richiede che ogni valore di una colonna o di un set di colonne sia univoco. Nessuna riga di una tabella può avere valori duplicati in una singola colonna (la chiave univoca) o in un set di colonne (la chiave univoca composta) con un vincolo di chiave univoco.

Nota: il termine chiave si riferisce solo alle colonne definite nel vincolo di integrità. Poiché il database applica un vincolo univoco creando o riutilizzando implicitamente un indice nelle colonne chiave, il termine chiave univoca viene talvolta utilizzato in modo errato come sinonimo di vincolo chiave univoco o indice univoco.

I vincoli chiave univoci sono appropriati per qualsiasi colonna in cui non sono consentiti valori duplicati. I vincoli univoci differiscono dai vincoli di chiave primaria, il cui scopo è identificare ogni riga di tabella in modo univoco e in genere contengono valori che non hanno significato se non essere univoci. Esempi di chiavi univoche sono:

Come illustrato nell'Esempio 4-1, nella colonna email della tabella hr.employees esiste un vincolo di chiave univoco. La parte pertinente della dichiarazione è la seguente:

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

Il vincolo emp_email_uk garantisce che nessun dipendente abbia lo stesso indirizzo e-mail, come mostrato nell'esempio seguente:

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

A meno che non sia definito anche un vincolo NOT NULL, un valore nullo soddisfa sempre un vincolo di chiave univoco. Pertanto, le colonne con vincoli di chiave univoci e vincoli NOT NULL sono tipiche. Questa combinazione forza l'utente a immettere i valori nella chiave univoca ed elimina la possibilità che i nuovi dati di riga siano in conflitto con i dati di riga esistenti.

Nota: a causa del meccanismo di ricerca dei vincoli di chiave univoci su più colonne, non è possibile avere valori identici nelle colonne non nulle di un vincolo di chiave univoca composto parzialmente nullo.

Esempio di vincolo univoco 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

Vedere anche:

Vincoli PRIMARY KEY

In un vincolo chiave primaria, i valori nel gruppo di una o più colonne soggette al vincolo identificano in modo univoco la riga. Ogni tabella può avere una chiave primaria, che in effetti denomina la riga e garantisce che non esistano righe duplicate.

Una chiave primaria può essere naturale o surrogata. Una chiave naturale è un identificativo significativo costituito da attributi esistenti in una tabella. Ad esempio, una chiave naturale potrebbe essere un codice postale in una tabella di ricerca.

Al contrario, una chiave sostitutiva è un identificativo incrementale generato dal sistema che garantisce l'univocità all'interno di una tabella. In genere, una sequenza o un UUID genera chiavi alternative.

L'implementazione di Oracle AI Database del vincolo di chiave primaria garantisce che le seguenti istruzioni siano vere:

Una situazione tipica che richiede una chiave primaria è l'identificativo numerico di un dipendente. Ogni dipendente deve avere un ID univoco. Un dipendente deve essere descritto da una sola riga nella tabella employees.

L'esempio in Vincoli univoci indica che un dipendente esistente ha l'ID dipendente 202, dove l'ID dipendente è la chiave primaria. L'esempio seguente mostra un tentativo di aggiungere un dipendente con lo stesso ID dipendente e un dipendente senza 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")

Il database applica vincoli di chiave primaria con un indice. In genere, un vincolo di chiave primaria creato per una colonna crea implicitamente un indice univoco e un vincolo NOT NULL. Tenere presenti le seguenti eccezioni alla regola:

Per impostazione predefinita, il nome dell'indice creato implicitamente è il nome del vincolo di chiave primaria. È inoltre possibile specificare un nome definito dall'utente per un indice. È possibile specificare le opzioni di memorizzazione per l'indice includendo la clausola ENABLE nell'istruzione CREATE TABLE o ALTER TABLE utilizzata per creare il vincolo.

Vedere anche: Oracle AI Database Introduzione a Oracle AI Database Development e Oracle AI Database Development Guide per informazioni su come aggiungere vincoli di chiave primaria a una tabella

Vincoli FOREIGN KEY

Ogni volta che due tabelle contengono una o più colonne comuni, Oracle AI Database può applicare la relazione tra le due tabelle tramite un vincolo chiave esterna, detto anche vincolo di integrità referenziale.

Un vincolo di chiave esterna richiede che per ogni valore nella colonna in cui è definito il vincolo, il valore nell'altra tabella e colonna specificate corrisponda. Un esempio di regola di integrità referenziale è che un dipendente può lavorare solo per un reparto esistente.

Nella tabella seguente sono elencati i termini associati ai vincoli di integrità referenziale.

Tabella 2 - Condizioni vincolo integrità referenziale

Termine Definizione
Chiave esterna

La colonna o il set di colonne inclusi nella definizione del vincolo che fa riferimento a una chiave di riferimento. Ad esempio, la colonna department_id in employees è una chiave esterna che fa riferimento alla colonna department_id in departments.

Le chiavi esterne possono essere definite come più colonne. Tuttavia, una chiave esterna composta deve fare riferimento a una chiave primaria o univoca composta con lo stesso numero di colonne e gli stessi tipi di dati.

Il valore delle chiavi esterne può corrispondere al valore della chiave primaria o univoca di riferimento oppure essere nullo. Se una colonna qualsiasi di una chiave esterna composta è nulla, le parti non nulle della chiave non devono corrispondere a nessuna parte corrispondente di una chiave padre.
Chiave di riferimento Chiave univoca o chiave primaria della tabella a cui fa riferimento una chiave esterna. Ad esempio, la colonna department_id in departments è la chiave di riferimento per la colonna department_id in employees.
Tabella dipendente o figlio Tabella che include la chiave esterna. Questa tabella dipende dai valori presenti nella chiave univoca o primaria di riferimento. Ad esempio, la tabella employees è un elemento figlio di departments.
Tabella di riferimento o padre La tabella a cui fa riferimento la chiave esterna della tabella figlio. La chiave di riferimento di questa tabella determina se sono consentiti inserimenti o aggiornamenti specifici nella tabella figlio. Ad esempio, la tabella departments è l'elemento padre di employees.

La Figura 7-1 mostra una chiave esterna nella colonna employees.department_id. Garantisce che ogni valore in questa colonna deve corrispondere a un valore nella colonna departments.department_id. Pertanto, nella colonna employees.department_id non possono esistere numeri di reparto errati.

Figura 1: Vincoli di integrità referenziale

Descrizione dell'illustrazione cncpt056.gif

Vincoli di integrità autoreferenziali

Un vincolo di integrità autoreferenziale è una chiave esterna che fa riferimento a una chiave padre nella stessa tabella.

Nella figura seguente, un vincolo autoreferenziale garantisce che ogni valore nella colonna employees.manager_id corrisponda a un valore esistente nella colonna employees.employee_id. Ad esempio, il manager per il dipendente 102 deve esistere nella tabella employees. Questo vincolo elimina la possibilità di numeri di dipendente errati nella colonna manager_id.

Figura 2: Vincoli referenziali a tabella singola

Descrizione dell'illustrazione cncpt057.gif

Null e chiavi esterne

Il modello relazionale consente al valore delle chiavi esterne di corrispondere al valore della chiave primaria o univoca di riferimento oppure di essere nullo. Ad esempio, una riga in hr.employees potrebbe non specificare un ID reparto.

Se una colonna qualsiasi di una chiave esterna composta è nulla, le parti non nulle della chiave non devono corrispondere a nessuna parte corrispondente di una chiave padre. Ad esempio, una tabella reservations potrebbe contenere una chiave esterna composta nelle colonne table_id e date, ma table_id è nullo.

Modifiche chiave padre e chiavi esterne

La relazione tra chiave esterna e chiave padre ha implicazioni per l'eliminazione delle chiavi padre. Ad esempio, se un utente tenta di eliminare il record per questo reparto, cosa accade ai record per i dipendenti di questo reparto?

Quando una chiave padre viene modificata, i vincoli di integrità referenziale possono specificare le seguenti azioni da eseguire sulle righe dipendenti in una tabella figlio:

Nella Tabella 7-3 sono descritte le istruzioni DML consentite dalle diverse azioni referenziali sui valori delle chiavi nella tabella padre e i valori delle chiavi esterne nella tabella figlio.

Tabella 3 Istruzioni DML consentite da aggiornamento ed eliminazione senza azione

Istruzione DML Emesso rispetto a tabella padre Emesso rispetto a tabella figlio
INSERT OK sempre se il valore della chiave padre è univoco OK solo se il valore della chiave esterna esiste nella chiave padre oppure è parzialmente o completamente nullo
UPDATE NO ACTION Consentito se l'istruzione non lascia righe nella tabella figlio senza un valore di chiave padre di riferimento Consentito se il nuovo valore di chiave esterna fa ancora riferimento a un valore di chiave di riferimento
DELETE NO ACTION Consentito se nessuna riga nella tabella figlio fa riferimento al valore della chiave padre Sempre OK
DELETE CASCADE Sempre OK Sempre OK
DELETE SET NULL Sempre OK Sempre OK

Nota: altre azioni referenziali non supportate dai vincoli di integrità FOREIGN KEY di Oracle Database possono essere applicate utilizzando i trigger del database. Vedere "Panoramica dei trigger".

Vedere anche: Oracle AI Database SQL Language Reference per informazioni sulla clausola ON DELETE

Indici e chiavi esterne

Di norma, le chiavi esterne devono essere indicizzate. L'unica eccezione si verifica quando la chiave univoca o primaria corrispondente non viene mai aggiornata o eliminata.

L'indicizzazione delle chiavi esterne nelle tabelle figlio offre i seguenti vantaggi:

Vedere anche:

Vincoli CHECK

Un vincolo di controllo in una colonna o in un set di colonne richiede che una condizione specificata sia vera o sconosciuta per ogni riga.

Se DML restituisce la condizione del vincolo che restituisce false, viene eseguito il rollback dell'istruzione SQL. Il vantaggio principale dei vincoli di controllo è la capacità di applicare regole di integrità molto specifiche. Ad esempio, è possibile utilizzare i vincoli di controllo per applicare le seguenti regole nella tabella hr.employees:

Nell'esempio seguente viene creato un vincolo stipendio massimo in employees e viene illustrato cosa accade quando un'istruzione tenta di inserire una riga contenente uno stipendio superiore al massimo:

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

Una singola colonna può avere più vincoli di controllo che fanno riferimento alla colonna nella relativa definizione. Ad esempio, la colonna salary potrebbe avere un vincolo che impedisce valori superiori a 10000 e un vincolo separato che impedisce valori inferiori a 500.

Se esistono più vincoli di controllo per una colonna, questi devono essere progettati in modo che i relativi scopi non siano in conflitto. Nessun ordine di valutazione delle condizioni può essere assunto. Il database non verifica che le condizioni di controllo non si escludano a vicenda.

Vedere anche: Oracle AI Database SQL Language Reference per informazioni sulle limitazioni relative ai vincoli di controllo

Vincoli CHECK pre-controllati

Un vincolo di controllo contrassegnato come PRECHECK può essere controllato al di fuori del database.

L'impostazione di un vincolo di controllo sullo stato PRECHECK viene eseguita durante la creazione o la modifica della tabella. Lo stato PRECHECK completa gli stati ENABLE e VALIDATE di un vincolo e un vincolo che consente di attivare contemporaneamente tutti e tre gli stati.

Se si utilizza JSON per esprimere il vincolo di controllo, Oracle AI Database consente di esportare uno schema JSON e convalidare i dati JSON con un validatore di schema JSON all'interno del client dell'applicazione. Quando un vincolo è impostato sullo stato PRECHECK, indica che il vincolo ha uno schema JSON equivalente che preserva la semantica del vincolo. Gli sviluppatori di applicazioni client possono preconvalidare il vincolo all'interno dell'applicazione client. Il database controlla il vincolo e viene generato un errore se il vincolo non può essere espresso nello schema JSON.

Argomenti correlati

Asserzioni

Le asserzioni sono espressioni booleane che coinvolgono subquery nelle tabelle di database di un'applicazione.

Le asserzioni hanno la semantica di un vincolo, cioè il database ha il compito di garantire la verità di queste espressioni booleane mentre le transazioni cambiano i dati e eseguono il commit di queste modifiche.

L'istruzione SQL seguente crea un'asserzione, denominata NO_TRAINERS_IN_BOSTON, per richiedere che non vi siano formatori impiegati nei reparti con sede a Boston. Il valore NOT EXISTS di livello superiore rappresenta un'espressione booleana che richiede che il set di risultati del join tra le tabelle EMP e DEPT rimanga vuoto.

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'));

Le asserzioni sono oggetti a livello di schema. Ciò significa che se l'espressione booleana fa riferimento a tabelle di altri schemi, per accedere a tali tabelle è richiesto il privilegio oggetto ASSERTION REFERENCES. Questa operazione non è diversa da una chiave esterna che si estende su due schemi, ovvero è necessario disporre di un privilegio oggetto REFERENCES per creare correttamente una chiave esterna che "punta" a una chiave primaria di una tabella in uno schema diverso.

Le asserzioni sono vincoli generici di integrità dei dati. Potrebbero essere utilizzati per sostituire i vincoli CHECK, PRIMARY KEY, UNIQUE e FOREIGN KEY.

Ad esempio, si supponga i seguenti vincoli nella tabella EMP:

Questi vincoli possono essere implementati con le seguenti asserzioni:

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)));

Tuttavia, si consiglia di utilizzare sempre i vincoli quando possibile e di utilizzare le asserzioni solo quando non è possibile utilizzare un vincolo.

Argomenti correlati

Stati dei vincoli e delle asserzioni di integrità

Nell'ambito della definizione del vincolo o dell'asserzione, è possibile specificare come e quando Oracle AI Database deve applicare il vincolo o l'asserzione, determinando in tal modo lo stato del vincolo o dell'asserzione.

Verifica la presenza di dati modificati ed esistenti

Il database consente di specificare se un vincolo o un'asserzione si applica ai dati esistenti o futuri. Se un vincolo o un'asserzione è abilitato, il database controlla i nuovi dati non appena vengono immessi o aggiornati. I dati non conformi al vincolo o all'asserzione non possono entrare nel database.

Ad esempio, l'abilitazione di un vincolo NOT NULL su employees.department_id garantisce che ogni riga futura abbia un ID reparto. Se un vincolo è disabilitato, la tabella può contenere righe che violano il vincolo.

È possibile impostare vincoli e asserzioni su una delle modalità di convalida riportate di seguito.

Il funzionamento di VALIDATE e NOVALIDATE dipende sempre dal fatto che il vincolo o l'asserzione siano abilitati o disabilitati. Nella tabella seguente vengono riepilogate le relazioni.

Tabella 4 Controlli sui dati modificati ed esistenti

Dati modificati Dati esistenti Riepilogo
ENABLE VALIDATE I dati esistenti e futuri devono rispettare il vincolo o l'asserzione. Un tentativo di applicare un nuovo vincolo o un'asserzione a una tabella popolata genera un errore se le righe esistenti violano il vincolo o l'asserzione.
ENABLE NOVALIDATE Il database controlla il vincolo o l'asserzione, ma non è necessario che sia vero per tutte le righe. Pertanto, le righe esistenti possono violare il vincolo o l'asserzione, ma le righe nuove o modificate devono essere conformi alle regole. Questa modalità viene spesso utilizzata nei data warehouse che contengono dati esistenti la cui integrità è già stata verificata.
DISABLE VALIDATE Il database disabilita il vincolo, ne elimina l'indice e impedisce la modifica delle colonne vincolate. Un'asserzione non può trovarsi in questo stato.
DISABLE NOVALIDATE Il vincolo o l'asserzione non è controllato e non è necessariamente vero.

Vedere anche: Oracle AI Database SQL Language Reference per informazioni sugli stati dei vincoli

Quando il database verifica la validità dei vincoli e delle asserzioni

Ogni vincolo e asserzione è in uno stato non differibile (predefinito) o differibile. Questo stato determina quando Oracle AI Database controlla la validità del vincolo o dell'asserzione.

Il grafico seguente mostra le opzioni per i vincoli e le asserzioni differibili.

Figura 3: Opzioni per vincoli e asserzioni differibili

Descrizione dell'illustrazione cncpt313.gif

Vincoli e asserzioni non tracciabili

In un vincolo non trasferibile, Oracle AI Database non trasferisce mai il controllo di validità del vincolo o dell'asserzione alla fine della transazione. Al contrario, il database controlla il vincolo o l'asserzione alla fine di ogni istruzione. Se il vincolo o l'asserzione viene violato, viene eseguito il rollback dell'istruzione.

Ad esempio, esiste un vincolo NOT NULL non differibile per la colonna employees.last_name. Se una sessione tenta di inserire una riga senza cognome, il database esegue immediatamente il rollback dell'istruzione perché viene violato il vincolo NOT NULL. Nessuna riga inserita.

Vincoli e asserzioni differibili

Un vincolo o un'asserzione differibile consente a una transazione di utilizzare la clausola SET CONSTRAINT per differire il controllo di questo vincolo o asserzione fino all'emissione di un'istruzione COMMIT. Se si apportano modifiche al database che potrebbero violare il vincolo o l'asserzione, questa impostazione consente effettivamente di disabilitare il vincolo o l'asserzione fino al completamento di tutte le modifiche.

È possibile impostare il comportamento predefinito per quando il database controlla il vincolo o l'asserzione differita. È possibile specificare uno dei seguenti attributi:

Si supponga che un vincolo NOT NULL differibile su employees.last_name sia impostato su INITIALLY DEFERRED. Un utente crea una transazione con 100 istruzioni INSERT, alcune delle quali con valori nulli per last_name. Quando l'utente tenta di eseguire il commit, il database esegue il rollback di tutte le 100 istruzioni. Tuttavia, se questo vincolo è impostato su INITIALLY IMMEDIATE, il database non eseguirà il rollback della transazione.

Se un vincolo causa un'azione, il database considera questa azione come parte dell'istruzione che l'ha causata, indipendentemente dal fatto che il vincolo sia differito o immediato. Ad esempio, l'eliminazione di una riga in departments comporta l'eliminazione di tutte le righe in employees che fanno riferimento alla riga del reparto eliminata. In questo caso, l'eliminazione da employees è considerata parte dell'istruzione DELETE eseguita su departments.

Vedere anche: Oracle AI Database SQL Language Reference per informazioni sugli attributi dei vincoli e sui relativi valori predefiniti.

Esempi di controllo dei vincoli e delle asserzioni

Gli esempi riportati di seguito illustrano quando Oracle AI Database esegue il controllo di vincoli e asserzioni.

Se, ad esempio:

Esempio: inserimento di un valore in una colonna chiave esterna quando non esiste alcun valore chiave padre

Questo esempio riguarda l'inserimento della prima riga nella tabella employees. Attualmente non esistono righe, quindi come si può immettere una riga se il valore nella colonna manager_id non può fare riferimento a un valore esistente nella colonna employee_id?

Alcune possibilità sono:

I valori predefiniti vengono inclusi come parte di un'istruzione INSERT prima che l'istruzione venga analizzata. Pertanto, i valori di colonna predefiniti sono soggetti al controllo di tutti i vincoli di integrità.

Esempio: aggiornamento di tutti i valori chiave esterna e chiave primaria

In questo esempio, un vincolo autoreferenziale rende le voci nella colonna manager_id di employees dipendenti dai valori della colonna employee_id.

L'azienda è stata venduta. A causa di questa vendita, tutti i numeri dipendente devono essere aggiornati in modo da essere il valore corrente più 5000 per essere coordinati con i numeri dipendente della nuova società. Come mostrato nel grafico seguente, alcuni dipendenti sono anche manager:

Figura 4: Tabella dei dipendenti prima degli aggiornamenti

Descrizione dell'illustrazione cncpt058.gif

Poiché i numeri dei manager sono anche numeri dei dipendenti, anche i numeri dei manager devono aumentare di 5000. È possibile eseguire la seguente istruzione SQL per aggiornare i valori:

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

Sebbene sia definito un vincolo per verificare che ogni valore manager_id corrisponda a un valore employee_id, l'istruzione precedente è valida perché il database controlla efficacemente i vincoli al termine dell'istruzione. La Figura 7-5 mostra che il database esegue le azioni dell'intera istruzione SQL prima di controllare i vincoli.

Figura 5: Controllo dei vincoli

Descrizione dell'illustrazione cncpt059.gif

Gli esempi riportati in questa sezione illustrano il meccanismo di controllo dei vincoli durante le istruzioni INSERT e UPDATE, ma il database utilizza lo stesso meccanismo per tutti i tipi di istruzioni DML. Il database utilizza lo stesso meccanismo per tutti i tipi di vincoli, non solo per i vincoli autoreferenziali. Nota: le operazioni in una vista o in un sinonimo sono soggette ai vincoli di integrità definiti nelle tabelle di base.

Esempio: ogni manager deve avere un impiegato nello stesso reparto

In questo esempio viene creata un'asserzione per garantire che ogni manager disponga di un impiegato nello stesso reparto.

In questo esempio è necessario inserire prima l'impiegato, quindi il manager. Durante l'esecuzione di più inserimenti di riga, come per i vincoli, le asserzioni vengono convalidate dopo l'esecuzione di ogni istruzione INSERT. È possibile impostare questa asserzione su DEFERRABLE in modo che sia possibile inserire prima il manager e poi l'impiegato. La convalida viene eseguita all'indirizzo COMMIT anziché a ogni istruzione INSERT.

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)));