iYCekQ3c

1enter code here
2
3CREATE TABLE IF NOT EXISTS `participer` (
4  `statut_Invitation` tinyint(1) NOT NULL,
5  `NumVersion` int(11) NOT NULL,
6  `InviteCode` varchar(255) NOT NULL,
7  `IdQuest` int(11) NOT NULL,
8  PRIMARY KEY (`NumVersion`,`InviteCode`,`IdQuest`),
9  KEY `FK_Participer_NumVersion` (`NumVersion`),
10  KEY `FK_Participer_IdQuest` (`IdQuest`),
11  KEY `FK_Participer_InviteCode` (`InviteCode`)
12) ENGINE=InnoDB DEFAULT CHARSET=utf8;
13
14
15
16INSERT INTO `participer` (`statut_Invitation`, `NumVersion`, `InviteCode`, `IdQuest`) VALUES
17(0, 2, '2548', 1),
18(0, 2, '8742', 1);
19
20
21
22CREATE TABLE IF NOT EXISTS `questionnaire` (
23  `IdQuest` int(11) NOT NULL AUTO_INCREMENT,
24  `Nom` varchar(256) DEFAULT NULL,
25  `DateCreation` date DEFAULT NULL
26  PRIMARY KEY (`IdQuest`)
27) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
28
29
30INSERT INTO `questionnaire` (`IdQuest`, `Nom`, `DateCreation`, `ID`) VALUES
31(1, 'Parions Sport', '2015-03-23'),
32(2, 'GPS', '2015-03-23');
33
34CREATE TABLE IF NOT EXISTS `utilisateurs` (
35  `InviteCode` varchar(255) NOT NULL,
36  `Email` varchar(25) NOT NULL,
37  `DateNaissance` date DEFAULT NULL,
38  `Ville` varchar(25) DEFAULT NULL,
39  PRIMARY KEY (`InviteCode`)
40) ENGINE=InnoDB DEFAULT CHARSET=utf8;
41
42
43
44INSERT INTO `utilisateurs` (`InviteCode`, `Email`, `DateNaissance`, `Ville`) VALUES
45('1235314', 'bocchi@gmail.com', NULL, NULL),
46('2548', 'bocchiTEST@gmail.com', NULL, NULL),
47('337752493652424404824466004444846460026', 'dylan@gmail.com', NULL, NULL),
48('53131172170670664482420846024208824402', 'dylan2@gmail.com', NULL, NULL),
49('5446544', 'lool', NULL, NULL),
50('8742', 'max@gmail.com', NULL, NULL);
51
52-- --------------------------------------------------------
53
54
55
56CREATE TABLE IF NOT EXISTS `versionquestionnaire` (
57  `NumVersion` int(11) NOT NULL,
58  `DateExpiration` date DEFAULT NULL,
59  `SommeNote` float DEFAULT NULL,
60  `NbReponses` int(11) DEFAULT NULL,
61  `IdQuest` int(11) NOT NULL,
62  PRIMARY KEY (`NumVersion`,`IdQuest`),
63  KEY `FK_VersionQuestionnaire_IdQuest` (`IdQuest`)
64) ENGINE=InnoDB DEFAULT CHARSET=utf8;
65
66INSERT INTO `versionquestionnaire` (`NumVersion`, `DateExpiration`, `SommeNote`, `NbReponses`, `IdQuest`) VALUES
67(1, '2015-03-26', 120, 2, 1),
68(1, '2015-03-30', 100, 1, 2),
69(2, '2015-03-26', 210, 3, 1),
70(2, '2015-03-30', 300, 4, 2);
71
72
73ALTER TABLE `participer`
74  ADD CONSTRAINT `FK_Participer_IdQuest` FOREIGN KEY (`IdQuest`) REFERENCES `versionquestionnaire` (`IdQuest`),
75  ADD CONSTRAINT `FK_Participer_InviteCode` FOREIGN KEY (`InviteCode`) REFERENCES `utilisateurs` (`InviteCode`),
76  ADD CONSTRAINT `FK_Participer_NumVersion` FOREIGN KEY (`NumVersion`) REFERENCES `versionquestionnaire` (`NumVersion`);
77
78
79ALTER TABLE `versionquestionnaire`
80  ADD CONSTRAINT `FK_VersionQuestionnaire_IdQuest` FOREIGN KEY (`IdQuest`) REFERENCES `questionnaire` (`IdQuest`);
81
82When i want to delete the version 1 of the questionnaire 1 :
83DELETE FROM `versionquestionnaire` WHERE `versionquestionnaire`.`NumVersion` = 1 AND `versionquestionnaire`.`IdQuest` = 1;
84
85I have a error :
861451 - Cannot delete or update a parent row: a foreign key constraint fails (`testbug`.`participer`, CONSTRAINT `FK_Participer_IdQuest` FOREIGN KEY (`IdQuest`) REFERENCES `versionquestionnaire` (`IdQuest`)) ;