Gestione di Vincoli di Integrità

martedì 23 ottobre 2007 alle 23:22 | Pubblicato su Diario, Installation and Configuration, SQL | 4 commenti

Pur troppo nell’azienda in cui lavoro, come immagino in molte altre, nella progettazione della base dati si è scelto di fare un uso molto blando della definizione dei vincoli di integrità sul database (Integrity Constraints). Questo significa che non vengono mai definite chiavi esterne (Foreign Key) o chiavi primarie (Primary Key) sono vincoli di non nullità o univocità, anche questi quando ci si ricorda.

Questo ha causato in me una grave lacuna riguardo la gestione e amministrazione dei vincoli di integrità in Oracle. Volendo prendere la certificazione devo per forza colmare tale lacuna. Siccome faccio ancora fatica a ricordare tutte le cose ho finalmente deciso di trascrivere tutte le informazioni in un bel post in questo blog.

In realtà faro poco di più che una traduzione del paragrafo “Managing Integrity Constraints” del capitolo 13 del manuale Oracle “Oracle Database Administrator’s Guide“.

Come fa il manuale di riferimento anche io tralascio le spiegazioni su cosa sono i vincoli di integrità e perchè occorre usarli mi limito ad elencarne i tipi:

  1. Not Null
  2. Unique
  3. Primary Key
  4. Foreign Key
  5. Check

In realtà a me pare che il Not Null sia solo un caso particolare di check, questa mia impressione nasce dal fatto che sulla tabella ALL_CONSTRAINTS per entrambi il campo CONSTRAINT_TYPE assume valore ‘c’ e sulla documentazione spiega anche come distinguere i constraint not null dai constraint Check andando a guardare la colonna SEARCH_CONDITION:

CONSTRAINT_NAME  SEARCH_CONDITION
---------------  ----------------------------------------
LOC_CHECK1       loc IN ('NEW YORK', 'BOSTON', 'CHICAGO')
SYS_C00278       ENAME IS NOT NULL
SYS_C00279       DEPTNO IS NOT NULL

Stati di un vincolo di integrità

Un vincolo può essere attivo ENABLE (controlla i dati che vengono immessi nella tabella) o non attivo DISABLE (non controlla i dati inseriti). Inoltre è possibile specificare se i dati già presenti devono rispettare le regole imposte dal vincolo, che quindi può essere in modalità VALIDATE, altrimenti è in modalità NOVALIDATE. Gli stati possibili del vincolo sono le quattro combinazioni delle due caratteristiche sopra descritte:

  1. ENABLE, VALIDATE
  2. ENABLE, NOVALIDATE
  3. DISABLE, VALIDATE
  4. DISABLE, NOVALIDATE

Il primo stato , ENABLE, VALIDATE è quello di default.

Lo stato DISABLE,VALIDATE è particolare; creando un vincolo in questo stato, i dati già esistenti vengono controllati. Sam Alapati nel suo libro dice che Oracle rimuove l’indice sul vincolo ma mantiene attivo il vincolo. Poi dice che essendo DISABLE non sono possibili operazioni DML sulla tabella, questo devo verificarlo per capirlo. La documentazione oracle in questo caso è più chiara, il problema è sempre trovare le cose. Infatti occorre andare qui per trovare conferma del fatto che non sono possibili DML, non mi è del tutto chiaro il perchè. In ogni caso ho fatto delle prove:

SQL> alter table test add constraint test_fk1
 2 foreign key (idfk) references tvaluta (tvalid)
 3 deferrable disable validate ;

Tabella modificata.

SQL> insert into test values (100,1,'test');
insert into test values (100,1,'test')
*
ERRORE alla riga 1:
ORA-25128: Nessun inserimento/aggiornamento/eliminazione 
su tabelle con restrizione (SVILUPPO31.TEST_FK1) 
disabilitata e convalidata

Test di constraints:

SQL> alter table test enable constraint test_fk1;

Tabella modificata.

SQL> insert into test values (100,1,'test');

Creata 1 riga.

SQL> rollback;

Rollback completato.

SQL> insert into test values (100,2,'test');
insert into test values (100,2,'test')
*
ERRORE alla riga 1:
ORA-02291: restrizione di integrità violata (SVILUPPO31.TEST_FK1) -
 chiave madre non trovata

SQL> set constraint test_fk1 deferred;

Set di vincoli.

SQL> insert into test values (100,1,'test');

Creata 1 riga.

SQL> insert into test values (100,2,'test');

Creata 1 riga.

SQL> commit;
commit
*
ERRORE alla riga 1:
ORA-02091: transazione in rollback
ORA-02291: restrizione di integrità violata (SVILUPPO31.TEST_FK1) -
 chiave madre non trovata

SQL> select * from test where pkid=100;

Nessuna riga selezionata

Rely Constraints

Si tratta di una caratteristica che come specificato qui è utile in situazioni di datawarehouse

ALTER TABLE SALESE ADD CONSTRAINT sales_fk1
 FOREIGN KEY (fk1) REFERENCES region(pkid)
 RELY DISABLE NOVALIDATE;

Differimento del controllo dei vincoli

Per default Oracle controlla il rispetto del vincolo durante l’esecuzione dello statement di modifica dei dati, se i dati risultanti dalla modifica apportata dallo statement non rispettano i vincoli allora lo statement fallisce. E’ possibile però stabilire quando si definisce il vincolo che lo stesso vincolo non venga controllato all’esecuzione dello statement ma al commit della transazione. Si parla allora di differimento, o meglio di deferring. Con la parola chiave DEFERRABLE nella definizione del vincolo, si indica che il rispetto del vincolo potrà essere differito a livello di transazione e non di statement. Per farlo si può usare l’istruzione: “SET CONSTRAINT constraint_name DEFERRED;”. Se nella definizione del vincolo si specifica NOT DEFERRABLE allora il vincolo non sarà differibile a livello di transazione. Questa modalità non è modificabile, per cambiarla occorre eliminare e ricreare il vincolo. Per default, specificando “DERRABLE” alla creazione di un vincolo, il vincolo comunque sarà verificato a livello di statement e per differirlo occorrerà modificarlo esplicitamente con l’istrunzione SET già descritta. Specificando “INITIALLY DEFERRED” alla creazione si cambia questo comportamento. Il default, che è l’opposto di INITIALLY DEFERRED è INITIALLY IMMEDIATE.

Interessante è poi la possibilità di definire un vincolo specificando che le violazioni vengano registrate su una tabella, questo si fa aggiungendo all’istruzione di crezione del vincolo “EXCEPTIONS INTO EXCEPTIONS”. In questo caso le violazioni o eccezioni vengono inserite in una tabella la cui definizione si trova sotto ?/rdbms/utlexcpt.sql . Questa cosa si usa principalmente se si attiva il vincolo in modalità validate su una tabella con già dei dati che si vogliono controllare.

SQL> alter table test  add constraint test_fk1
 2 foreign key (idfk) references tvaluta (tvalid)
 3 deferrable enable validate exceptions into exceptions;

Tabella modificata.

SQL> insert into test values (100,2,'test');
insert into test values (100,2,'test')
*
ERRORE alla riga 1:
ORA-02291: restrizione di integrità violata (SVILUPPO31.TEST_FK1) -
 chiave madre non trovata

SQL> select * from exceptions wherE TABLE_NAME='TEST';

Nessuna riga selezionata

SQL> set constraints test_fk1 deferred;

Set di vincoli.

SQL> insert into test values (100,1,'test');

Creata 1 riga.

SQL> insert into test values (100,2,'test');

Creata 1 riga.

SQL> SELECT * FROM EXCEPTIONS;

Nessuna riga selezionata

SQL> COMMIT;
COMMIT
*
ERRORE alla riga 1:
ORA-02091: transazione in rollback
ORA-02291: restrizione di integrità violata (SVILUPPO31.TEST_FK1) - 
 chiave madre non trovata

SQL> SELECT * FROM EXCEPTIONS;

Nessuna riga selezionata
SQL> alter table test drop constraint test_fk1;

Tabella modificata.
SQL> alter table test  add constraint test_fk1
  2  foreign key (idfk) references tvaluta (tvalid)
  3  enable validate exceptions into exceptions;
alter table test  add constraint test_fk1 foreign key (idfk) references tvaluta (tvalid)
                                 *
ERRORE alla riga 1:
ORA-02298: impossibile convalidare (SVILUPPO31.TEST_FK1) - 
chiavi padre non trovate

SQL> SELECT * FROM EXCPETIONS WHERE TABLE_NAME='TEST';
SELECT * FROM EXCPETIONS WHERE TABLE_NAME='TEST'
              *
ERRORE alla riga 1:
ORA-00942: tabella o vista inesistente

SQL> SELECT * FROM EXCEPTIONS WHERE TABLE_NAME='TEST';

ROW_ID                     OWNER        TABLE_NAME  CONSTRAINT
---------------------- ------------ ------------  -------------
AAAG8zAAFAAArAXAAU SVILUPPO31  TEST           TEST_FK1

4 commenti »

RSS feed for comments on this post. TrackBack URI

  1. Non definire vincoli di integrità referenziale è uno degli errori più enormi che si possono fare quando si ha a che vedere con un database.

    Se per conoscere bene una persona basta guardare il contenuto del suo hard-disk, per conoscere bene un’azienda basta guardare come ha strutturato il proprio database.

  2. parole sante

  3. I vincoli di integrità “fisici” inseriti direttamente sul DB sono la più grossa fesseria che un progettista di sistemi informativi possa fare.
    I vincoli andrebbero inseriti sulle procedure di accesso ai dati in modo da poterle modificare. I vincoli fisici sul DB nel tempo creano sistemi impossibili da modificare perchè nessuno si prenderà mai la responsabilità di eliminarne uno.
    Questo fa si che se cambiano le relazioni o la definizione di oggetti, NON si può cambiare il DB e quindi si crea una proliferazione di nuove tabelle sempre più complicate da gestire, si è costretti alle “migrazioni” tra DB e si perde la memoria storica del “perchè” erano stati inseriti quei vincoli.

    • o sei un troll e quindi ci puo stare oppure, ahimè, le uniche grosse e grasse fesserie sono le tue parole;
      il grave è sentire queste fesserie da uno che si definisce “Progettista di Sistemi Informativi da 25 anni” e la cosa mi provoca una gran pena per i tuoi sfortunati clienti …
      Ti invito, anche se ho la sensazione che cadrà nel vuoto, a leggerti il capitolo 7 del libro “Effective Oracle by Design”

      http://www.amazon.com/Effective-Oracle-Design-Osborne-ORACLE/dp/0072230657

      lo troveresti ILLUMINANTE


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: