Problème de FOREIGN KEY Can't create table xyz (errno: 150 "Foreign key constr

**cjean123** · 19/02/2018, 15h00

Bonjour à tous,
Depuis ce matin je m'arrache les cheveux sur le problème suivant :
J'ai 2 Tables :
La première collecte les données des charges d'un site client, comprendre Charge au sens comptable. Voici sa DDL :

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 `cus_sit_cha` (
	`cus_sit_ide` VARCHAR(45) NOT NULL COMMENT 'Identifiant du site client',
	`cus_ide` INT(11) NOT NULL COMMENT 'Identifiant du client',
	...
	`cus_sit_cha_des` VARCHAR(250) NOT NULL COMMENT 'Description de la charge',	`cat_ide` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Id. catalogue',
	...
        .....
	`cus_sit_cha_deb` DATE NOT NULL COMMENT 'Date de debut d\'utilisation de la charge ',
	`cus_sit_cha_fin` DATE NOT NULL COMMENT 'Date de fin d\'utilisation de la charge',
	`cus_sit_cha_ter` TINYINT(4) UNSIGNED NOT NULL DEFAULT '',
         ...
         ...
	`cus_sit_cha_per` TINYINT(4) UNSIGNED NOT NULL DEFAULT '',	
        PRIMARY KEY (`cus_sit_ide`, `cus_ide`, `cus_swi_ide`, `cat_ide`, `cus_sit_cha_deb`),
	INDEX `idx_sel_cha` (`cus_ide`),
	INDEX `cat_cus_sit_cha` (`cat_ide`),	CONSTRAINT `cat_cus_sit_cha` FOREIGN KEY (`cat_ide`) REFERENCES `cat` (`cat_ide`) ON UPDATE CASCADE,
	CONSTRAINT `cus_sit_cus_sit_cha` FOREIGN KEY (`cus_sit_ide`, `cus_ide`, `cus_swi_ide`) REFERENCES `cus_sit` (`cus_sit_ide`, `cus_ide`, `cus_swi_ide`) ON UPDATE CASCADE)
COMMENT='Charges rattachées au site géographique du client'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Celle-ci est reliée à sa table correspondante pour gérer les charges par des devis. Voici sa DDL :

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 `cus_sit_cha_devis` (
	`cus_sit_ide` VARCHAR(45) NOT NULL COMMENT 'Identifiant du site client',
	`cus_ide` INT(11) NOT NULL COMMENT 'Identifiant du client',
	`cat_ide` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'Id. catalogue',
	`cha_deb` DATE NOT NULL COMMENT 'Date de debut d\'utilisation de la charge ',
	`cus_sit_ide_devis` VARCHAR(45) NOT NULL,
	...
	`author_ide` INT(11) NOT NULL,
	PRIMARY KEY  (`cus_ide`, `cus_sit_ide`, `cat_ide`, `cha_deb`),
	CONSTRAINT `cus_sit_cha_devis_author_fk_cus` FOREIGN KEY (`author_ide`) REFERENCES `cus` (`cus_ide`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Au passage, vous aurez compris qu'il y a des champs clé primaire composites. La réflexion est la suivante : une charge d'un site client peut passer par un devis donc dans ce cas, l'ID de la charge est recopié dans la table des devis. Mais il se peut que la charge soit facturée directement, donc elle ne sera pas recopiée dans la table correspondante. IL ME FAUT DONC UNE RELATION DE 1 , 1 à 0 , 1 !!!
Je veux relier ces 2 deux tables par une relation de (de cus_sit_cha)1,1 ==> (vers cus_sit_cha_devis) 0,1

Et depuis ce matin j'essaie de créer la contrainte suivante :

Code :

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

1
2
3
4
5
6
ALTER TABLE `cus_sit_cha_devis`
	ADD CONSTRAINT `cus_sit_cha_cus_sit_cha_devis` 
	FOREIGN KEY (`cus_sit_ide`, `cus_ide`,  `cat_ide`, `cha_deb`)
	REFERENCES `cus_sit_cha` (`cus_sit_ide`, `cus_ide`,  `cat_ide`, `cus_sit_cha_deb`)
	ON UPDATE CASCADE 
	ON DELETE CASCADE;

Mais à l'exécution de cette requête, j'ai le warning suivant qui s'affiche :
"Warning"
"150"
"Alter table '`Mydatabase`.`cus_sit_cha_devis`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' FOREIGN KEY (`cus_sit_ide`, `cus_ide`, `cat_ide`, `cha_deb`) REFERENCES `cus_sit_cha` (`cus_sit_ide`, `cus_ide`, `cat_ide`, `cus_sit_cha_deb`)'.

Pour information, je suis sous

Code :

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

10.2.10-MariaDB-10.2.10+maria~jessie

Comment faire aboutir la créatoin de cette relation ?

Merci à tous ceux qui se pencheront sur mon problème.

**al1_24** · 19/02/2018, 15h34

Regarde bien la composition de la clé primaire de la table référencée et compare la avec les colonnes de la clé étrangère... Tu comprendras où est ton erreur

**cjean123** · 19/02/2018, 15h51

Ben c'est ce que j'ai fait

cus_sit_cha	cus_sit_cha_devis
`cus_sit_ide` VARCHAR(45) NOT NULL	`cus_sit_ide` VARCHAR(45) NOT NULL
`cus_ide` INT(11) NOT NULL	`cus_ide` INT(11) NOT NULL
`cat_ide` VARCHAR(255) NOT NULL DEFAULT ''	`cat_ide` VARCHAR(255) NOT NULL DEFAULT ''
`cus_sit_cha_deb` DATE NOT NULL	`cha_deb` DATE NOT NULL

Il y a juste le dernier champ qui ne porte pas le même nom. Mais ce n'est pas un impératif, j'ai plein d'exemples sur ce sujet. Et en plus, si le concepteur avait bien suivi la nomenclature il aurait du l'appeler cus_sit_cha_devis_cha_deb (le fainéant).

Merci de me mettre sur la voie .

**SQLpro** · 19/02/2018, 16h19

Envoyé par cjean123

Bonjour à tous,
Depuis ce matin je m'arrache les cheveux sur le problème suivant :
J'ai 2 Tables :
La première collecte les données des charges d'un site client, comprendre Charge au sens comptable. Voici sa DDL :

Code :

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

1
2
3
4
5
6
7
8
9
 CREATE TABLE `cus_sit_cha` ...
        PRIMARY KEY
                              (`cus_sit_ide`, `cus_ide`, `cus_swi_ide`, `cat_ide`, `cus_sit_cha_deb`)
...
ALTER TABLE `cus_sit_cha_devis`
...
	REFERENCES `cus_sit_cha`
                              (`cus_sit_ide`, `cus_ide`,  `cat_ide`, `cus_sit_cha_deb`)
...;

Il te faut sans doute des lunettes !!!

A +

**cjean123** · 19/02/2018, 16h34

La mise en relation porte exclusivement sur les champs (`cus_sit_ide`, `cus_ide`, `cat_ide`, `cus_sit_cha_deb`/'cha_deb')

le champ `cus_swi_ide` ne fait pas partie de cette mise en relation.

Et pour rappel, on met en relation des champs si possible indexés, mais pas obligatoirement : j'ai déjà fait des relations avec des champs non indexé, ça fonctionne bien hormis des performance nulles à chi....
Or dans le cas d'un clé primaire composite, chaque champ est indexé avec possibilité de doublons mais la combinaison ne doit jamais se répéter.

Ceci dit, toujours pour respecter la structure des champs de la base, j'avais proposé l'ajout de ce champs `cus_swi_ide`, histoire de faire enrager le copain qui va faire la mise en prod

)))))

