none
Clef primaire auto incrémentée : le rejet d'une valeur unique sur un autre champ incrémente la valeur suivante de 2

    Question

  • Bonjour,

    J'ai une table sql server :  AUTEUR (numAuteur clef primaire, nomAuteur contrainte unique, genre).

    Je la gère avec un windows form.

    Avant d'ajouter un enregistrement dans ma table, je vérifie par try ... catch l'unicité du nomAuteur.

    Cela fonctionne et me retourne un avertissement si le nom existe déjà.

    Par contre à l'enregistrement suivant, la colonne numAuteur s'est incrémenté de 2, ce qui semble vouloir dire que le tableAdapter.insert a quand même inséré la ligne avec l'incrément n+1 mais ne l'a pas validé (ou l'a delete).

    Quand je vais voir les données, j'ai effectivement un numéro de ligne qui a disparu ; et si je delete le dernier incrément (ex 912) et que je recrée une ligne, l'incrément proposé sera 913.

    Donc il semble que la ligne a été crée mais n'est pas accessible pour modification.

    Est ce comme dans les anciennes tables DBase où "delete" marque la ligne comme à détruire et "pack" la détruit définitivement ?

    Dans ce cas, y a t il une instruction sql qui permette de supprimer définitivement ces ligner "marqués pour destruction" ?

    Malgré mes recherches, je n'ai rien trouvé de tel.

    Merci de votre aide.

    lundi 27 mars 2017 17:51

Réponses

  • Non ce n'est pas vraiment farfelu, mais SQL Server peut avoir besoin de prendre des décisions qui parfois nous semble farfelu :)

    L'identity est une séquence qui ne fait que "avancer" (même principe que les objets SEQUENCE apparu avec SQL Server 2012), donc au moment de votre INSERT on à demandé à la séquence la prochaine valeur et elle c'est incrémentée, maintenant cette séquence ne sait pas de ce que l'on a fait de cette valeur, par conséquent si on ne s'en sert pas (c'est votre cas en cas d'échec de votre INSERT) cette valeur sera en effet inutilisée.

    Il ne faut pas oublier qu'un serveur SQL est multi-utilisateur, multi-requêtes, il peut se passer des milliers de choses par secondes, et à cause de cela le serveur peut décider de changer sa manière de fonctionner dans certains contexte. Dans cette situation le seul moyen fiable d'avoir une valeur unique c'est de l'incrémenter constamment sans jamais revenir en arrière. Imaginons votre code utilisé par 5 personnes au même moment vous allez avoir des incrémentations "bizarres", car le serveur va tout faire pour ne pas avoir de problème avec des valeurs identiques. Si le serveur devait se "souvenir" de tout ce qui n'est pas utilisé, cela prendrait énormément de temps, hors le principe de l'identity est d'avoir une valeur unique, on se moque de l'incrémentation.

    C'est pour cela que l'incrémentation d'un identity n'est pas garanti. 

    Par définition on se moque de l'incrémentation d'un ID, il ne sert qu'à identifier de manière unique (et rapide) un enregistrement dans une table. Vous n'êtes pas sensé utiliser cette identity comme donnée c'est juste un outil fourni par le serveur pour nous faciliter la vie, par conséquent si vous voulez avoir un "id" qui vous sert de code/numéro avec un logique spécifique vous avez deux options:

    • ajouter une colonne à votre table dans laquelle vous calculez un numéro et vous laissez votre identity faire son boulot
    • vous supprimez l'autoincrémentation de votre clé primaire et vous calculez vous même votre id

    Dans les deux cas vous devrez mettre en place des mécanismes similaires à une SEQUENCE pour calculer votre numéro, mais vous devrez faire attention à détecter les valeurs qui ne sont pas utilisées pour pouvoir les reprendre les prochaines fois.

    Cordialement,


    Yan Grenier

    Merci de bien vouloir "Marquer comme réponse", les réponses qui ont répondues à votre question, et de noter les réponses que vous avez trouvé utiles.

    • Marqué comme réponse Serjean38 mardi 28 mars 2017 20:07
    mardi 28 mars 2017 15:59

Toutes les réponses

  • Bonjour,

    C'est possible de voir le code que vous utilisez pour ajouter un nouvel enregistrement ?

    Vous travaillez avec du SQL Server ?

    Cordialement,

    mardi 28 mars 2017 13:00
  • Bonjour,

    Non il n'y a pas cette notion de "ligne supprimée" dans SQL Server. Votre ligne existe où elle n'existe pas. Eventuellement elle peut exister que dans une transaction jusqu'à ce que la transaction soit validée ou annulée.

    La requête INSERT n'a pas créée de ligne qui a été supprimée ensuite, en revanche vous devez utiliser un auto-increment (identity en SQL Server) comme clé primaire, hors pour garantir l'unicité de la valeur de la clé, la première chose que fait une requête INSERT c'est de réserver la prochaine valeur dans la séquence du champ identity.

    Donc si votre insert échoue, cette valeur ne sera pas gardée (elle a été consommée par la séquence, donc on ne revient pas en arrière). En aucun cas il n'est garanti par le serveur que la prochaine valeur d'identité sera incrémentée de 1. C'est à son bon vouloir. Dans certains cas de modification de la structure d'une table, votre identity sera incrémentée de 1000 pour s'assurer qu'il n'y a pas de collisions avec de prochaines instructions.

    Il n'y a donc pas à s'inquiéter des "sauts" dans les valeurs d'une clé identity.

    Cordialement,


    Yan Grenier

    Merci de bien vouloir "Marquer comme réponse", les réponses qui ont répondues à votre question, et de noter les réponses que vous avez trouvé utiles.

    mardi 28 mars 2017 13:39
  • Donc, si je comprends bien et malgré que ma table sql soit en incrément de 1, après le 911 qui correspond à la ligne 911, je peux avoir 950 ou tout autre incrément farfelu ?

    Voici le code :

        try

        {

        auteurTableAdapter.Insert(ajoutNomAuteurTextBox.Text, genreComboBox.Text);

        }            

        catch(Exception)

        {                       

        MessageBox.Show("Auteur EXISTANT");

        return;

        }

        auteurTableAdapter.Fill(bibliothequeJPAndroid2DataSet.auteur);

    Le champ [auteur] de ma table sql est marqué comme unique et cela déclenche bien l'exception et la sortie de la méthode.

    Si j'ajoute des nouveaux auteurs, l'incrément est bon (912,913,914).

    Par contre si je teste l'ajout d'un auteur existant, l'exception est déclenchée et je reviens à la saisie de l'auteur (ce que je veux) par contre l'incrément numéro d'auteur (numAuteur clef primaire) s'incrémente de 2 (913) lors de l'ajout d'un nouvel auteur valide.

    Donc le numéro 912 ne sera jamais attribué ?

    Donc il n'y a pas de solution pour avoir une continuité dans les numéros d'auteur ?

                                                   

    mardi 28 mars 2017 14:49
  • Non ce n'est pas vraiment farfelu, mais SQL Server peut avoir besoin de prendre des décisions qui parfois nous semble farfelu :)

    L'identity est une séquence qui ne fait que "avancer" (même principe que les objets SEQUENCE apparu avec SQL Server 2012), donc au moment de votre INSERT on à demandé à la séquence la prochaine valeur et elle c'est incrémentée, maintenant cette séquence ne sait pas de ce que l'on a fait de cette valeur, par conséquent si on ne s'en sert pas (c'est votre cas en cas d'échec de votre INSERT) cette valeur sera en effet inutilisée.

    Il ne faut pas oublier qu'un serveur SQL est multi-utilisateur, multi-requêtes, il peut se passer des milliers de choses par secondes, et à cause de cela le serveur peut décider de changer sa manière de fonctionner dans certains contexte. Dans cette situation le seul moyen fiable d'avoir une valeur unique c'est de l'incrémenter constamment sans jamais revenir en arrière. Imaginons votre code utilisé par 5 personnes au même moment vous allez avoir des incrémentations "bizarres", car le serveur va tout faire pour ne pas avoir de problème avec des valeurs identiques. Si le serveur devait se "souvenir" de tout ce qui n'est pas utilisé, cela prendrait énormément de temps, hors le principe de l'identity est d'avoir une valeur unique, on se moque de l'incrémentation.

    C'est pour cela que l'incrémentation d'un identity n'est pas garanti. 

    Par définition on se moque de l'incrémentation d'un ID, il ne sert qu'à identifier de manière unique (et rapide) un enregistrement dans une table. Vous n'êtes pas sensé utiliser cette identity comme donnée c'est juste un outil fourni par le serveur pour nous faciliter la vie, par conséquent si vous voulez avoir un "id" qui vous sert de code/numéro avec un logique spécifique vous avez deux options:

    • ajouter une colonne à votre table dans laquelle vous calculez un numéro et vous laissez votre identity faire son boulot
    • vous supprimez l'autoincrémentation de votre clé primaire et vous calculez vous même votre id

    Dans les deux cas vous devrez mettre en place des mécanismes similaires à une SEQUENCE pour calculer votre numéro, mais vous devrez faire attention à détecter les valeurs qui ne sont pas utilisées pour pouvoir les reprendre les prochaines fois.

    Cordialement,


    Yan Grenier

    Merci de bien vouloir "Marquer comme réponse", les réponses qui ont répondues à votre question, et de noter les réponses que vous avez trouvé utiles.

    • Marqué comme réponse Serjean38 mardi 28 mars 2017 20:07
    mardi 28 mars 2017 15:59
  • Merci de ces infos.

    Je pense que je vais supprimer l'auto incrémentation et revenir à une gestion des index comme dans mon programme de 2005.

    Ce sera plus simple ; d'autant qu'à priori, j'ai un problème apparenté sur mon datagridview : à l'ajout de nouvelle ligne, l'incrément de la clef primaire qui apparaît est "-1".

    Je ne me suis pas vraiment penché sur le sujet mais en VB sous Visual Studio 2005, l'incrémentation que j'avais programmée fonctionnait très bien.

    Bien cordialement

    Jean Paul MAFFINI

    mardi 28 mars 2017 19:59
  • J'ai donc modifié ma table pour supprimer l'auto incrément de la colonne clef primaire et suis intervenu sur le dataset pour supprimer l'auto incrément aussi.

    Malgré tout, le tableAdapter considère toujours que la colonne est auto incrémentée.

    J'ai cherché mais n'ai pas trouvé où intervenir pour  indiquer le changement.

    Vais je être  obligé de tout reprendre à zéro ?

    En fait, j'ai mis à jour la requête du tableAdapter et à priori ça marche ... à suivre.


    • Modifié Serjean38 mercredi 29 mars 2017 20:18
    mercredi 29 mars 2017 20:08