Discussion :

[Shao17]

Bonjour,

J'ai plusieurs tables dans ma base de données mais pour l'exemple, je vais m'arrêter à 3 d'entre elles.
La base de données stocke des créations artistiques. Pour chaque oeuvre, outre son titre, on a un matériau utilisé (acrylique, fusain, ...) et un type d'oeuvre (dessin, peinture, ...). On a ainsi :
- 2 tables parentes : "Matter" et "Type"
- 1 table enfant : "Artwork"

Elles sont configurées ainsi :
Matter

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `Matter` (
  `idMatter` int(11) NOT NULL,
  `Matter` varchar(30) COLLATE latin1_general_ci NOT NULL,
  `DateModification` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Matter used in the work';
 
ALTER TABLE `Matter`
  ADD PRIMARY KEY (`idMatter`);
 
ALTER TABLE `Matter`
  MODIFY `idMatter` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

Type

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `Type` (
  `idType` int(11) NOT NULL,
  `Type` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
  `DateModification` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Type of work';
 
ALTER TABLE `Type`
  ADD PRIMARY KEY (`idType`);
 
ALTER TABLE `Type`
  MODIFY `idType` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

Artwork

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
CREATE TABLE `Artwork` (
  `id` int(11) NOT NULL,
  `Reference` varchar(9) COLLATE latin1_general_ci NOT NULL,
  `Title` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `Type` int(11) DEFAULT NULL,
  `Matter` int(11) DEFAULT NULL,
  `Support` int(11) DEFAULT NULL,
  `Dimensions` int(11) DEFAULT NULL,
  `Theme` int(11) DEFAULT NULL,
  `Filename` varchar(255) COLLATE latin1_general_ci DEFAULT NULL,
  `Year` year(4) DEFAULT NULL,
  `Status` int(11) NOT NULL,
  `DateCreation` timestamp NOT NULL DEFAULT current_timestamp(),
  `DatePublication` timestamp NULL DEFAULT current_timestamp(),
  `DateModification` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  `DateExpiration` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Art work';
 
ALTER TABLE `Artwork`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_Matter` (`Matter`),
  ADD KEY `fk_Support` (`Support`),
  ADD KEY `fk_Dimensions` (`Dimensions`);
 
ALTER TABLE `Artwork`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=96;

Problème rencontré :
Tandis que la clé étrangère s'est bien créée dans 'Artwork' pour le champ 'idMatter' de la table 'Matter'. En revanche, c'est pour la clé étrangère du champ 'Type' que je rencontre ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`devel_sh-art`.`#sql-69d_98a`, CONSTRAINT `fk_Type` FOREIGN KEY (`Type`) REFERENCES `Type` (`idType`) ON UPDATE CASCADE)
.

J'avais créé la clé étrangère par l'interface graphique de phpmyadmin. Devant l'erreur rencontrée, j'ai ensuite tapé en ligne de commande :

Code :

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

1
2
3
4
5
ALTER TABLE `Artwork`
ADD CONSTRAINT `fk_Type`
FOREIGN KEY (`Type`) REFERENCES `Type`(`idType`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

Toujours la même erreur bien sûr.

Au secours !

[tatayo]

Bonjour,

Tandis que la clé étrangère s'est bien créée dans 'Artwork' pour le champ 'idMatter' de la table 'Matter'.

Je ne vois pas de définition de clé étrangère dans la création de la table ArtWork (pas de foreign key sur cette colonne).

Visiblement lors de l'insertion d'une ligne dans la table ArtWork, tu renseignes un Type qui n'existe pas dans la table Type.
Peux-tu nous montrer la requête d'insertion, ainsi que la ligne de la table Type associée (avec le même IdType) ?

Tatayo.

[Shao17]

Bonjour Tatayo,

Je ne suis pas sûr qu'on se soit bien compris. En fait, je ne cherche pas à insérer un enregistrement mais plutôt à créer une clé étrangère.
La commande que je tape et qui me renvoie l'erreur 1452 est :

Code :

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

1
2
3
4
5
ALTER TABLE `Artwork`
ADD CONSTRAINT `fk_Type`
FOREIGN KEY (`Type`) REFERENCES `Type`(`idType`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

[Shao17]

Je ne comprends pas trop ce qu'il se passe. Je viens de taper ces commandes et ça a fonctionné :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER TABLE `Artwork` ADD KEY `fk_Type` (`Type`);
Query OK, 0 rows affected (0.085 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
ALTER TABLE `Artwork` ADD KEY `fk_Theme` (`Theme`);
Query OK, 0 rows affected (0.033 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
ALTER TABLE `Artwork` ADD KEY `fk_Theme` (`Status`);
ERROR 1061 (42000): Duplicate key name 'fk_Theme'
MariaDB [devel_sh-art]> ALTER TABLE `Artwork` ADD KEY `fk_Status` (`Status`);
Query OK, 0 rows affected (0.038 sec)
Records: 0  Duplicates: 0  Warnings: 0

Du coup, j'obtiens cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SHOW INDEXES FROM `Artwork`;
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Artwork |          0 | PRIMARY       |            1 | id          | A         |          95 |     NULL | NULL   |      | BTREE      |         |               |
| Artwork |          1 | fk_Matter     |            1 | Matter      | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| Artwork |          1 | fk_Support    |            1 | Support     | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
| Artwork |          1 | fk_Dimensions |            1 | Dimensions  | A         |          31 |     NULL | NULL   | YES  | BTREE      |         |               |
| Artwork |          1 | fk_Type       |            1 | Type        | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| Artwork |          1 | fk_Theme      |            1 | Theme       | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
| Artwork |          1 | fk_Status     |            1 | Status      | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.001 sec)

Ca a l'air bon.
Qu'en pensez-vous ?
Qu'est-ce qui n'allait donc pas dans la 1ère commande ?
Est-ce que le comportement va être correct lors de requêtes avec jointures ?

[tatayo]

En fait je m'étais arrêté au message d'erreur.

Mais quoi qu'il en soit je vois tout de même un différence entre la requête qui fonctionne:

Code :

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

1
2
 
ALTER TABLE `Artwork` ADD KEY `fk_Type` (`Type`);

et celle qui ne fonctionne pas:

Code :

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

1
2
3
4
5
6
 
ALTER TABLE `Artwork`
ADD CONSTRAINT `fk_Type`
FOREIGN KEY (`Type`) REFERENCES `Type`(`idType`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

La première ne fait qu'ajouter un index, alors que la deuxième ajoute une clé étrangère.
Ce n'est pas du tout la même chose.

Dans ton premier message tu indiques que "Tandis que la clé étrangère s'est bien créée dans 'Artwork' pour le champ 'idMatter' de la table 'Matter'.". Non, je ne vois pas de création de clé étrangère, mais juste d'une clé (un index).
Et idem dans ton dernier message.
Il n'y a donc aucun lien entre la table Artwork et les autres tables.
La seule requête qui fait le lien est celle qui ne fonctionne pas.
Il y a donc des données dans cette table qui ne permettent pas de créer la clé étrangère, des valeurs pour artwork.type qui n'existent pas dans type.idtype.
Tu peux les retrouver facilement:

Code :

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

1
2
3
4
5
 
select artwork.id
from artwork
left outer join type on type.idtype = artwork.fk_type
where type.idtype = nul

Tatayo.

[Shao17]

Merci Tatayo.

Je croyais en fait que comme apparaissait le fk_... dans la table Artwork pour les 3 clés, ça avait marché. Je trouve ça surprenant qu'il m'ait créé de simples index alors que j'avais renseigné les champs dans le phpmyadmin.

Donc j'ai essayé ta commande :

Code :

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

1
2
SELECT Artwork.id FROM Artwork LEFT OUTER JOIN Type ON Type.idType = Artwork.fk_Type WHERE Type.idType = NULL;
ERROR 1054 (42S22): Unknown column 'Artwork.fk_Type' in 'on clause'

J'ai corrigé Artwork.fk_Type par le nom de colonne et j'obtiens :

Code :

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

1
2
SELECT Artwork.id FROM Artwork LEFT OUTER JOIN Type ON Type.idType = Artwork.Type WHERE Type.idType = NULL;
Empty set (0.001 sec)