Discussion :

[CliffeCSTL]

Bonjour,

je crée ma première base et j'ai quelques questions

le code généré par MySql :

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
22
23
24
25
26
27
28
29
30
 
-- -----------------------------------------------------
-- Table `myDb`.`INTERVENANTS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `myDb`.`INTERVENANTS` ;
 
CREATE TABLE IF NOT EXISTS `myDb`.`INTERVENANTS` (
  `NIR` INT NOT NULL,
  PRIMARY KEY (`NIR`),
  UNIQUE INDEX `ID_INTERVENANT_UNIQUE` (`NIR` ASC))
ENGINE = InnoDB;
 
-- -----------------------------------------------------
-- Table `myDb`.`IDENTIFIANTS`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `myDb`.`IDENTIFIANTS` ;
 
CREATE TABLE IF NOT EXISTS `myDb`.`IDENTIFIANTS` (
  `NIR` INT NOT NULL,
  `NOM` VARCHAR(255) NOT NULL,
  `PRENOM` VARCHAR(255) NOT NULL,
  `TELEPHONE` INT NULL,
  `MAIL` VARCHAR(255) NULL,
  PRIMARY KEY (`NIR`),
  CONSTRAINT `fk_IDENTIFIANTS_INTERVENANTS1`
    FOREIGN KEY (`NIR`)
    REFERENCES `myDb`.`INTERVENANTS` (`NIR`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Une ligne dans la table IDENTIFIANTS ne doit exister que si le NIR correspondant existe dans la table INTERVENANTS.
De la même manière un NIR dans la table INTERVENANTS impose l’existence d'une ligne dans IDENTIFIANTS.

1. Pourquoi MySql ne m'a pas généré de contrainte de clé étrangère dans la table INTERVENANTS ?
2. Lorsque j'insère une ligne dans INTERVENANTS le moteur accepte sans broncher alors qu'il n'y a pas la ligne correspondante dans IDENTIFIANTS (je suppose que cela vient du 1.)
3. Plus tard, je souhaite développer un programme en Java pour insérer des valeurs dans mes tables. A quel niveau doit se déroulé la vérification de la cohérence des données ?

[al1_24]

Si je comprends bien ce que tu exprimes : pour ajouter une valeur de NIR dans INTERVENANTS, elle doit exister au préalable dans IDENTIFIANTS mais avant de l'ajouter dans IDENTIFIANTS, il faut qu'elle existe dans INTERVENANTS.
Ces deux règles ne seraient elles pas contradictoires ?

[escartefigue]

D'accord avec AL1_24 et pourquoi coder on delete/on update no action vous acceptez que la suppression ou modification de l'entité mère 'intervenants' laisse des orphelins dans l'entité fille 'identifiants' ?

[CliffeCSTL]

Si je comprends bien ce que tu exprimes : pour ajouter une valeur de NIR dans INTERVENANTS, elle doit exister au préalable dans IDENTIFIANTS mais avant de l'ajouter dans IDENTIFIANTS, il faut qu'elle existe dans INTERVENANTS.
Ces deux règles ne seraient elles pas contradictoires ?

C'est pas comme ça que l'on fait ?
En supprimant les les contraintes avant une insertion ... et ensuite en les réactivant ?

[Artemus24]

Salut CliffeCSTL.

Une ligne dans la table IDENTIFIANTS ne doit exister que si le NIR correspondant existe dans la table INTERVENANTS.

Pour activer cette règle, vous devez définir une "foreign key" (une clef étrangère en français) dans la table "IDENTIFIANTS".
La table "INTERVENANTS" est la table père, et la table "IDENTIFIANTS" est la table fils.

De la même manière un NIR dans la table INTERVENANTS impose l’existence d'une ligne dans IDENTIFIANTS.

Ce que vous définissez ici, est l'exacte opposé de la règle ci-dessus.
Donc non, vous ne pouvez pas mettre ces deux règles, telle que vous les formulez, car cela signifie que vous avez un "dead lock".

C'est pas comme ça que l'on fait ?

Il vous faut choisir entre l'une ou l'autre de ces deux clefs étrangères.
Si la relation est 1 - 1, le mieux est de mettre un "unique index" sur la colonne "nir".

En supprimant les contraintes avant une insertion ... et ensuite en les réactivant ?

Certainement pas !

C'est à vous de faire le choix qui vous convient le mieux.
Si vous décidez de créer une contrainte d'intégrité dans MySql, il est logique de la laisser active.
Sinon pourquoi introduire une contrainte si vous ne vous en servez pas ?

Quand quelque chose ne va pas dans la conception de votre base de données, revenez aux définitions régissant vos données.

La table "identifiants" est clair dans la façon dont vous la gérer. Elle stocke les caractéristiques d'une personne.

Par contre la table "intervenants" n'est pas clair dans sa définition.
Est-ce une extension de la table "identifiants" où l'on met des données qui sont peu consultés ?
Dans ce cas, pour rendre le lien "identifiants - intervenants" unique sur la colonne "NIR", il suffit de créer un index unique sur cette colonne dans la table "intervenants".
Je vous donne la solution tel que je l'ai compris :

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `mydb`
--------------
 
--------------
CREATE DATABASE `mydb`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `identifiant`
--------------
 
--------------
CREATE TABLE `identifiant`
(
  `nir`           integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `nom`           varchar(255)     NOT NULL,
  `prenom`        varchar(255)     NOT NULL,
  `telephone`     varchar(255)     NOT NULL,
  `mail`          varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `identifiant` (`nom`,`prenom`,`telephone`,`mail`) VALUES
  ('Marx', 'Karl', '01-23-45-67-89', 'karl.marx@hotmail.com'),
  ('Blum', 'Léon', '02-34-56-78-91', 'leon.blum@hotmail.com')
--------------
 
--------------
select * from identifiant
--------------
 
+-----+------+--------+----------------+-----------------------+
| nir | nom  | prenom | telephone      | mail                  |
+-----+------+--------+----------------+-----------------------+
|   1 | Marx | Karl   | 01-23-45-67-89 | karl.marx@hotmail.com |
|   2 | Blum | Léon   | 02-34-56-78-91 | leon.blum@hotmail.com |
+-----+------+--------+----------------+-----------------------+
--------------
DROP TABLE IF EXISTS `intervenant`
--------------
 
--------------
CREATE TABLE `intervenant`
(
  `nir`           integer unsigned NOT NULL primary key,
  `commentaire`   varchar(255)     NOT NULL,
  CONSTRAINT `FK_NIR` FOREIGN KEY (`nir`) REFERENCES `identifiant` (`nir`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `IX_NIR` UNIQUE INDEX USING BTREE (`nir`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `intervenant` (`nir`,`commentaire`) VALUES  (1, 'Marx - Karl')
--------------
 
--------------
INSERT INTO `intervenant` (`nir`,`commentaire`) VALUES  (2, 'Blum - Léon')
--------------
 
--------------
INSERT INTO `intervenant` (`nir`,`commentaire`) VALUES  (1, 'Marx - Groucho')
--------------
 
ERROR 1062 (23000) at line 69: Duplicata du champ '1' pour la clef 'PRIMARY'
--------------
select * from intervenant
--------------
 
+-----+-------------+
| nir | commentaire |
+-----+-------------+
|   1 | Marx - Karl |
|   2 | Blum - Léon |
+-----+-------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

[CliffeCSTL]

Merci pour vos réponses.

Pour ma 3ème question, à quel niveau faut-il faire les tests sur les données ? Dans une procédure stockée ?

[escartefigue]

Tout dépend de ce que vous voulez vérifier, s'il s'agit uniquement de s'assurer de la présence d'un identifiant dans une table mère avant de l'ajouter dans une table fille, afin de préserver l'intégrité de la base de données, alors les contraintes suffisent.
Si vous voulez ajouter une liste fermée de valeurs possibles, vous pouvez aussi passer par des contraintes mais de type check
Si vous avez des règles plus sioux, vous pouvez utiliser des triggers

[CliffeCSTL]

Je cherche à avoir les bonnes pratiques.

Par exemple, si je souhaite faire un INSERT d'une ligne dans une table :

Code :

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

1
2
statement.executeUpdate("INSERT INTO Client VALUES (999)");
statement.executeUpdate("INSERT INTO Client VALUES (999)");

sachant que les valeurs de la colonne sont unique. Faut-il faire le test avant INSERT ou bien traiter les erreurs remontées par MySql ?

[escartefigue]

Et bien ça dépend du contexte

1. vérifiez les règles et normes de développement du site sur lequel vous intervenez
2. si votre colonne est de type identifier, c'est à dire calculée par le SGBD, et que vous insérez sur la table mère, ne fournissez pas la valeur elle sera attribuée automatiquement
3. si votre colonne est de type identifier mais que vous êtes sur une table fille qui hérite de l'identifiant de la table mère, utilisez la fonction LAST_INSERT_ID() pour récupérer la valeur
4. si votre colonne est identifiante, et que c'est à vous de fournir la valeur, faute de norme interne, vous pouvez tester l'unicité avant l'insert ou pas, mais quoi qu'il arrive, vous devrez vérifier le diagnostic post insert, il est en effet possible qu'une autre transaction en parallèle ait ajouté la valeur de clef dans l'intervalle

Dans tous les cas, quand vous faites des insert ou update, il est préférable de fournir la liste des colonnes impactées avant les valeurs, pour éviter que le résultat change en cas de modif de DDL, et c'est aussi plus clair.

Donc préférez la syntaxe "insert into ma table (col1, col2... coln) values (val1, val2, ... valn)"

[Artemus24]

Salut à tous.

Je cherche à avoir les bonnes pratiques.

Vaste sujet où il y aura autant de réponses que d'intervenant.

Faut-il faire le test avant INSERT ou bien traiter les erreurs remontées par MySql ?

Votre question est mal posé.

Si l'on fait un INSERT, c'est qu'a priori, on pense que l'insertion ne va poser aucun problème dans votre traitement.
Ou alors, vous vous êtes trompés de requête pour le travail que vous cherchez à faire.

Il existe une autre requête qui permet de faire un INSERT si la clef primaire n'existe pas ou au contraire un UPDATE si elle existe.
Il s'agit du REPLACE : http://dev.mysql.com/doc/refman/5.7/en/replace.html

A vrai dire, il y a fort longtemps, vous deviez faire le test de l'existence avant soit de faire un INSERT ou soit un UPDATE.
Cette requête est bien pratique et évite trop de se poser ds questions.

Cela dépend comme le dit Escartefigue du contexte et de ce que vous désirez faire.

En général, on évite de provoquer une erreur dans un traitement, si justement on peut la résoudre par ailleurs.

Quand je travaillais sous gros système IBM, donc en cobol, si je rencontrais un cas de figure que je ne savais pas traité, alors on faisait planter le programme, avec un compte-rendu sur la cause de cette anomalie.
Ensuite, on nous demandait de ne plus procéder ainsi, mais de faire du recyclage, c'est-à-dire de ne pas traiter la ligne et de la mettre dans un fichier spécial pour quelle soit analyser par un utilisateur responsable.
Consigne imposée par l'exploitation afin de ne plus chercher à résoudre des problèmes fonctionnels.

@+