Renforcer l'unicité sur un sous-ensemble de lignes d'une table dans Oracle DB

Question

Pour une table, CARS, avec deux colonnes, MAKE et MODEL, j'aimerais imposer que pour certaines valeurs MAKE particulières, leurs valeurs MODEL doivent être uniques (en supposant qu'il s'agisse d'une base de données Oracle).

Par exemple, je veux imposer les doublons pour Ford, mais pas pour Toyota. Je ne veux pas plus d'une ligne avec le MODÈLE "F150" et la MARQUE "Ford". Par contre, je peux avoir deux lignes avec le MODELE "Rav4" et la MARQUE "Toyota". Il est également possible d'avoir une rangée avec le MODÈLE "F150" et la MARQUE "Ford" et une autre rangée avec le MODÈLE "F150" et la MARQUE "Toyota".

J'ai recherché diverses stratégies avec des déclencheurs, des contraintes ou des index, mais je n'ai encore rien trouvé qui me donnerait le niveau de contrôle dont j'ai besoin.

Merci de votre attention !

Answer 1

Vous pouvez créer un index unique basé sur une fonction afin de garantir l'unicité des paires (marque, modèle) uniquement lorsque la marque est "Ford" :

create table cars (make varchar2(255) not null, model varchar2(255) not null);

create unique index ford_index on cars (
  case make 
  when 'Ford' then make
  else null
  end,
  case make 
  when 'Ford' then model
  else null
  end  );

insert into cars (make, model) values ('Ford', 'F150');
insert into cars (make, model) values ('Ford', 'Fiesta');
insert into cars (make, model) values ('Toyota', 'Camry');
insert into cars (make, model) values ('Toyota', 'Camry');
insert into cars (make, model) values ('Ford', 'F150'); --- FAILS with unique constraint violation

ford_index indexe (marque, modèle) uniquement si marque='Ford' - tout le reste est exclu de l'index et n'est donc pas soumis à la contrainte d'unicité.

Answer 2

Créer une vue matérialisée, avec rafraîchissement rapide lors de la validation. La définition de la vue matérialisée peut être simplement select make, model from <your_table> where make in (select make from <small_table>) . Les <small_table> énumérera les marques pour lesquelles vous souhaitez appliquer cette unicité. Puis créer un index unique sur (make, model) dans la vue matérialisée.

Chaque fois qu'une duplication est tentée avec la fonction make 'Ford' le MV sera actualisé lors de la validation. Le changement dans le MV sera rejeté, ce qui entraînera à son tour le rejet du MV original. COMMIT à l'échec.