Discussion :

[Kropernic]

Bonjour,

Je me rends compte aujourd'hui que, selon mysql workbench, une table ne peut pas avoir deux colonnes auto incrémentée...

Une idée du pourquoi d'une telle restriction ? J'imagine que c'est pour coller à mysql mais bon, ça ne fait que déporter la question d'un niveau.

N.B. : Je suis sur la version 5.2. Je vais mettre une plus récente pour voir.

EDIT : Pareil sur la version 6.3

EDIT 2 : Ah bin mer** ! Finalement, cette limitation semble tout à fait cohérente vu qu'on ne peut pas mettre deux colonnes identity chez sql server... Ce qui, quand on réfléchit 5 min, est logique car on est emmerdé pour utiliser @@identity sinon. Mais j'arrête de digresser sur sql server.

[fsmrel]

Bonjour valeureux Krop,

je me rends compte aujourd'hui que, selon mysql workbench, une table ne peut pas avoir deux colonnes auto incrémentée...

Une idée du pourquoi d'une telle restriction ? J'imagine que c'est pour coller à mysql mais bon, ça ne fait que déporter la question d'un niveau.

Comme son nom l’indique, MySQL Workbench est plutôt orienté MySQL , or que se passe-t-il quand on soumet l’instruction suivante à MySQL ?

CREATE TABLE  KROP
(
        kropid              int             not null auto_increment 
      , kropid_bis          int             not null auto_increment
      , nom                 varchar(16) 
      , salaire             int             not null  
    , CONSTRAINT KROP_PK PRIMARY KEY (kropid)   
) ;

Eh bien, on s’en prend une sévère... :

 Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key.  

Même punition avec MS SQL Server :

CREATE TABLE  KROP
(
        kropid              int             not null identity 
      , kropid_bis          int             not null identity
      , nom                 varchar(16) 
      , salaire             int             not null  
    , CONSTRAINT KROP_PK PRIMARY KEY (kropid)   
) ;

insert into  KROP (nom, salaire) values ('krop', 100) ;
insert into  KROP (nom, salaire) values ('griftou', 200) ;
insert into  KROP (nom, salaire) values ('fsmrel', 100) ;

select * from  KROP ;

=>

Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'KROP'. Only one identity column per table is allowed. 

Maintenant, si vous utilisez PostgreSQL, il n’y a pas d’erreur, tout se passe bien, vous avez toute liberté pour auto-incrémenter à volonté, mais pourquoi fichtre, dire trente-six fois la même chose, quand une seule fois suffit ?

CREATE TABLE  KROP
(
        kropid              serial
      , kropid_bis          serial
      , kropid_ter          serial
      , kropid_quater       serial
      , nom                 varchar(16) 
      , salaire             int             not null  
    , CONSTRAINT KROP_PK PRIMARY KEY (kropid)   
) ;

insert into  KROP (nom, salaire) values ('krop', 100) ;
insert into  KROP (nom, salaire) values ('griftou', 200) ;
insert into  KROP (nom, salaire) values ('fsmrel', 100) ;

select * from  KROP ;

=>

kropid    kropid_bis    kropid_ter    kropid_quater    nom        salaire
------    ----------    ----------    -------------    -------    -------
1         1             1             1                krop       100
2         2             2             2                griftou    200
3         3             3             3                fsmrel     100

[Kropernic]

Bonjour valeureux Krop,

Bonjour grand fmsrel ! ^^

mais pourquoi fichtre, dire trente-six fois la même chose, quand une seule fois suffit ?

(j'avais dit que j'arrêtais la digression mais je me sens obligé de vous répondre )
Je me suis posé aussi la question de savoir si cette seconde colonne était vraiment nécessaire.

C'est une DB qui va en fait servir à gérer des cartes de personnels (pour faire court). Du coup, dans cette table, j'ai mis une colonne servant d'identifier technique tel que recommandé par Mr. Tabourier (Yves pour les intimes) et une seconde pour stocker le "numéro de série" de la carte. Et vu qu'à chaque nouvelle carte, ce numéro doit être incrémenté, quoi de mieux ?

De plus, même avec deux colonnes auto incrémentée, on ne dit pas forcément la même chose. La racine et/ou l'incrément peuvent varier. Mais vous aller me dire que pour la racine, il suffit de faire une addition et pour l'incrément, une multiplication... (je me contredis moi-même comme ça vous gagner du temps ).

La vraie raison pour laquelle j'ai voulu avoir cette colonne "en dur" dans la table au lieu de passer par une vue qui pourrait additionner ma valeur racine (pour coller avec l'historique des numéros déjà existants) et la valeur de l'identifiant de la ligne, c'est pour éviter qu'un jour quelqu'un (probablement moi d'ailleurs ) aille modifier le script de la vue et par conséquent, invalide tous les numéros des cartes existantes...

Ce serait quand même vachement moche. Et comme il s'agit d'une colonne qui ne devrait JAMAIS être mise à jour, je pensais (c'est à l'état d'embryon d'idée pour le moment) utiliser un trigger pour "interdire" la modification de cette colonne.

Sinon, sur SQL Server 2012+ (je n'ai pas fait la recherche pour les autres SGBDR), il existe les séquences pour palier à ce genre de chose. Le code ressemblait à quelque chose dans ce genre-ci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE SEQUENCE S_GM.S_CARD_NUMBER START WITH 206809 INCREMENT BY 1;
GO

CREATE TABLE S_GM.T_CARD_CRD (
    CRD_ID    INT    IDENTITY(1,1) NOT NULL,
    EMP_ID    INT    NOT NULL,
    CRD_NUMBER    INT DEFAULT (NEXT VALUE FOR S_GM.S_CARD_NUMBER),
    CRD_LOCKED BIT NOT NULL DEFAULT CAST(0 AS BIT),
    CONSTRAINT PK_T_CARD_CRD PRIMARY KEY CLUSTERED (
        CRD_ID
    ) WITH (FILLFACTOR = 80)
);
GO

Où 206809 représenterait (j'ai mis un nombre au pif là) le numéro auquel on est arrivé jusqu'ici avec l'ancien système (un vieux brol dont la DB est encore en Advantage Database Server (de Sybase je crois)).

Manque de bol, nous sommes encore avec 2008R2 donc les séquences ne me sont pas disponibles. Du coup, j'ai déporté le numéro de carte dans une table fille comme ceci. :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE S_GM.T_CARD_CRD (
    CRD_ID    INT    IDENTITY(1,1) NOT NULL,
    EMP_ID    INT    NOT NULL,
    CRD_LOCKED BIT NOT NULL DEFAULT CAST(0 AS BIT),
    CONSTRAINT PK_T_CARD_CRD PRIMARY KEY CLUSTERED (
        CRD_ID
    ) WITH (FILLFACTOR = 80)
);
GO

CREATE TABLE S_GM.T_CARD_NUMBER_CRN(
    CRD_ID    INT NOT NULL REFERENCES S_GM.T_CARD_CRD(CRD_ID),
    CRD_NUMBER INT IDENTITY(206809,1) NOT NULL,
    CONSTRAINT PK_T_CARD_NUMBER_CRN PRIMARY KEY CLUSTERED(
        CRD_ID
    ) WITH (FILLFACTOR = 80)
);
GO

ALTER TABLE S_GM.T_CARD_NUMBER_CRN WITH CHECK ADD CONSTRAINT FK_CRN_TO_CRD FOREIGN KEY (CRD_ID) REFERENCES S_GM.T_CARD_CRD(CRD_ID);
GO

(j'ai omis le code faisant références à d'autres tables pour ne pas encombrer)

EDIT : J'ai oublié de préciser que, du coup, je vais mettre une vue en plus pour simuler une table unique et que, une fois que j'aurai migré vers une version de sql server 2012+, je pourrai mettre la séquence en place et réintégrer le numéro de carte dans la table des cartes.

[fsmrel]

Bonjour Krop,

même avec deux colonnes auto incrémentée, on ne dit pas forcément la même chose. La racine et/ou l'incrément peuvent varier.

Je connais un peu le Krop, qui n'est pas un perdreau de l'année, aussi j'attendais qu’il fasse cette remarque

Manque de bol, nous sommes encore avec 2008R2 donc les séquences ne me sont pas disponibles. Du coup, j'ai déporté le numéro de carte dans une table fille comme ceci :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
 CREATE TABLE S_GM.T_CARD_NUMBER_CRN(
    CRD_ID    INT NOT NULL REFERENCES S_GM.T_CARD_CRD(CRD_ID),
    CRD_NUMBER INT IDENTITY(206809,1) NOT NULL,
    CONSTRAINT PK_T_CARD_NUMBER_CRN PRIMARY KEY CLUSTERED(
        CRD_ID
    ) WITH (FILLFACTOR = 80)
);
GO
 
ALTER TABLE S_GM.T_CARD_NUMBER_CRN WITH CHECK ADD CONSTRAINT FK_CRN_TO_CRD FOREIGN KEY (CRD_ID) REFERENCES S_GM.T_CARD_CRD(CRD_ID);

Il y a un risque : se retrouver avec une ligne S_GM.T_CARD_CR sans son homologue dans la table S_GM.T_CARD_NUMBER_CRN...


Pourquoi ne pas prévoir un trigger attaché à la table S_GM.T_CARD_CR, où, en attendant de disposer de l’instruction CREATE SEQUENCE, vous simuleriez l’auto-incrémentation de l’attribut CRD_NUMBER lors des INSERT ?

Quant à l’interdiction de modifier la colonne CRD_NUMBER, pourquoi ne pas utiliser l’instruction GRANT ?

[alassanediakite]

Salut
Un article de SQLpro qui peut être utile.
Je pense qu'il à fait un autre sur "comment faire deux incréments dans une table", mais je ne le trouve pas.
@+
ps
fsmrel, j'ai une idée qui germe (un travail de TITAN ): "compiler les interventions chef-d’œuvre de fsmrel". Qu'avez-vous à dire?

[Kropernic]

Salut
Un article de SQLpro qui peut être utile.
Je pense qu'il à fait un autre sur "comment faire deux incréments dans une table", mais je ne le trouve pas.
@+
ps
fsmrel, j'ai une idée qui germe (un travail de TITAN ): "compiler les interventions chef-d’œuvre de fsmrel". Qu'avez-vous à dire?

L'article de sqlpro est effectivement intéressant (comme toujours j'ai envie de dire). Mais j'ai toujours mon problème de paranoïa de la modif...

Pour le hit fsmrel, je vote pour

[Kropernic]

Hello,

Voici ce que j'avais modélisé dans mysql workbench jusqu'ici.


Le problème se situe au niveau de la table S_GM.T_CARD_CRD. Le numéro de carte (la colonne CRD_NUMBER) devant s'incrémenter à chaque nouvelle carte, j'avais pensé à une colonne auto-incrémentée (IDENTITY sous MS Sql Server par exemple). Mais puisque la clef primaire (la colonne CRD_ID) est déjà auto-incrémentée et qu'on ne peut avoir qu'une colonne de ce genre par table, cette solution n'est pas valide.

Je me suis dit que que je pourrais faire sauter l'une de ces deux colonnes. Puisque ces deux colonnes vont croître au même rythme, même s'il devait y avoir un décalage, ce décalage resterait constant et une simple addition permettrait de s'y retrouver. Mais en faisant cela, je perds l'identifiant technique (mais j'assure l'unicité du numéro sans devoir ajouter une contrainte supplémentaire).

Je suis donc en quête de la bonne idée pour représenter cette problématique de manière correcte.

[Kropernic]

Ajout d'info :

Il me faut absolument un endroit pour stocker le numéro de série de la carte car je pars avec un historique.

[Kropernic]

Pour clore cette discussion, voici la solution que j'ai retenue.

Il y a peu de chance que quelqu'un ait un jour le même besoin mais sait-on jamais...



Vu que le sujet n'avait pas l'air de passionner les foules, je ne m'ennuie pas à commenter. Si un jour à quelqu'un à une question ou une remarque, qu'il n'hésite surtout pas, je viendrai lui répondre.

[fsmrel]

Bonjour Krop,


Je suis désolé de répondre si tard, mais je suis plutôt débordé, ça tombe comme à Gravelotte...

J’ai cherché une solution simple et efficace, mais en vain...

Je ne désespère pas pour autant d’y arriver...

En tout cas, bon courage à vous, valeureux Krop.

[Kropernic]

Bonjour Krop,


Je suis désolé de répondre si tard, mais je suis plutôt débordé, ça tombe comme à Gravelotte...

Y a pas de mal.

J’ai cherché une solution simple et efficace, mais en vain...

Je ne désespère pas pour autant d’y arriver...

Nous sommes deux.

C'est cet historique qui pose problème.

Et depuis hier, le schéma s'est encore étoffé de deux tables supplémentaires pour y fourrer les anciens employés. Ceux qui sont pensionnés depuis un certain et qui ne se trouvent plus dans la base de données de notre secrétariat social mais bien dans la vielle DB (Advantage DataBase Server et ses fichiers .dbf et .cdx sans aucune intégrité référentielle ).

Et je crois que je vais devoir en ajouter encore pour y fourrer les employés marquer dans la vielle DB comme étant actif mais que je ne retrouve pas dans la DB actuelle de notre secrétariat social. Mais ceux-là devraient, à terme, être amenés à disparaître et je pourrai supprimer cette table. Et je remarque en écrivant ceci que les pensionnés de mon paragraphe précédent aussi seront amenés à disparaître petit à petit au fur et à mesure que les années passeront . Au final (dans un paquet d'années si le programme est utilisé jusque là et moi avec), je devrais quand même avoir un schéma propre.

En tout cas, bon courage à vous, valeureux Krop.

Merci, j'ai l'impression que je ne suis pas au bout de mes surprises avec cette histoire de cartes ^^.

[Kropernic]

Tiens, une nouvelle question mais par rapport aux commandes ce coup-ci.

Dans l'application, les admins pour commander des cartes (ie. création d'un fichier à envoyer vers l'imprimeur pour la fabrication des cartes).

A priori, pas compliquer de déterminer les cartes qu'il faut commander.

Ce sont celles qui n'ont pas encore fait l'objet d'une commande ainsi que celle dont le flag CRD_REPRINT est sur True.

Seulement voilà, l'historique vient de nouveau mettre son grain de sel vu que toutes les cartes existantes que j'importe n'auront forcément pas fait l'objet d'une commande dans ce nouveau système et il ne faut pas les commander chez l'imprimeur vu qu'elles existent déjà physiquement.

Du coup, deux options s'offrent à moi (enfin, je n'en vois que deux, s'il y en a d'autres, faites moi signe ^^) :

1. Ajouter un flag CRD_NEW dans la table T_CARD_CRD pour signaler les cartes qui sont nouvelles et doivent se trouver dans la prochaine commande.
2. Simuler une commande pour y mettre toutes les cartes que j'importe depuis mon historique.

Je pense opter pour l'option n°1 mais j'aurais voulu avoir un 2e avis.

Qu'en pensez-vous ?