Doute sur les definitions de clefs en "CREATE TABLE" - PRIMARY KEY + UNIQUE KEY + key + index

Bonjour,

J'ai un gros doute sur la conception lors des "CREATE TABLE"

Exemple concret , on va considérer une table "clients" avec le PRIMARY KEY + UNIQUE KEY + key + index ou l'on stock des numéros de clients de manière unique :

cas 1 sans id :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE IF NOT EXISTS client ( num_client int(11) NOT NULL , type_client varchar(25) NOT NULL , fk_departement_client varchar(5) NOT NULL date_insertion datetime NOT NULL, PRIMARY KEY (num_client), UNIQUE KEY (num_client), key (num_client,departement_client), index (num_client,departement_client), CONSTRAINT departement_fk FOREIGN KEY (fk_departement_client) REFERENCES departement (departement_client) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

---

cas 2 avec id :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE IF NOT EXISTS client ( id_ligne int(11) auto_increment , num_client int(11) NOT NULL , type_client varchar(25) NOT NULL , fk_departement_client varchar(5) NOT NULL date_insertion datetime NOT NULL, PRIMARY KEY (id_ligne), UNIQUE KEY (num_client), key (num_client,departement_client), index (id_ligne,num_client,departement_client), CONSTRAINT departement_fk FOREIGN KEY (fk_departement_client) REFERENCES departement (departement_client) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

---

Merci de m'aiguiller et de dire ce qui cloche avec " PRIMARY KEY + UNIQUE KEY + key + index "

Bonjour

Une clef primaire est par définition unique, donc, définir à la fois une contrainte PK et une contrainte unique sur cette colonne est une redondance.
Je suppose que MySQL refuse ce script

De plus

• Une FK de type varchar est une hérésie :aie:, cela signifie soit que dans la table où la colonne est PK elle est également de type varchar, ce qu'il ne faut jamais choisir comme type de PK, soit le type est différent entre la PK et la FK ce qui n'est pas mieux
• La clause KEY et la clause INDEX sont redondantes. En principe, la clef est l'identifiant logique alors que l'index est le support physique, mais, dans MySQL les deux mots clefs sont traités à l'identique (c'est aberrant mais c'est comme ça)
• une type client de type varchar est également une aberration, en principe, vous devriez avoir une FK de type smallint ou integer qui fait référence à une table des types clients.
• vu que num_client seul est unique, ajouter un index sur num_client + departement_client n'a d'intérêt que s'il est dit "couvrant" pour certaines requêtes. En ce cas, il vaut mieux créer l'index en inversant les deux colonnes pour permettre une recherche sur le département client seul (après avoir corrigé l'hérésie sur le typage de cette colonne bien sur ;))

Bonjour

Citation:

---

Envoyé par escartefigue

Je suppose que MySQL refuse ce script

---

J'ai testé un script de la sorte sur une version Uwamp derniere version et sur du Heidi SQL + Maria DB/Mysql5.5 . Le script passe sans aucun problème ... C'est bien cela qui me surprend . En même temps le code proposé par MySQL est juste horrible :aie:

De plus

Citation:

---

Envoyé par escartefigue

• Une FK de type varchar est une hérésie :aie:, cela signifie soit que dans la table où la colonne est PK elle est également de type varchar, ce qu'il ne faut jamais choisir comme type de PK, soit le type est différent entre la PK et la FK ce qui n'est pas mieux
• La clause KEY et la clause INDEX sont redondantes. En principe, la clef est l'identifiant logique alors que l'index est le support physique, mais, dans MySQL les deux mots clefs sont traités à l'identique (c'est aberrant mais c'est comme ça)
• une type client de type varchar est également une aberration, en principe, vous devriez avoir une FK de type smallint ou integer qui fait référence à une table des types clients.
• vu que num_client seul est unique, ajouter un index sur num_client + departement_client n'a d'intérêt que s'il est dit "couvrant" pour certaines requêtes. En ce cas, il vaut mieux créer l'index en inversant les deux colonnes pour permettre une recherche sur le département client seul (après avoir corrigé l'hérésie sur le typage de cette colonne bien sur ;))

---

[/QUOTE]

Pour les autres champs cela est volontairement arbitraire j’aurai pu mettre numérique partout ...

Il n'y a pas de raison logique de rejeter cette double clef et SQL (la norme du langage) ne l'interdit pas. En effet pour des raisons d'évolution de base on peut se retrouver avec 2 contraintes presque identiques sur la même table. Voici un exemple :

Code:

---

1 2 3 4 5 6 7 8 9

CREATE TABLE T_VEHICULE_VHC (VHC_IMMATRICULATION CHAR(10) CONSTRAINT PK_VHC PRIMARY KEY, VHC_MARQUE VARCHAR(32) NOT NULL, VHC_MODELE VARCHAR(32) NOT NULL) GO   INSERT INTO T_VEHICULE_VHC VALUES ('745 RBL 75', 'Pijo', '404'), ('AB 344 CA', 'Rino', '4L'); GO

---

--> une nouvelle voiture va être achetée, mais on ne connais pas son immatriculation encore...

Code:

---

1 2 3

INSERT INTO T_VEHICULE_VHC VALUES (NULL, 'TESLA', 'X'); GO

---

--> EXCEPTION ! Impossible d'insérer NULL dans la colonne 'VHC_IMMATRICULATION', table 'T_VEHICULE_VHC'...

--> remède, rajoutons une contrainte d'unicité sur VHC_IMMATRICULATION

Code:

---

ALTER TABLE T_VEHICULE_VHC ADD CONSTRAINT UK_VHC_IMMAT UNIQUE (VHC_IMMATRICULATION)

---

--> rajoutons maintenant une colonne auto incrémentée (IDENTITY norme SQL) qui servira de clef primaire

Code:

---

ALTER TABLE T_VEHICULE_VHC ADD VHC_ID INT NOT NULL IDENTITY;

---

--> supprimons l'actuelle clef primaire :

Code:

---

ALTER TABLE T_VEHICULE_VHC DROP CONSTRAINT PK_VHC;

---

--> ajoutons la nouvelle clef primaire :

Code:

---

ALTER TABLE T_VEHICULE_VHC ADD CONSTRAINT PK_VHC PRIMARY KEY (VHC_ID);

---

Grâce à cette superposition de deux contraintes à aucun moment il n'a été possible d'insérer un doublon dans la clef de la table, alors que si j'avais du procéder en supprimant d'abord la PK, les utilisateurs concurrent auraient pu insérer un doublon d'immatriculation et là ma base était désintégrée !!!!

A +

PS : pour réviser le SQL, mon livre
Pièce jointe 509295

Citation:

---

Envoyé par tanaka59

Bonjour,

J'ai un gros doute sur la conception lors des "CREATE TABLE"

Exemple concret , on va considérer une table "clients" avec le PRIMARY KEY + UNIQUE KEY + key + index ou l'on stock des numéros de clients de manière unique :

cas 1 sans id :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE IF NOT EXISTS client ( num_client int(11) NOT NULL , type_client varchar(25) NOT NULL , fk_departement_client varchar(5) NOT NULL date_insertion datetime NOT NULL, PRIMARY KEY (num_client), UNIQUE KEY (num_client), key (num_client,departement_client), index (num_client,departement_client), CONSTRAINT departement_fk FOREIGN KEY (fk_departement_client) REFERENCES departement (departement_client) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

---

cas 2 avec id :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

CREATE TABLE IF NOT EXISTS client ( id_ligne int(11) auto_increment , num_client int(11) NOT NULL , type_client varchar(25) NOT NULL , fk_departement_client varchar(5) NOT NULL date_insertion datetime NOT NULL, PRIMARY KEY (id_ligne), UNIQUE KEY (num_client), key (num_client,departement_client), index (id_ligne,num_client,departement_client), CONSTRAINT departement_fk FOREIGN KEY (fk_departement_client) REFERENCES departement (departement_client) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

---

Merci de m'aiguiller et de dire ce qui cloche avec " PRIMARY KEY + UNIQUE KEY + key + index "

---

En revanche la définition d'un index dans une table ne fait pas partie de la norme SQL… et appeler "key" quelque chose qui est en fait un index est d'une haute stupidité comme bien des choses dans mySQmerde…. À lire :
https://sqlpro.developpez.com/tutori...mysql-mariadb/

A +

Fréderic,

Ton exemple est limite dans la mesure où le choix initial d'une PK fonctionnelle est évidemment un mauvais exemple, mais surtout c'est sans rapport avec le script fourni dans lequel il est posé à la fois une contrainte PK et une contrainte UNIQUE, redondante donc, sur la même colonne :

Citation:

---

Envoyé par tanaka59

Code:

---

1 2 3 4 5 6 7 8 9 10 11

CREATE TABLE IF NOT EXISTS client ( num_client int(11) NOT NULL , type_client varchar(25) NOT NULL , fk_departement_client varchar(5) NOT NULL date_insertion datetime NOT NULL, PRIMARY KEY (num_client), UNIQUE KEY (num_client), key (num_client,departement_client), index (num_client,departement_client), CONSTRAINT departement_fk FOREIGN KEY (fk_departement_client) REFERENCES departement (departement_client) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ;

---

---

Je suis surpris que MySQL l'accepte, sans doute ignore-t- il la deuxième contrainte puisque déjà assurée par la contrainte PK
Il faudrait le tester sur d'autres SGBD pour voir quels sont les messages émis.

Salut à tous.

Citation:

---

Envoyé par Tanaka59

J'ai un gros doute sur la conception lors des "CREATE TABLE"

---

La bonne question que vous auriez dû poser est comment bien déclarer mes index ?

A) En ce qui concerne la clef primaire, je réponds ainsi :

Code:

---

`num_client` integer unsigned NOT NULL PRIMARY KEY,

---

Le seul problème a cette déclaration est que vous devez insérer, par vous-même, des valeur à votre "num_client".
Pas toujours pratique de le faire.

Si vous ne désirez pas trop vous casser la tête et laisser MySql vous attribuer des valeurs, faites cela :

Code:

---

`num_client` integer unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,

---

Pourquoi mettre "integer unsigned" et non "integer" ?
Votre colonne "num_client" va accepter que ces valeurs positives.
Autant définir votre colonne avec la plage maximale.
--> https://dev.mysql.com/doc/refman/8.0...ger-types.html
La plage va de 0 jusqu'à 4294967295.
Si "integer unsigned" est encore trop petit, mettez "bigint unsigned".
La plage va alors de 0 jusqu'à 2^64 - 1.

Que représente cette clef primaire (je parle de la déclarative ci-dessus avec auto_incrément) ?
A-1) c'est un index.
A-2) chaque valeur sera unique dans l'index.
A-3) la valeur NULL est interdite
A-4) avec AUTO_INCREMENT, c'est MySql qui incrémente de +1 chaque insertion, en commençant par 1.
A-5) la valeur maximale est stockée dans la base et peut-être récupéré par "last_insert_id()" :
--> https://dev.mysql.com/doc/refman/8.0...last-insert-id
A-6) on peut faire référence à la primary key (dans la version 8.0) en utilisant "_rowid".
A-7) il est vivement conseillé de toujours définir une clef primaire auto incrémenté dans une table.
A-8) c'est un index technique qui sert à rendre unique chaque ligne d'une table.

B) "key" ou "index" sont synonymes dans MySql.

C) à quoi peut servir un "UNIQUE INDEX" ?
Quand vous avez besoin d'utiliser un index, autre que la clef primaire, dont la recherche unique sera basée sur un autre critère.
Par exemple, selon la date de saisie, dont celle-ci comprendra la date, l'heure et pourquoi pas les millionièmes de secondes.

Comme le dit Escartefigue :

Citation:

---

Envoyé par Escartefigue

Une clef primaire est par définition unique, donc, définir à la fois une contrainte PK et une contrainte unique sur cette colonne est une redondance.

---

ceci est tout à fait vrai dans MySql, pas nécessairement dans d'autres SGBDR.

Par exemple :

Code:

---

unique index `idx` (`col1`,`col2`,`col3`)

---

C-1) Il est vivement conseillé de nommer chaque index. Ici, j'ai mis "idx".
C-2) L'index unique autorise les NULL, sauf si vous spécifiez explicitement (NOT NULL) que vous n'en voulez pas.
C-3) On peut associer plusieurs colonnes à un index afin de rendre celui-ci unique.

D) à quoi peut servir une "foreign key" ?
C'est associer des lignes d'une table fille à une ligne unique d'une table mère.
C'est une relation de type hiérarchique.

Par exemple :

