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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
|
CREATE TABLE MDP (
id_mdp INT NOT NULL AUTO_INCREMENT,
lib_mdp TEXT NULL,
desc_mdp TEXT NULL,
log_mdp TEXT NOT NULL,
mdp_mdp TEXT NULL,
PRIMARY KEY(id_mdp)
)
TYPE=InnoDB;
CREATE TABLE Permissions (
id_per INT NOT NULL AUTO_INCREMENT,
lib_per TEXT NULL,
PRIMARY KEY(id_per)
)
TYPE=InnoDB;
CREATE TABLE utilisateur (
id_ut INT NOT NULL AUTO_INCREMENT,
email_ut TEXT NOT NULL,
mdp_ut TEXT NULL,
PRIMARY KEY(id_ut)
)
TYPE=InnoDB;
CREATE TABLE Groupe (
id_gp INT NOT NULL AUTO_INCREMENT,
lib_gp TEXT NULL,
PRIMARY KEY(id_gp)
)
TYPE=InnoDB;
CREATE TABLE Groupe_d_utilisateur (
Groupe_id_gp INT NOT NULL,
utilisateur_id_ut INT NOT NULL,
PRIMARY KEY(Groupe_id_gp, utilisateur_id_ut),
INDEX gp_ut_FK1(Groupe_id_gp),
INDEX gp_ut_FK2(utilisateur_id_ut),
FOREIGN KEY(Groupe_id_gp)
REFERENCES Groupe(id_gp)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY(utilisateur_id_ut)
REFERENCES utilisateur(id_ut)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB;
CREATE TABLE Groupe_MDP (
Groupe_id_gp INT NOT NULL,
MDP_id_mdp INT NOT NULL,
Permissions_id_per INT NOT NULL,
PRIMARY KEY(Groupe_id_gp, MDP_id_mdp, Permissions_id_per),
INDEX Gp_MDP_FK1(Groupe_id_gp),
INDEX Gp_MDP_FK2(MDP_id_mdp),
INDEX Gp_MDP_FK3(Permissions_id_per),
FOREIGN KEY(Groupe_id_gp)
REFERENCES Groupe(id_gp)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(MDP_id_mdp)
REFERENCES MDP(id_mdp)
ON DELETE SET NULL
ON UPDATE CASCADE,
FOREIGN KEY(Permissions_id_per)
REFERENCES Permissions(id_per)
ON DELETE SET NULL
ON UPDATE CASCADE
)
TYPE=InnoDB; |
Partager