Contrainte "unique" sur une table sql!

**nixmind** · 29/05/2012, 01h15

Bonjour!

J'ai un problème avec une des tables de ma base de données sql. J'utilise mysql.

En effet, j'ai une table job avec entre autres entrées, job_id(clé primaire), job_name, user_id(référence au user associé), host_id(référence au host associé).

Dans ma table, on ne devrait pas avoir deux fois un même non de job pour un même user_id ET un host_id.

Mais je veux bien permettre deux fois un même nom de job pour le même user_id avec un host_id différent, le même nom de job pour le même host_id avec un user_id différent.

J'ai utililé les méthodes suivantes :
-

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 alter table job add constraint job_id unique(job_name, user_id, host_id)

Commande accepté.
Mais elle ne marche pas comme je veux, car il y a erreur si je veux créer un job avec le même nom qu'un job existant sous le même user, mais avec un host différent, pourtant je veux que la contrainte d'unicité s'applique à la fois sur les trois champs, et non sur les trois deux à deux.
-

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 alter table job add constraint job_name unique(user_id, host_id);

Génère l'erreur :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ERROR 1062 (23000): Duplicate entry '1-5' for key 'job_name'

-

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

alter table job add unique index(job_name, user_id, host_id);

Commande acceptée.
Celle ci ne me signale plus d'erreur à la création de mes entrées, mais à chaque création d'un job avec le même job_name, l'ancienne valeur est simplement mise à jour sur les autres champs, il n'y a qu'une seule occurrence du job name dans la table.

Bref le problème est peut être compliqué à résoudre, car je n'ai pas encore trouvé de solution, mais ma problématique est facile à comprendre.
En algo j'aurais demandé au sgbd de faire ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
pour une entrée de job, 
si il existe job avec même job_name, même user_id ET même host_id, ne pas créer l'entrée
si non créer l'entrée et ajouter un nouvel index

Je ne sais pas si vous avez une solution, je n'en ai pas trouvé qui satisfasse mon besoin sur le net.

Merci par avance.

**CinePhil** · 29/05/2012, 09h46

Tu as un léger problème de modélisation.

Puisque un job peut être associé à plusieurs user_id et plusieurs host_id, ta table job est plutôt issue d'une entité du MCD et tu es devant une association ternaire :
job -0,n----associer----0,n- user
host -0,n--------|

Ainsi tu devrais avoir une table associative :
job_user_host (juh_id_job, juh_id_user, juh_id_host)

Ceci m'étonne cependant :

J'ai utililé les méthodes suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE job ADD constraint job_id UNIQUE(job_name, user_id, host_id)

Commande acceptée.
Mais elle ne marche pas comme je veux, car il y a erreur si je veux créer un job avec le même nom qu'un job existant sous le même user, mais avec un host différent, pourtant je veux que la contrainte d'unicité s'applique à la fois sur les trois champs, et non sur les trois deux à deux.

Car cette contrainte unique est équivalente à la clé primaire de ma table associative.
Es-tu sûr de ne pas déjà avoir une autre contrainte d'unicité qui entre en conflit avec celle que tu présentes ?

**nixmind** · 29/05/2012, 10h46

Non Cinéphile!

Je ne vois pas.

Mais en fait ma table user a une référence sur la table hôte avec une contrainte d'unicité entre un user et un host, qui marche.

Mais je ne savais pas que l'unicité ne s'applique pas qu'à la seule teble altérée.

Et je ne sais pas si cette contrainte là qui pose un souci, je ne vois pas trop comment.

Et même avec la table associative, je devrais gérer l'unicité, donc je ne pense pas que ça soit la solution.

Car le MPD géné à partir de mon MCD me donnait bien deux tables associatives, une entre job et user et une antre job et host. Mais je n'ai pas trouvé l'intérêt de les créer.

Merci, je continue de chercher.

**CinePhil** · 29/05/2012, 10h54

Pourtant, ta contrainte autorise ces triplets :
job1 / 1 / 1
job1 / 1 / 2 => même job, même user mais pas même host
job1 / 2 / 1 => par rapport à la première ligne, même job, même host mais pas même user.

Les 3 triplets sont différents donc autorisés par la contrainte.

Ceci dit, revois quand même ton modèle de données !

**nixmind** · 29/05/2012, 11h09

Je vais corriger un petit truc Cinéphile!

En fait je pense que l'erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ERROR 1062 (23000): Duplicate entry '1-5' FOR KEY 'job_name'

après l'exécution de la commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE job ADD constraint job_name UNIQUE(user_id, host_id);