Code:

---

CONSTRAINT `FK_01` FOREIGN KEY (`fk_mere_id`) REFERENCES `mere` (`mere_id`) ON DELETE CASCADE ON UPDATE CASCADE

---

D-1) il faut nommer cette clef étrangère, ici : "FK_01".
D-2) vous devez faire référence à la clef primaire d'une table mère, ici : "mere".
D-3) la clef étrangère "fk_mere_id" que vous déclarez dans la table fille doit avoir exactement la même déclarative "mere_id" que dans la table "mère".
D-4) la table "mère" doit être déclarée et remplie avant la table "fille" sinon, lors de l'insertion d'une ligne dans la table "fille", celle-ci sera rejeté.
D-5) lors de la suppression d'une ligne dans la table "mère", vous pouvez spécifier comment seront traiter les lignes en relation de la table fille.
Il s'agit de cette déclaration : "ON DELETE CASCADE ON UPDATE CASCADE".
--> https://dev.mysql.com/doc/refman/8.0...eign-keys.html

Voici un résumé de ce qu'il faut savoir sur les index.
Au lieu de nous parlez des index par des exemples, le mieux aurait été de nous demander dans quel cas nous devons utiliser un index :
--> pour définir une relation entre deux tables (foreign key).
--> pour des questions de performances.
--> comment définir un ou plusieurs identifiants dans une table mysql.

