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
| CREATE TABLE `t_etablissement` (
`id_etablissement` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`nom` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id_etablissement`),
INDEX (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_etablissement` (`id_etablissement`, `nom`) VALUES
(1, "Etablissement 1"),
(2, "Etablissement 2"),
(3, "Etablissement 3");
CREATE TABLE `t_personne` (
`id_personne` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`civilite` ENUM("","Mr","Mme","Mlle") NOT NULL,
`nom` VARCHAR(255) NOT NULL,
`prenom` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id_personne`),
KEY (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_personne` (`id_personne`, `civilite`, `nom`, `prenom`)
VALUES
(1, "Mr", "AAAAA", "aaaaaaaa"),
(2, "Mr", "BBBB", "bbbbbbb"),
(3, "Mme", "CCCCCCC", "ccccccccccc"),
(4, "Mme", "DDDDDDD", "ddd"),
(5, "Mr", "EEEEEEE", "");
CREATE TABLE `t_appartenance` (
`id_personne` SMALLINT(5) UNSIGNED NOT NULL,
`id_etablissement` SMALLINT(5) UNSIGNED NOT NULL,
PRIMARY KEY (`id_personne`, `id_etablissement`),
FOREIGN KEY (`id_personne`) REFERENCES `t_personne` (`id_personne`) ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY (`id_etablissement`) REFERENCES `t_etablissement` (`id_etablissement`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_appartenance` (`id_personne`, `id_etablissement`)
VALUES
(3, 1),
(3, 2),
(4, 1),
(5, 3); |
Partager