Clés étrangères nulles

**tofito** · 01/09/2009, 14h42

Bonjour,

J'ai une petite question... Vous mettriez une clé étrangère sur un champs qui peut être vide vous?

Exemple: j'ai une liste de pays du monde codés sur 3 caractères. J'enregistre un client en lui demandant sa nationalité et sur le champs nationalité j'ai mis une clé étrangère pour vérifier que ce soit uniquement un code de 3 caractère correspondant à un pays qu'on mette dans ce champs.
J'ai fait la même chose pour le champs "double nationalité". En effet je demande au client s'il a une double nationalité, ce qui est rare mais arrive...
Le champs "nationalité" est obligatoire tandis que le champs "double nationalité" est optionnel dans le formulaire. Donc dans la base de données le champs "nationalité" est not NULL tandis que l'autre peut-être NULL.
J'obtiens une erreur lorsque j'essaye d'enregistrer un client qui n'a qu'une nationalité car "" ne fait pas partie des valeurs des index de ma table de pays.

Du coup j'ai supprimé ma clé étrangère pour le champs "double nationalité". Est ce que j'ai raison parce que c'est une erreur de ma part d'avoir voulu mettre une clé étrangère sur un champs pouvant être nul ou est ce qu'il y aurait un moyen pour faire cohabiter clé étrangère et possibilité d'une valeur nulle?

Merci pour votre aide!

**Maljuna Kris** · 01/09/2009, 14h57

Saluton,
En toute rigueur, l'information sur la double nationalité est une information de relation optionnelle entre la table client et la table pays (0,1-0,n).
Si tu veux rester en formes normales tu devrais avoir une table relationnelle double_nationalité, qui intègrerait les deux clefs étrangères, celle du client et celle du pays. Et le problème ne se poserait plus, l'optionnalité étant gérable par une jointure LEFT JOIN entre clients et pays, qui retournera NULL quand le client n'aura pas de double nationalité.

**tofito** · 01/09/2009, 20h32

Concrètement, si j'ai bien compris, je remplace mon ancienne structure de base de données:

Code SQL :

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
 