@+

Citation:

---

Envoyé par Artemus24

Citation:

---

Envoyé par escartefigue

Bonjour

Une clef primaire est par définition unique, donc, définir à la fois une contrainte PK et une contrainte unique sur cette colonne est une redondance.

---

ceci est tout à fait vrai dans MySql, pas nécessairement dans d'autres SGBDR.
Par exemple :

Code:

---

unique index `idx` (`col1`,`col2`,`col3`)

---

---

bien sur que si : les caractéristiques d'une clef primaire sont d'être à la fois unique et non "nullable", ceci quel que soit le SGBD
Le fait qu'une clef primaire, comme toute autre clef, puisse s'appuyer sur plusieurs colonnes ne change rien à l'affaire.

Cf. l'article de fsmrel sur ce sujet

Salut Escartefigue.

Tu me parles de la finalité de ce que représente la clef primaire. Sur ce point, je suis d'accord.
La façon d'obtenir cette clef primaire est différente selon le SGBDR.
Définir un "Clustering index" est selon moi la bonne façon de définir une clef primaire.
Il y a qu'un seul "Clustering index" par table, tout comme il y a une seule clef primaire par table.
Le fait de définir une clef primaire ne conduit pas nécessairement à définir un "Clustering index".
Je ne connais pas suffisamment les autres SGBDR pour être sûr de mon affirmation.

@+

La clé primaire est de niveau logique, définie dans la norme du langage SQL.
Le "clustering index" est de niveau physique, défini par MySQL, avec ou sans équivalent dans d'autres SGBDR.

Comparons des choses comparables https://www.developpez.com/template/kit/kitwarning.png

Citation:

---

Envoyé par Artemus24

Tu me parles de la finalité de ce que représente la clef primaire. Sur ce point, je suis d'accord.
La façon d'obtenir cette clef primaire est différente selon le SGBDR.

---

Elle ne l'est guère : le DDL mentionne le mot-clef PRIMARY dans tous les SGBD-R mais ce n'est pas le sujet

Citation:

---

Envoyé par Artemus24

Définir un "Clustering index" est selon moi la bonne façon de définir une clef primaire.

---

Mais ça n'a absolument rien à voir, la clef primaire c'est la colonne ou le groupe de colonnes qui permet d'identifier de façon certaine une et une seule ligne dans la table
Cet identifiant est défini dès le stade conceptuel, lors de la réalisation du MCD.
L'index cluster est une notion physique, c'est l'index de rangement : après réorganisation, les lignes sont agencées selon cet index.
La ou les colonnes de l'index cluster peuvent tout à fait être différentes de celles de la PK. C'est même souvent un bon choix, tout dépend des types de requêtes effectuées sur la BDD

Citation:

---

Envoyé par Artemus24

Il y a qu'un seul "Clustering index" par table, tout comme il y a une seule clef primaire par table.

---

Oui et également un seul nom par table et un seul créateur par table, pour autant tous ces concepts sont sans aucun rapport entre eux

Salut Escartefigue.

Tu veux en venir où ?

@+

Bonjour,

Citation:

---

Envoyé par Artemus24

Salut Escartefigue.
Tu veux en venir où ?

---

À rectifier cette assertion qui est totalement fausse

Citation:

---

Envoyé par Artemus24

La façon d'obtenir cette clef primaire est différente selon le SGBDR.

---

L'identifiant primaire ou clef primaire ne saurait dépendre du SGBDR dans la mesure où, comme expliqué plus haut cette notion apparaît dès la modélisation conceptuelle et qu'on on la retrouve bien sur dans le modèle logique. Or, MCD et MLD sont déconnectés du choix du SGBD ;)

Salut Escartefigue.

Citation:

---

Envoyé par Escartefigue

... comme expliqué plus haut cette notion apparaît dès la modélisation conceptuelle et qu'on on la retrouve bien sur dans le modèle logique.

---

Je te rappelle que la question posée par Tanaka59 concerne l'aspect technique de MySql et non une question de norme ou de modélisation.
Voir son premier message où il demande : "J'ai un gros doute sur la conception lors des "CREATE TABLE" " ?

Citation:

---

Envoyé par Escartefigue

Or, MCD et MLD sont déconnectés du choix du SGBD

---

Autant c'est vrai sur ce que tu dis, autant tu es à coté de la question posée par Tanaka59.
Tanaka59 s'intéresse à l'aspect technique de MySql et non à sa modélisation.

Je soulève la question, à savoir si la "clef primaire" est un "index unique" comme les autres index uniques.
Dans un premier temps, je dis que je ne sais pas comment est traité la clef primaire dans les autres SGBDR.
Pourquoi, je me pose cette question ? J'aimerai savoir si dans les autres SGBDR, la clef primaire est gérée de la même façon ?

Et toi, tu me réponds modélisation ???

Je parle bien des SGBDR, et je m'interroge :
1) qu'est-ce qui fait que la clef primaire n'est pas un index unique comme les autres index uniques ?
A cela, je réponds "Clustering index".
--> https://dev.mysql.com/doc/refman/8.0...dex-types.html

2) a-t-on nécessairement besoin d'avoir une clef primaire dans le SGBDR MySql.
Je réponds non, car MySql va prendre le premier index unique qu'il va trouver.
Et en ce qui concerne "Clustering index" ?
S'il n'existe pas d'index unique ou de clef primaire, MySql va gérer en interne index clusterisé.
Autrement dit, les lignes seront rangées selon l'ordre d'insertion.

Est-ce une aberration de MySql ? Je réponds OUI car une clef primaire devrait être obligatoire dans une table, quelque soit le SGBDR.

3) on peut s'interroger sur la déclarative concernant la clef primaire.
Je me rappelle que dans DB2 gros système IBM, je devais associer à la "primary key" un index physique.
Or cette double déclaration n'existe pas dans MySql car la clef primaire est déjà un index unique, mais aussi un "Clustering index" !

Il est donc inutile pour une clef primaire dans mysql de créer aussi un index unique sur les mêmes colonnes.

@+

Bonjour,