**cjean123** · 19/02/2018, 16h46

Ceci dit, SQL Pro, tu as raison : c'est la clé primaire qui défini le côté 1 (car unique) si je ne prend qu'une partie des champs, il n'y a plus unicité.
Donc la relation ne peut pas être définie et surtout pas en 1.

Donc il faut que je rajoute ce champs

Donc le collègue devoir déployer tout dans les 134 base de données

(Le pire c'est qu'il m'a pris de haut

**SQLpro** · 19/02/2018, 18h49

Beaucoup de choses à dire
1) l'intégrité référentielles n'a rien à voir avec les relations. C'est le mécanisme qui permet d'assurer que les associations entre les entités sont physiquement représentées avec des règles de gestion adéquates (mode NO ACTION, CASCADE, SET DEFAULT, SET NULL pour les mises à jour et appairage avec MATCH FULL ou PARTIAL ou SIMPLE)
2) les relations au sens de bases de données relationnelles sont des objets mathématiques porteurs de données (en fait ce sont des tables d'un type particulier (pas de NULL, présence obligatoire d'une clef, informations toujours atomique - cf règles de normalisation)
3) les contraintes d'IR peuvent être déclaratives (donc une commande SQL de type ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES ...) soit fonctionnelles (faites à coups de déclencheurs)
4) dans le cas d'une contrainte de clef étrangère la référence devant être unique, elle porte obligatoirement sur une clef PRIMAIRE ou bien une contrainte d'unicité. Pour cela le vecteur définissant la clef doit être identique (même nombre de colonnes, même ordre des colonnes) de part et d'autres entre référence et table fille.

Révisez votre cours sur le SQL, parce que vous mélangez tous les concepts et c'est pour cela que vous faites des bêtises... Mon livre comme mon site web peuvent vous y aider...
Nom : Couverture SQL Synthex 4e ed - 500.jpg
Affichages : 105
Taille : 77,8 Ko

Nom : Couverture SQL Synthex 4e ed - 500.jpg
Affichages : 105
Taille : 77,8 Ko

**escartefigue** · 19/02/2018, 20h27

Et la pk qui commence par un varchar de 45 et y ajoute 4 ou 5 colonnes supplémentaires, c'est complètement pourri aussi

**Artemus24** · 21/02/2018, 18h07

Salut cjean123.

Envoyé par cjean123

Depuis ce matin je m'arrache les cheveux sur le problème suivant :

attention à ne pas devenir chauve !

En reprenant les conseils de al1_24, voici ce qu'il faut faire :

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `cus_sit_cha`
--------------
 
--------------
CREATE TABLE `cus_sit_cha`
( `cus_sit_ide`        VARCHAR(45)         NOT NULL            COMMENT 'Identifiant du site client',
  `cus_ide`            INT(11)             NOT NULL            COMMENT 'Identifiant du client',
  `cus_swi_ide`        INT(11)             NOT NULL            COMMENT 'Identifiant du client',
  `cus_sit_cha_des`    VARCHAR(250)        NOT NULL            COMMENT 'Description de la charge',
  `cat_ide`            VARCHAR(255)        NOT NULL DEFAULT '' COMMENT 'Id. catalogue',
  `cus_sit_cha_deb`    DATE                NOT NULL            COMMENT 'Date de debut d\'utilisation de la charge ',
  `cus_sit_cha_fin`    DATE                NOT NULL            COMMENT 'Date de fin d\'utilisation de la charge',
  `cus_sit_cha_ter`    TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
  `cus_sit_cha_per`    TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`cus_sit_ide`, `cus_ide`, `cus_swi_ide`, `cat_ide`, `cus_sit_cha_deb`),
  INDEX `idx_sel_cha`     (`cus_ide`),
  INDEX `cat_cus_sit_cha` (`cat_ide`)
)
COMMENT='Charges rattachées au site géographique du client'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
--------------
 
--------------
DROP TABLE IF EXISTS `cus_sit_cha_devis`
--------------
 
--------------
CREATE TABLE `cus_sit_cha_devis`
( `cus_sit_ide`        VARCHAR(45)   NOT NULL            COMMENT 'Identifiant du site client',
  `cus_ide`            INT(11)       NOT NULL            COMMENT 'Identifiant du client',
  `cus_swi_ide`        INT(11)       NOT NULL            COMMENT 'Identifiant du client',
  `cat_ide`            VARCHAR(255)  NOT NULL DEFAULT '' COMMENT 'Id. catalogue',
  `cha_deb`            DATE          NOT NULL            COMMENT 'Date de debut d\'utilisation de la charge ',
  `cus_sit_ide_devis`  VARCHAR(45)   NOT NULL,
  `author_ide`         INT(11)       NOT NULL,
  PRIMARY KEY (`cus_ide`, `cus_sit_ide`, `cat_ide`, `cha_deb`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
--------------
 
--------------
ALTER TABLE `cus_sit_cha_devis`
  ADD CONSTRAINT `cus_sit_cha_cus_sit_cha_devis`
    FOREIGN KEY              (`cus_sit_ide`, `cus_ide`, `cus_swi_ide`, `cat_ide`, `cha_deb`)
    REFERENCES `cus_sit_cha` (`cus_sit_ide`, `cus_ide`, `cus_swi_ide`, `cat_ide`, `cus_sit_cha_deb`)
    ON UPDATE CASCADE ON DELETE CASCADE
--------------
 
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Sinon quelques remarques :

1) quel est l'intérêt d'utiliser des clefs primaires composites ?
Cela alourdit inutilement la clef primaire !
Pourquoi ne par utiliser une clef primaire technique (auto incrémenté) ?

2) un varchar() dans une clef primaire n'est pas ce que l'on peut faire de mieux en terme de performance.

3) default '' (vide) sur une colonne numérique ???
Je déconseille cette façon de faire. Autant mettre lors de l'insertion la valeur par défaut.

4) aucun intérêt à utiliser des "default".

5) quel est l'intérêt de mettre des commentaires sur vos colonnes ? Aucun.
Normalement, vous devez avoir en votre possession une documentation complète sur votre base de données qui doit reprendre le dictionnaire de données, ainsi que les requêtes.
Dois-je comprendre que le travail de modélisation n'a jamais été entrepris ?

6) les noms à rallonge, j'aime pas du tout ! Cela devient vite illisible.
Le mieux est de mettre un suffixe pour désigner le nom de la colonne et utiliser un préfixe pour indiquer le nom de la table.
Ce genre de travail aurait dû être fait lors de la constitution du dictionnaire de données.

7) vous avez parlé "des charges d'un site client", au sens comptable. Et que voie-je ?
Des suppressions en cascade qui sont autorisées !
Si c'est comptable, on ne doit jamais supprimer mais annuler.

8) aucune compression des données a été utilisé dans la déclarative de vos tables.
Quel est la volumétrie de vos tables ?

9) Pourquoi utiliser 'latin1_swedish_ci' ? Etes-vous suédois ?

10) votre debian est en jessie. Il faudrait envisager d'évoluer dans la dernière version qui est la stretch ? C'est la version 9.3.

@+

Problème de FOREIGN KEY Can't create table xyz (errno: 150 "Foreign key constr

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager