Clés etrangères sous MySQL

Version imprimable

13/02/2008, 19h19
Targan

Clés etrangères sous MySQL
Bonsoir,

je pose une question qui revient peut etre souvent mais malgre qq recherches je n ai pas vraiment trouvé de reponses satisfaisantes.

Voila, je vous expose ma problematique qui est assez simple et assez souvent vu :

creation de plusieurs tables en cascade facon arborescence et je voudrais y inclure un champ qui indique si un user verrouille le repertoire, donc cela veut dire qu a la creation du repertoire le user n est pas renseigné....ce qui se fera plus tard.

J'ai donc crée une clé etrangere sur le champ de ce style :
Code:

1 2 CONSTRAINT `fkOwner` FOREIGN KEY (`OWNER`) REFERENCES `dbusers` (`ID`) avec ID cle primaine de ma table dbusers
et mon champ OWNER est definit de la sorte sachant que je definis toutes mes cles primaires de table en bigint unsigned not null :
Code:

1 2 `OWNER` bigint(20) unsigned default NULL COMMENT 'Index user'
j'ai mis NULL pour l insertion non renseignée du départ.

Pourtant cela ne semble pas fonctionner.
Il n'est pas possible de créer une cle etrangere sur un champ non renseigné à l'insertion ? ou dois je mettre NOT NULL default 0 ?
ou devons nous modifier les options de "mise à jour" a SET NULL par exemple au lieu de RESTRICT ?

Bref si qqun peut me filer un tuyau.
Un grand merci d avance.

A+
13/02/2008, 20h51
Antoun

Pour que ça fonctionne, il faut que tes deux tables soient en InnoDB...

Tu peux par exemple faire un SHOW CREATE TABLE sur chacune ; à la fin du CREATE, tu dois avoir la clause ENGINE = qui te donne le moteur de stockage. Si ce n'est pas InnoDB, tu peut le modifier ainsi :

Code:

ALTER table machin ENGINE = InnoDB ;
13/02/2008, 21h37
Targan

merci pour ta reponse.

Mais elles sont bien en innodb, d ailleurs jai besoin des transactions....

Des que j'insere un enregistrement j'ai droit a une erreur parent/child sur la contrainte d integrite.
13/02/2008, 23h19
Antoun

Pourrais montrer le CREATE TABLE complet de deux de tes tables liées par une intégrité référentielle, ainsi que la requête qui provoque une erreur ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE `Users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `dbLOGIN` varchar(50) NOT NULL COMMENT 'Login utilisateur',
  `dbNAME` varchar(100) NOT NULL COMMENT 'Nom de l utilisateur',
  `dbPASSWORD` varchar(50) default NULL COMMENT 'Password crypte',
  `dbRIGHTS` tinyint(4) unsigned NOT NULL default '0' COMMENT 'Droits utilisateur',
  `dbACTIVATED` tinyint(1) NOT NULL default '0' COMMENT 'Indique si l utilisateur est actif ou non',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `idxLogin` (`dbLOGIN`),
  KEY `idxGetActivatedUser` (`dbACTIVATED`,`dbLOGIN`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Liste des utilisateurs';

Et voici ma table dans laquelle j ai ma cle etrangere qui me bloque à l'ajout d'une donnée :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE TABLE `dbChildDirectory` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `dbIDDIR` bigint(20) unsigned NOT NULL COMMENT 'Index de repertoire parent',
  `dbDATECREATE` datetime NOT NULL COMMENT 'Date de creation',
  `dbUSERCREATE` bigint(20) unsigned NOT NULL COMMENT 'Utilisateur a l origine du repertoire',
  `dbPATH` varchar(255) NOT NULL COMMENT 'Path relatif',
  `dbXML` mediumblob NOT NULL COMMENT 'Fichier XML descriptif',
  `dbOWNER` bigint(20) unsigned default NULL COMMENT 'Utilisateur qui a verrouille le repertoire',
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `idxGetDir` (`dbIDDIR`,`dbPATH`),
  KEY `idxdbIDDIR` (`dbIDDIR`),
  KEY `idxdbUSERCREATE` (`dbUSERCREATE`),
  KEY `idxdbOWNER` (`dbOWNER`),
  CONSTRAINT `fkDIROwner` FOREIGN KEY (`dbOWNER`) REFERENCES `Users` (`ID`),
  CONSTRAINT `fkDIRPresta` FOREIGN KEY (`dbIDDIR`) REFERENCES `dbParentDirectory` (`ID`),
  CONSTRAINT `fkDIRUserCreate` FOREIGN KEY (`dbUSERCREATE`) REFERENCES `Users` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Liste des sous repertoires';

et c'est cette contrainte qui bloque : CONSTRAINT `fkDIROwner` FOREIGN KEY (`dbOWNER`) REFERENCES `Users` (`ID`),

14/02/2008, 10h04
qi130

Si tu ne renseignes pas cette colonne

Code:

`dbOWNER` bigint(20) UNSIGNED DEFAULT NULL

le SGBD tente d'y mettre NULL et recherche un FK ayant cette valeur...

Et je doute qu'il la trouve dans la tables users, puisque cette colonne est en auto incrément (donc toujours <>NULL).

Il faut donc utiliser le last_insert_id pour alimenter dbOWNER
14/02/2008, 10h59
Targan

Ok, je comprends mieux le pb....

Maintenant, comment faire pour ne pas devoir renseigner la valeur Owner de suite mais le faire plus tard ?
On ne doit tout de meme pas supprimer la cle etrangere ?

Doit bien exister un moyen de mettre une valeur qui n'est pas dans la table user car je vois qu il y a 2 "triggers" sur les cles etrangeres qui permettent de modifier les valeurs qd y a suppression et mise à jour avec une commande SET NULL à la place de RESTRICT ?

Peut etre devrais mettre SET NULL pour les mises à jour sur cette cle etrangere ?

Merci pour votre aide en tout cas.
14/02/2008, 22h02
qi130

Si tu souhaites laisser la contrainte en place, je ne vois que 2 solutions:
1/ soit tu récupères l'id de la FK
2/ soit tu "bricoles" ton modèle afin que la contrainte soit satisfaite: 1 ligne avec valeur de l'ID à NULL (mais pb à l'horizon rapport à l'auto-incrément)

Si tu veux te passer de la contrainte gérée par le SGBD, l'effort de cohésion/cohérence de ta base sera à ta charge...

En définitive, même si ce n'est pas élégant (et peut-être pas portable):

Citation:

InnoDB does not check foreign key constraints on those foreign key or referenced key values that contain a NULL column.
15/02/2008, 00h27
Targan

Bon alors la je ne comprends pas du tout.

Sur une autre table j ai fait la meme chose, cest a dire que j ai mis default null sur la colonne sur laquelle est definie la cle etrangere et je peux parfaitement inserer des données sans que ca coince.

Mais sur cette table, pourtant identique sur le fonctionnement, il y a un soucy....

Est ce que cela ne viendra pas d'EMS MySQL (version 2005) par hasard ? cest avec lui que j ai cree mes contraintes.