pourrait être dû au fait que dans ma table j'avais déjà des duplications avant l'application de la nouvelle contrainte.
Et donc après l'exécution de la commande le sgbd a fait un check et décellé les anomalies.
Je vais recréer la base from scratch, et valider mon modèle morceau par morceau.

J'avais exécuter cette commande en premier car elle était la plus appropriée pour mon cas, d'après mes notions en BD, et je crois qu'elle avait marché et j'ai mal interprété l'erreur. Mais je suis ouvert à tes suggestions de par ton expérience.

par contre je ne suis pas encore pour l'ajout de la table associative qui ne m'épargne pas de la définition de l'unicité.

Je regarde tout ça et je te dis ce qui marche le mieux. je vais peaufiner le modèle.

À bientôt.

**CinePhil** · 29/05/2012, 11h21

Envoyé par sympaval

Je vais corriger un petit truc Cinéphile!

En fait je pense que l'erreur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ERROR 1062 (23000): Duplicate entry '1-5' FOR KEY 'job_name'

après l'exécution de la commande :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE job ADD constraint job_name UNIQUE(user_id, host_id);

pourrait être dû au fait que dans ma table j'avais déjà des duplications avant l'application de la nouvelle contrainte.
Et donc après l'exécution de la commande le sgbd a fait un check et décellé les anomalies.

Ben oui mais moi je ne parlais pas de cette dernière contrainte d'unicité sur le couple {user_id, host_id} mais de celle sur le triplet {job_name, user_id, host_id}.

Je vais recréer la base from scratch, et valider mon modèle morceau par morceau.

Commence alors par refaire un MCD normalisé, de préférence à base de règles de gestion claires.

par contre je ne suis pas encore pour l'ajout de la table associative qui ne m'épargne pas de la définition de l'unicité.

Si, puisque la clé primaire de la table associative est justement le triplet des clés étrangères référençant les clés primaires des trois tables associées.

Je regarde tout ça et je te dis ce qui marche le mieux. je vais peaufiner le modèle.

Au besoin, soumets ton modèle dans le forum Schéma.

**nixmind** · 29/05/2012, 11h36

Ok!

Ça marche!

**nixmind** · 29/05/2012, 12h15

Alors CinéPhile!

je crois c'est toi qui avait raison.

C'est la première commande qui était bien la bonne pour la contrainte. À savoir :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 ALTER TABLE job ADD constraint job_id UNIQUE(job_name, user_id, host_id)

La problème c'est que j'ai intégré la génération et l'application de toutes les contraintes dans le module modèle de mon logiciel, et celui devait faire le travail automatiquement.

Mais j'ai fait une petite erreur de propagation de mes tables associatives, et j'ai aussi passer les mauvais paramètres pour l'unicité.

J'ai repris le module avec le même MCD, et tout marche comme il fallait au niveau de l'interface d'admistration des objets de la base, et du sgbd en question. On a bien les triplets du genre :
job1 / 1 / 1
job1 / 1 / 2 => même job, même user mais pas même host
job1 / 2 / 1 => par rapport à la première ligne, même job, même host mais pas même user.
Et les rejets quand il y a duplication du job_name avec les même user_id et host_id.

Je préfère te mettre directement ici en pièce jointe le MCD et le MPD que tu puisse y jeter un coup d'oeil.
Précisions:
- Sur le MCD, le lien d'association entre la table job et la table host n'est pas ressorti lord de l'exportation (petit détail). Mais considère qu'il y est, comme pour le l'association entre job et user que tu peux bien voir sur le MCD
- Le MCP correspond bien au même MCD avec l'association entre job et hote, et tu veras la table d'association qui en découle
- Les noms des entités dans mes modèles sont plutôt en français. Ouais je préfère toujours l'anglais, mais je dois parler français à mes collaborateurs français.
- la seule table intermédiaire que j'ai créée est celle des dépendances, car si non je devais stocker des données dupliquées sur les tâches.
- J'ai utilisé PowerMC sous windows pour créer le MCD et générer le MPD, car je n'ai pas trouvé un équivalent Linux pour faire le travail (vérifiaction du modèle, application des lois normales, et génération du MPD). Mais j'ai reproduit le schéma avec mon code.

Voilà tu me diras ce que tu penses du MCD (puisque le MPD en découle simplement), et je peux te répondre s'il y a des trucs que tu n'arrives pas à voir.

Je l'ai conçu sans être à la base dévoloppeur ou expert BD, mais avec mes petites notions et ma capacité d'appréhension.

Merci pour ton attention.