4 votes

En utilisant des scripts pour assurer l'intégrité des données dans Oracle

Est-il mauvais de recourir à des déclencheurs ou des scripts pour maintenir l'intégrité des données qu'Oracle n'est pas conçu pour imposer, ou est-ce un signe que je modélise mal mes données?

D'après les réponses à un précédent post (Mise en œuvre des champs définis par l'utilisateur), j'ai décidé de continuer à concevoir en utilisant un mélange d'Héritage de classe et d'Héritage concret. Je veux une classe de base pour tous les SAMPLE puis une table concrète pour chaque ensemble unique d'attributs.

Alors que je peux imposer que chaque table concrète ait une entrée parent dans SAMPLE en faisant de SAMPLE.sample_id la clé primaire avec une contrainte de clé étrangère. Cependant, je ne sais pas comment imposer qu'une entrée SAMPLE ait exactement un enfant puisque l'entrée enfant pourrait être dans un nombre quelconque de tables.

Comment puis-je imposer cela? Si la solution est des déclencheurs INSERT, UPDATE et DELETE, est-ce considéré comme une mauvaise pratique?

3voto

tuinstoel Points 6329

Je pense que vous pouvez résoudre cela en utilisant une vue matérialisée qui est une union de TABLEA, TABLEB et TABLEC + group by sur l'identifiant de la table maître. Vous devez créer des logs de vue matérialisée pour rendre cela une vue matérialisée rafraîchissable rapidement. Et vous devez ajouter une contrainte de vérification qui génère une erreur lorsqu'il y a plus d'une ligne dans la vue matérialisée par identifiant de table maître.

Rob van Wijk explique ici http://rwijk.blogspot.com/2009/07/fast-refreshable-materialized-view.html beaucoup de choses sur les mv's rafraîchissables rapidement. Rob van Wijk est souvent présent ici sur stackoverflow aussi.

Ici, vous pouvez lire sur l'utilisation des contraintes de vérification sur les vues matérialisées : http://technology.amis.nl/blog/475/introducing-materialized-views-as-mechanism-for-business-rule-implementation-complex-declarative-constraints

Utiliser des vm's rafraîchissables rapidement signifie que la vérification d'intégrité est effectuée lors de la validation, et non lors de l'insertion ou de la mise à jour des données.

Je suis très fatigué je ne peux pas le tester moi-même et je ne peux pas fournir un exemple réel.

edit1: Voici l'exemple :

Cela fonctionne lorsque vous créez une vue matérielle à rafraîchissement rapide avec une contrainte de vérification et un index unique basé sur une fonction.

Tout d'abord, nous créons les tables :

SQL> create table mastertable (id number(10) not null primary key);

SQL> create table tablea
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

SQL> create table tableb
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

SQL> create table tablec
(id number(10) not null primary key
, master_id number(10) not null references mastertable (id));

Ensuite, nous créons les logs de mv :

SQL> create materialized view log on tablea with rowid (master_id) 
     including new values;

SQL> create materialized view log on tableb with rowid (master_id) 
     including new values;

SQL> create materialized view log on tablec with rowid (master_id) 
     including new values;

La mv (la colonne umarker est vraiment nécessaire !) :

SQL> create materialized view table_abc
     refresh fast with rowid on commit
     as
     select master_id,count(*) master_count, 'A' umarker
     from   tablea
     group by master_id
     union all
     select master_id,count(*) master_count, 'B' umarker
     from   tableb
     group by master_id
     union all
     select master_id,count(*) master_count, 'C' umarker
     from   tablec
     group by master_id
     /

Ensuite, nous ajoutons une contrainte de vérification à cette vue matérialisée pour garantir que vous ne pouvez pas insérer deux fois dans la même table de détails par identifiant maître :

SQL> alter table table_abc add check (master_count in (0,1) );

Et nous ajoutons un index unique basé sur une fonction à cette vue matérialisée pour garantir que vous ne pouvez pas insérer dans la table a et la table b avec le même identifiant maître :

SQL> create unique index table_abc_ufbi1 on table_abc
     (case when master_count = 1 then master_id else null end);

Test 1 (le cas simple) :

SQL> insert into mastertable values (1);

1 ligne créée.

SQL> insert into tablea values (1,1);

1 ligne créée.

SQL> commit;

Validation terminée.

Test 2 (une insertion dans table a et une insertion dans table b avec le même identifiant maître)

SQL> insert into mastertable values (2);

1 ligne créée.

SQL> insert into tablea values (2,2);

1 ligne créée.