Vin d'iou le débat est parti loin8O

Pour faire simple , si j'ai bien suivi :

Code:

---

1 2 3 4 5

PRIMARY KEY (la_champ_clef_primaire), UNIQUE KEY (la_combinaison_de_clef_unique), /* key (la_combinaison_de_clef_unique),   ou bien   index (la_combinaison_de_clef_unique), */

---

On met key ou bien index mais pas les deux.
Ce qui est dans primary key n'est pas à reporter dans key / index .
Selon les besoins on met ce qui est dans UNIQUE KEY dans key ou bien index.

Merci de vos lumières ;)

La primary key est une colonne ou une combinaison de colonnes qui permet d'identifier de façon certaine chaque ligne de la table.
Les autres clefs candidates peuvent également s'appuyer sur une ou plusieurs colonnes. Elles peuvent également être uniques, mais pas obligatoirement et elles peuvent éventuellement être "nullables" contrairement la PK.
Ce qui distingue une PK d'une clef candidate unique et "not null", c'est que la PK se propage dans d'autres tables via les contraintes d'intégrité.

Il est donc très important de choisir pour PK une colonne ou un groupe de colonnes dont le contenu est stable.
C'est la raison pour laquelle il faut utiliser une colonne technique, dépourvue de sens fonctionnel.
À cet effet, les identifiants attribués par le SGBD sont parfaits ("auto_increment" pour MySQL) : ils sont stables et concis, donc performants.

Exemple d'utilisation :
Table entreprise PK= colonne technique de type integer "auto_increment", AK non unique et nullable = n° de SIREN, AK unique et not null = numéro d'entreprise fonctionnel propre au S.I.

Pour le reste :
- il ne faut effectivement pas utiliser à la fois les mots clefs "KEY" et "INDEX" pour une même combinaison de colonnes, puisque pour MySQL ils sont équivalents.
- il ne faut pas ajouter de contrainte UNIQUE ou NOT NULL sur la PRIMARY KEY, car la PRIMAREY KEY est par définition UNIQUE et NOT NULL
- dans une base de données, il n'y a pas de champ, il y a des colonnes. Les champs sont les zones d'un état ou d'un formulaire

Pour ceux qui le peuvent je propose :

Faire le test sur :

MySQL
Maria DB
PosteGreSQL
SQL Server
Oracle
Access

et de donner vos retours ;)

Salut à tous.

Encore la valse des "-1", juste pour une divergence d'opinion.
Il n'y a que Escartefigue qui se permet de me corriger, et je le remercie.

@ Escartefigue : Je te rappelle que nous sommes dans le forum consacré à l'aspect technique de Mysql et non à la modélisation.
Il t'arrive fréquemment de faire des réponses concernant ls normes ou la modélisation alors que la question concerne des aspects techniques.

@ tanaka59 : il suffit de reprendre les exemples que j'ai donné.
KEY ET INDEX sont synonymes. Je préfère mettre "PRIMARY KEY" et "UNIQUE INDEX" plutôt qu'autre chose.

J'ai bien compris que tu fais de la provocation, mais dans ce forum consacré à MySql, on ne fait que du MySql et rien d'autre.

@+

UNIQUE INDEX et PRIMARY KEY sont deux choses différentes. En effet, PRIMARY KEY impose le NOT NULL, ce que UNIQUE ne fait pas.

A +

Citation:

---

Envoyé par Artemus24

@ Escartefigue : Je te rappelle que nous sommes dans le forum consacré à l'aspect technique de Mysql et non à la modélisation.
Il t'arrive fréquemment de faire des réponses concernant ls normes ou la modélisation alors que la question concerne des aspects techniques.

---

Ma première réponse (celle du 11-10 à 11h38) était d'ordre technique et ciblée sur la question initiale, ce n'est qu'ensuite que le sujet s'est élargi pour expliquer qu'il ne fallait aucunement confondre index cluster (considération technique et liée au SGBD) et identifiant primaire (considération qui apparaît dès le modèle conceptuel et sans lien avec le choix de tel ou tel SGBD)