Discussion :

[asenaici]

Bonjour,

J'aimerais avoir des précisions sur les intégrités référentielles dans une base MySQL
J'ai une table maitre contenant des articles : Code_article (article A, article B, article C, article D)
J'ai une table détail contenant les ventes : no_ligne, Code_article (ligne 1 article B, ligne 2 article C, ligne 3 article D)
1) Si j'ai bien compris la notion d'intégrité référentielle entre ces 2 tables liés par un clé étrangère sur le code article (ON UPDATE NO ACTION, ON DELETE NO ACTION).
- je ne peux pas supprimer un article de la table maitre si il existe dans la table détail
- je ne peux pas supprimer un article de la table détail si il existe dans la table maitre
Est-ce exact ?

2) Autre situation : j'ai déjà une table maitre et une table détail contenant plusieurs centaines d'enregistrement
Il n'y a jamais eu d'intégrité référentielle : j'ai donc des articles dans la table détail qui n'existe plus dans la table maitre car supprimé depuis.
- Je suppose qu'il est normal que je ne puisse pas créer maintenant une intégrité référentielle puisqu'il manque des articles dans la table maitre ?

Hormis le fait d'ajouter les articles manquants dans la table maitre, existe-t-il un moyen de créer l'intégrité référentielle ?

Merci d'avance pou'r votre aide :-)

[SQLpro]

Vos tables sont une hérésie.... Pouvez-vous publier le DDL (ordre CREATE TABLE) de chacune de vos tables SVP ?

A +

[SQLpro]

1) Si j'ai bien compris la notion d'intégrité référentielle entre ces 2 tables liés par un clé étrangère sur le code article (ON UPDATE NO ACTION, ON DELETE NO ACTION).
- je ne peux pas supprimer un article de la table maitre si il existe dans la table détail
- je ne peux pas supprimer un article de la table détail si il existe dans la table maitre
Est-ce exact ?

Non

2) Autre situation : j'ai déjà une table maitre et une table détail contenant plusieurs centaines d'enregistrement

La notion d'enregistrement n'existe pas en matière de SGBDR. On parle de ligne.

Il n'y a jamais eu d'intégrité référentielle : j'ai donc des articles dans la table détail qui n'existe plus dans la table maitre car supprimé depuis.
- Je suppose qu'il est normal que je ne puisse pas créer maintenant une intégrité référentielle puisqu'il manque des articles dans la table maitre ?

Oui, sauf si votre SGBDR autorise la création de contraintes d'IR pour le futur (not trusted).

Hormis le fait d'ajouter les articles manquants dans la table maitre, existe-t-il un moyen de créer l'intégrité référentielle ?

Not Trusted si votre SGBDR le permet, sinon, supprimer les lignes orphelines avant de créer la contrainte

Merci d'avance pou'r votre aide :-)

A +

[asenaici]

Bonjour,

Pour rappel, jusqu'à présent il n'y a pas d'intégrité référentielle.
Quelques articles ont été supprimés de la table ARTICLE mais les "lignes" sont toujours présentes dans la table VENTES

Voici la DDL de la table ARTICLE (c'est sous MySQL)

Code :

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

1
2
3
4
CREATE TABLE ARTICLE (
  Code_article  varchar(20) NOT NULL PRIMARY KEY,
  Libelle       varchar(50) NOT NULL
) ENGINE = InnoDB;

Voici la DDL de la table VENTES

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
CREATE TABLE VENTES (
  No_commande    int NOT NULL,
  No_ligne       int NOT NULL,
  Code_client    varchar(10) NOT NULL,
  Date_commande  datetime NOT NULL,
  Code_article   varchar(20) NOT NULL,
  Quantite       int,
  Prix           float,
  TauxRemise     float,
  PrixNet        float,
  Montant        float,
  PRIMARY KEY (No_commande, No_ligne)
) ENGINE = InnoDB;
 
CREATE INDEX VENTES_Index01
  ON VENTES
  (Code_article);

[SQLpro]

Bien ce que je pensais, une hérésie...

Voici ce qui devrait être fait :

Code :

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

1
2
3
4
5
CREATE TABLE ARTICLE 
(No_article  INT PRIMARY KEY,
 Code_article varchar(20) NOT NULL UNIQUE,
 Libelle varchar(50) NOT NULL
) ENGINE = InnoDB;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE VENTES 
(No_vente INT PRIMARY KEY,
 No_commande int NOT NULL,
 No_ligne int NOT NULL,
 Code_client varchar(10) NOT NULL, --> devrait être en INT
Date_commande datetime NOT NULL,
 No_article   INT NOT NULL REFERENCES ARTICLE (No_article),
Quantite int,  --> en float !!!
Prix float,    --> en DECIMAL !!!
TauxRemise float,
PrixNet float,  --> en DECIMAL !!! 
Montant float, --> en DECIMAL !!!
UNIQUE (No_commande, No_ligne)
) ENGINE = InnoDB;

A +

[asenaici]

Merci pour votre proposition et en même temps très surpris.

Après avoir créer les nouvelles tables, un peu de lumière sur votre proposition SVP :

1- Que signifie le mot clé REFERENCES (No_article INT NOT NULL REFERENCES ARTICLE (No_article)) ?
Je n'ai rien dans les propriétés du champ No_article de la table VENTES !
J'ai l'impression que ça n'a aucun effet dans ma base MySql !
2- Pourquoi vous ajoutez un identifiant entier dans les 2 tables (No_article et No_vente) ?
Quelle différence entre No_vente et No_commande ?
3- Pourquoi prôner Decimal au lieu de Float ?
Pourquoi la quantité en Float et pas en Int ?

Merci d'avance pour votre aide.
Il me semble que j'ai encore beaucoup à apprendre sur le SQL

[asenaici]

Bonjour,
Finalement, j'ai bien compris la partie REFERENCES : Merci pour votre cours dans ce site sur le SQL.
Mais j'ai toujours du flou pour certaines choses :

1- Pourquoi vous ajoutez un identifiant entier dans les 2 tables (No_article et No_vente) ?
Quelle différence y-a-t-il à utiliser No_vente au lieu de No_commande ou No_article au lieu de Code_article ?
2- Pourquoi prôner Decimal au lieu de Float ?
Pourquoi la quantité en Float et pas en Int ?

Merci d'avance pour votre aide.
Encore bravo pour votre doc sur le SQL sur ce site.
Cordialement

[Artemus24]

Salut à tous.

1- Que signifie le mot clé REFERENCES (No_article INT NOT NULL REFERENCES ARTICLE (No_article)) ?

C'est la déclarative pour une clef étrangère.

SQLPRO a choisit de mettre la reference sur la colonne servant de clef praimaire :

Code :

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

No_article   INT NOT NULL REFERENCES ARTICLE (No_article),

Personnellement, je préfère scinder en deux la déclarative comme ci-après :

Code :

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

1
2
No_article integer unsigned not null,
CONSTRAINT `FK_ARTICLE` FOREIGN KEY (`No_article`) REFERENCES `article` (`No_article`) ON DELETE CASCADE ON UPDATE CASCADE

Je sais, c'est un peu plus bavard, mais je préfère cette forme d'écriture.

Je n'ai rien dans les propriétés du champ No_article de la table VENTES !

Il est préférable de créer une clef technique, de type integer auto_incrémenté plutôt que de faire une concatenation d'identifiant.
Cela moins couteux en volumétrie, mais plus rapide à l'accès.

J'ai l'impression que ça n'a aucun effet dans ma base MySql !

Quoi donc ?

2- Pourquoi vous ajoutez un identifiant entier dans les 2 tables (No_article et No_vente) ?

Votre clef primaire "No-article" va servir en tant que clef étrangère dans la table ventes, il est préférable qu'elle soit simple, et unique et n'évoluant pas avec le temps.
La colonne code_article est de type varchar, et est déconseillé à cause de sa volumétrie (20 caractères au lieu de 4 octets), de sa complexité (celle d'une chaine de caractères), et pouvant évoluer dans le temps.
Avec votre colonne code_article, vous pouvez rencontrer un joueur un problème d'intégrité de votre base.

Quelle différence entre No_vente et No_commande ?

La colonne No_vente est une clef primaire technique, plus facile à gérer et unique.
Elle vient en remplacement de votre clef primaire composée des colonne No_commande et No_ligne.

De ce fait, la nouvelle clef primaire est la colonne No_Vente et votre ancienne clef primaire (No_Commande ; No_ligne) devient un undex unique.

3- Pourquoi prôner Decimal au lieu de Float ?

FLOAT est pour un usage scientifique et sa précision est approximative (un arrondi sur la dernière décimale).
DECIMAL donne un calcul exacte et est destiné à un usage comptable.

Il est plus que souhaité de toujours utiliser pour vos calculs le type decimal.

Pourquoi la quantité en Float et pas en Int ?

Ne jamais mettre float.

Si la quantité est petite, et toujours positive, vous pouvez mettre "tinyint unsigned" qui correspond à 1 octet.
si vous avez besoin d'un peut plus grand, mettez "smallint unsigned" qui correspond à deux octets.
--> https://dev.mysql.com/doc/refman/5.7...ger-types.html

Parce qu'un entier est traité beaucoup plus rapidement qu'une chaîne de caractères par le SGBD, surtout si elle est longue.

C'est vrai mais l'autre raison est que le code article peut changer au cours du temps.

Je suis un peu circonspect là-dessus aussi. Si vos quantités sont toujours entières, choisissez INTEGER.

Ne jamais utilise un float pour une quantité.
Il existe d'autre type que integer offrant une économie de place.
Voire le lien que j'ai donné ci-dessus.

Si elles peuvent être fractionnaires (2/3 par exemple) ça peut être utile d'utiliser FLOAT

Certainement pas ! Dans tous les cas, utilisez le type decimal.
Je rappelle qu'une quantité est toujours entière.

Si pour vos calculs, vous avez besoin de faire un arrondi, vous pouvez utiliser la fonction "round()".
--> https://dev.mysql.com/doc/refman/5.7...function_round

@+

[CinePhil]

Je rappelle qu'une quantité est toujours entière.

N'importe quoi !
Ça dépend de l'unité utilisée.
Si l'unité de base est la tonne mais que j'en veux 200 kg => 0,2 t.
Les quantités horaires sont souvent exprimées en décimal : temps de pose unitaire d'un mètre de câble U1000 RO2V 3G1.5 = 0,08 h, par exemple. Regardez votre prochaine facture de garagiste et, fort heureusement pour vous, vous y trouverez sans doute des quantités d'heures (voire de matière) exprimées en décimal.

Et attention aux arrondis ! L'arrondi de la somme n'est pas forcément égal à la somme des arrondis.

[Artemus24]

Salut CinePhil.

Je ne savais pas que l'on pouvait acheter 2/3 d'une boite de conserve ou 0.7 litres de lait au supermarché.

Et attention aux arrondis ! L'arrondi de la somme n'est pas forcément égal à la somme des arrondis.

Vous êtes en forme aujourd'hui.

@+

[CinePhil]

Sans doute n'avez-vous jamais acheté 0,800 kg de tomates ou 1,2 kg de pommes ?

[Artemus24]

Salut CinePhil.

Je vous rappelle que le poids n'est pas une quantité, et donc votre exemple est mauvais.
La quantité désigne le nombre de tomates ou de pommes que j'achète. Et bien sûr, je les achète entières et non en fraction.

@+

[CinePhil]

Je vous rappelle que le poids n'est pas une quantité

[escartefigue]

La quantité désigne le nombre de tomates ou de pommes que j'achète. Et bien sûr, je les achète entières et non en fraction.

Les règles de gestion sont propres à chaque S.I. il n'y a donc aucune raison d'exclure a priori une quantité fractionnaire
Si mon fournisseur m'autorise à acheter 10,55 m de moquette, ou mon boulanger de lui prendre une demi baguette, alors tout va bien

[Artemus24]

Salut à tous.

Les règles de gestion sont propres à chaque S.I. il n'y a donc aucune raison d'exclure a priori une quantité fractionnaire

Je ne parle pas des règles de gestion, mais sur ce point vous avez raison. Je parle des définitions !

Le problème est que CinePhil confond quantité et mesure !
Même les dictionnaires les désignent comme synonymes alors que ce n'est pas le cas.

Une quantité, c'est ce que l'on compte, ce qui déterminer le nombre. A moins de me tromper, un nombre désigne un entier.
De plus, une quantité n'a pas d'unité.
Quand je vais au supermarché, j'achète 15 pommes. Pomme ne représente pas une unité et je ne peux pas prendre une fraction de ces pommes.
J'aurai le même raisonnement avec des boîtes de conserves, des personnes ...

Inversement, une mesure est toujours associées à une unité. La mesure peut être une fraction de cette unité.
En poids, mes pommes représentent 1,50 Kg. L'unité ici est le Kilogramme.

Connaissant ces définitions, il est normal d'attribuer à quantité un nombre entier et à la mesure un décimal.

@+

[escartefigue]

A moins de me tromper, un nombre désigne un entier.

Les entiers naturels et les entiers relatifs oui, les autres nombres non : les décimaux, réels et complexes sont des nombres mais pas des entiers

En dehors de ces considérations mathématiques, ce qui importe au niveau base de données, c'est de stocker l'information selon un type et une longueur qui permette de la restituer en toute intégrité et notamment sans perte de décimales
Donc si le besoin est de restituer une valeur fractionnaire il faut utiliser un type DECIMAL(m,n) (ou numéric dans certains SGBD).

L'une des règles d'or de la modélisation est de se mettre d'accord sur la terminologie. Pour dissiper tout malentendu, il suffit donc de préciser en préambule du dictionnaire de données ce que l'on entend par "quantité" et basta