SQL> insert into tableb values (3,2);

1 ligne créée.

SQL> commit; commit * ERREUR à la ligne 1: .ORA-12008: Erreur dans le chemin de rafraîchissement de la vue. ORA-00001: Violation de contrainte UNIQUE (TESTT.TABLE_ABC_UFBI1).

test 3 (insertion dans la table a deux fois avec le même identifiant maître)

SQL> insert into mastertable values (3);

1 ligne créée.

SQL> insert into tablea values (4,3);

1 ligne créée.

SQL> insert into tablea values (5,3);

1 ligne créée.

SQL> commit; commit * ERREUR à la ligne 1: .ORA-12008: Erreur dans le chemin de rafraîchissement de la vue. ORA-02290: Violation de la contrainte de vérification (TESTT.SYS_C0015406).

2voto

Robert Harvey Points 103562

Dites que votre table "principale" s'appelle TableA et que sa clé principale s'appelle "ID". Créez votre deuxième table, appelée TableB, également avec une clé principale nommée "ID". Définissez maintenant TableB(ID) comme une clé étrangère à TableA(A).

Avoir TableB(ID) en tant que clé étrangère signifie qu'elle ne peut avoir de valeur que si elle existe dans TableA(ID), et le fait qu'elle soit la clé principale signifie qu'elle ne peut pas avoir une valeur plus d'une fois.

1voto

John Saunders Points 118808

Si les données ne sont jamais modifiées que par vos propres procédures stockées, alors je ne me soucierais pas de vérifier cette contrainte.

En fait, maintenant que j'y pense, il n'est pas nécessaire de vérifier dans le cas de l'INSERT. Vous insérez à la fois une ligne SAMPLE et une ligne CONCRETE_1 dans la même transaction. Il ne peut pas y avoir de ligne CONCRETE_2 avec la même clé primaire, car la ligne SAMPLE n'existait pas auparavant.

1voto

David Aldridge Points 27624

Les déclencheurs ne peuvent pas être utilisés pour garantir l'intégrité.

Tom Kyte explique pourquoi : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:599808600346047256

Le problème est qu'une transaction ne peut pas voir ce que font d'autres transactions non validées.

Prenons un exemple simple, les transactions A et B peuvent toutes deux avoir l'intention d'insérer la même valeur "X" dans une table, et peuvent toutes deux vérifier qu'une telle valeur n'existe pas déjà. La transaction A valide après que la transaction B ait vérifié l'existence des valeurs "X". La transaction B ne trouve pas de X, donc elle insère également son propre X et valide. Maintenant, l'exigence d'unicité est vaincue.

La seule façon d'éviter cela est que l'ensemble du processus de vérification de la valeur existante, de son insertion et de la validation de l'insertion soit sérialisé.

Ajoutez à cela le problème qu'un déclencheur ne peut pas voir le contenu de la table sur laquelle il se déclenche car elle mute ...

1voto

nagul Points 1469

Je pense que dans ce cas particulier, changer votre modèle de base de données, et ne pas créer de scripts ou de déclencheurs, est la réponse.

Vous avez mentionné dans un commentaire précédent :

Eh bien, j'ai TABLE puis TABLE_A, TABLE_B et TABLE_C. Chaque entrée dans TABLE doit avoir EXACTEMENT UNE entrée dans TABLE_A, TABLE_B ou TABLE_C. La contrainte FK sur A, B, C ne fait que la moitié du travail. Une Table A et une Table B pourraient alors avoir le même parent (ce que je ne veux pas).

Je suggérerais ceci :

  1. Créez une colonne isABC dans TABLE qui spécifie le type comme A, B ou C. Mieux encore, créez une nouvelle table avec la PK (clé primaire) de A et la nouvelle colonne isABC (avec la même PK et une FK sur TABLE.PK).
  2. Configurez (PK, isABC) en tant que contrainte unique.
  3. Ajoutez des colonnes (PK, isABC) à TABLE_A, TABLE_B et TABLE_C. Faites-en une contrainte FK (clé étrangère) sur les mêmes colonnes dans TABLE (ou dans la nouvelle table).
  4. Sur chacune de TABLE_A, TABLE_B et TABLE_C, configurez une contrainte de vérification sur la colonne isABC qui vérifie que la valeur est "A", "B" et "C" respectivement.

Ce design crée une colonne isABC supplémentaire dans TABLE_A, TABLE_B et TABLE_C au prix de l'application de cette contrainte, mais vous vous éloignez des implémentations complexes utilisant des scripts, des déclencheurs ou des procédures.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X