CREATE TABLE `client` (
  `id_client` int(10) unsigned NOT NULL auto_increment,
  `prenom` varchar(50) collate utf8_unicode_ci NOT NULL,
  `nom` varchar(50) collate utf8_unicode_ci NOT NULL,
  `date_naissance` date NOT NULL,
  `sexe` enum('male','female') collate utf8_unicode_ci NOT NULL default 'female',
  `nationalite_id` varchar(3) collate utf8_unicode_ci NOT NULL,
  `autre_nationalite_id` varchar(3) collate utf8_unicode_ci default NULL,
  `adresse` text collate utf8_unicode_ci NOT NULL,
  `code_postal` varchar(10) collate utf8_unicode_ci NOT NULL,
  `ville` varchar(50) collate utf8_unicode_ci NOT NULL,
  `pays_id` varchar(3) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id_client`),
  KEY `client_fk_1` (`nationalite_id`),
  KEY `client_fk_2` (`pays_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
 
ALTER TABLE `client`
  ADD CONSTRAINT `client_fk_1` FOREIGN KEY (`nationalite_id`) REFERENCES `nationalite` (`id_nationalite`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `client_fk_2` FOREIGN KEY (`pays_id`) REFERENCES `pays` (`id_pays`) ON DELETE CASCADE ON UPDATE CASCADE;
 
INSERT INTO `client` VALUES (1, 'Jacques', 'Chirac', '1940-01-01', 'male', 'FRA', '', 'Elysee', '75001', 'Paris', 'FRA');
 
 
 
CREATE TABLE `nationalite` (
  `id_nationalite` varchar(3) collate utf8_unicode_ci NOT NULL,
  `nom_fr` varchar(50) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id_nationalite`),
  UNIQUE KEY `nom_fr` (`nom_fr`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO `nationalite` VALUES ('AFG', 'afghane');
INSERT INTO `nationalite` VALUES ('ALB', 'albane');
INSERT INTO `nationalite` VALUES ('AND', 'andorrane');
INSERT INTO `nationalite` VALUES ('ARG', 'argentine');
INSERT INTO `nationalite` VALUES ('FRA', 'française');

... par cette nouvelle structure?

Code SQL :

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
 
CREATE TABLE `client` (
  `id_client` int(10) unsigned NOT NULL auto_increment,
  `prenom` varchar(50) collate utf8_unicode_ci NOT NULL,
  `nom` varchar(50) collate utf8_unicode_ci NOT NULL,
  `date_naissance` date NOT NULL,
  `sexe` enum('male','female') collate utf8_unicode_ci NOT NULL default 'female',
  `nationalite_id` varchar(3) collate utf8_unicode_ci NOT NULL,
  `adresse` text collate utf8_unicode_ci NOT NULL,
  `code_postal` varchar(10) collate utf8_unicode_ci NOT NULL,
  `ville` varchar(50) collate utf8_unicode_ci NOT NULL,
  `pays_id` varchar(3) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id_client`),
  KEY `client_fk_1` (`nationalite_id`),
  KEY `client_fk_2` (`pays_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
 
ALTER TABLE `familles`
  ADD CONSTRAINT `client_fk_1` FOREIGN KEY (`nationalite_id`) REFERENCES `nationalite` (`id_nationalite`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `client_fk_2` FOREIGN KEY (`pays_id`) REFERENCES `pays` (`id_pays`) ON DELETE CASCADE ON UPDATE CASCADE;
 
INSERT INTO `client` VALUES (1, 'Jacques', 'Chirac', '1940-01-01', 'male', 'FRA', 'Elysee', '75001', 'Paris', 'FRA');
 
 
 
CREATE TABLE `nationalite` (
  `id_nationalite` varchar(3) collate utf8_unicode_ci NOT NULL,
  `nom_fr` varchar(50) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id_nationalite`),
  UNIQUE KEY `nom_fr` (`nom_fr`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO `nationalite` VALUES ('AFG', 'afghane');
INSERT INTO `nationalite` VALUES ('ALB', 'albane');
INSERT INTO `nationalite` VALUES ('AND', 'andorrane');
INSERT INTO `nationalite` VALUES ('ARG', 'argentine');
INSERT INTO `nationalite` VALUES ('FRA', 'française');
 
 
 
CREATE TABLE `autre_nationalite` (
  `client_id` int(10) unsigned NOT NULL,
  `nationalite_id` varchar(3) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`client_id`),
  KEY `autre_nationalite_fk_1` (`nationalite_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO `autre_nationalite` VALUES (1, 'ALB');
 
ALTER TABLE `autre_nationalite`
  ADD CONSTRAINT `autre_nationalite_fk_2` FOREIGN KEY (`client_id`) REFERENCES `client` (`id_client`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `autre_nationalite_fk_1` FOREIGN KEY (`nationalite_id`) REFERENCES `nationalite` (`id_nationalite`) ON DELETE CASCADE ON UPDATE CASCADE;

Du coup, lors de l'insertion des données par le biais du formulaire, je faisais

Code php :

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

1
2
3
4
 
$req = 'INSERT INTO client (id_client, prenom, nom, date_naissance, sexe, nationalite_id, autre_nationalite_id, adresse, code_postal, ville, pays_id)
	VALUES ("", "'.$prenom.'", "'.$nom.'", "'.$date_naissance.'", "'.$sexe.'", "'.$nationalite.'", "'.$autre_nationalite.'", "'.$adresse.'", "'.$code_postal.'", "'.$ville.'", "'.$pays.'")';
mysql_query($req) or die ('Error: '.mysql_error());

Maintenant ça devient il me semble:

Code php :

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

1
2
3
4
5
6
7
 
$req1 = 'INSERT INTO client (id_client, prenom, nom, date_naissance, sexe, nationalite_id, adresse, code_postal, ville, pays_id)
	VALUES ("", "'.$prenom.'", "'.$nom.'", "'.$date_naissance.'", "'.$sexe.'", "'.$nationalite.'", "'.$adresse.'", "'.$code_postal.'", "'.$ville.'", "'.$pays.'")';
mysql_query($req1) or die ('Error: '.mysql_error());
$req2 = 'INSERT INTO autre_nationalite (client_id, nationalite_id)
	VALUES (LAST_INSERT_ID(), "'.$autre_nationalite.'")';
mysql_query($req2) or die ('Error: '.mysql_error());

Du coup vous gèreriez ça comment vous si je vous dis que je veux ajouter un champs "vit_en_couple" qui sera un booléen et indiquera si le client est en couple, et un champs "nationalite_id_conjoint" ainsi que "autre_nationalite_id_conjoint" qui indiquent la nationalité du conjoint et l'éventuelle double nationalité du conjoint. Ces 2 champs sont encore optionnels car déjà une double nationalité est optionnelle, et ensuite même la nationalité principale est optionnelle dans le cas où le client est célibataire et répond donc "non" à la question "vivez vous en couple?".

Vous gèreriez 3 tables (autre_nationalite_1 pour le champs optionnel correspondant à la double nationalité du client, autre_nationalite_2 pour le champs optionnel correspondant à la nationalité du conjoint, autre_nationalite_3 pour le champs optionnel correspondant à la double nationalité du conjoint) ou vous feriez plus optimisé?

**Maljuna Kris** · 02/09/2009, 13h18

Envoyé par tofito

vous gèreriez ça comment vous si je vous dis que je veux ajouter un champs "vit_en_couple" qui sera un booléen et indiquera si le client est en couple, et un champs "nationalite_id_conjoint" ainsi que "autre_nationalite_id_conjoint" qui indiquent la nationalité du conjoint et l'éventuelle double nationalité du conjoint. Ces 2 champs sont encore optionnels car déjà une double nationalité est optionnelle, et ensuite même la nationalité principale est optionnelle dans le cas où le client est célibataire et répond donc "non" à la question "vivez vous en couple?".

Vous gèreriez 3 tables (autre_nationalite_1 pour le champs optionnel correspondant à la double nationalité du client, autre_nationalite_2 pour le champs optionnel correspondant à la nationalité du conjoint, autre_nationalite_3 pour le champs optionnel correspondant à la double nationalité du conjoint) ou vous feriez plus optimisé?

La présence d'un conjoint pourrait aussi être gérée par une jointure externe LEFT JOIN vers une table conjoint qui aurait pour seul attribut le code nationalité dudit conjoint (outre l'identifiant de la personne gérée en principal et un identifiant dans la table conjoints).
Quant à son éventuelle double nationalité, le problème se pose dans les même termes que pour le cas précédent.

**tofito** · 03/09/2009, 00h32

Merci cher voisin breton pour tes conseils avisés!
Kenavo

**Maljuna Kris** · 03/09/2009, 06h42

Nedankinde,
Tu peux n'avoir qu'une seule table des doubles nationalités en lui ajoutant un champ de typage ENUM(natif,conjoint), ce qui permettrait d'éviter les doublons entre identifiant en mettant la PK sur les deux colonnes

Clés étrangères nulles

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager