Bonsoir Sebajuste,
Votre table Manipuler contient des clés étrangères faisant référence à des clés absentes des tables référencées, donc le SGBD ne peut que les rejeter. Par exemple, quand vous codez : FOREIGN KEY (utilisateur_id, entreprise_id) REFERENCES Utilisateur (id, entreprise_id), cela suppose que la table Utilisateur est dotée d’une clé {id, entreprise_id}, ce qui n’est pas le cas.
Pour arriver à respecter la contrainte selon laquelle un utilisateur n’a le droit de se servir que des appareils de son entreprise, on va injecter des surclés : Model_SK, Appareil_SK, Utilisateur_SK (cf. ci-dessous), une surclé étant un surensemble d’une clé (primaire dans votre cas). Comme une clé étrangère peut faire référence à une surclé, on ne va pas se priver d’utiliser cette possibilité, et au bout du compte on peut ainsi propager légalement (cette fois-ci !) la colonne entreprise_id jusque dans l’en-tête de la table Manipuler :
CREATE TABLE Entreprise (
id INTEGER PRIMARY KEY
);
CREATE TABLE Model (
id INTEGER PRIMARY KEY,
entreprise_id INTEGER,
CONSTRAINT Model_SK UNIQUE (entreprise_id, id),
FOREIGN KEY(entreprise_id) REFERENCES Entreprise (id)
);
CREATE TABLE Appareil (
id INTEGER PRIMARY KEY,
model_id INTEGER,
entreprise_id INTEGER,
CONSTRAINT Appareil_SK UNIQUE (entreprise_id, id),
FOREIGN KEY(model_id ) REFERENCES Model (id)
);
CREATE TABLE Utilisateur (
id INTEGER PRIMARY KEY,
entreprise_id INTEGER,
CONSTRAINT Utilisateur_SK UNIQUE (entreprise_id, id),
FOREIGN KEY(entreprise_id) REFERENCES Entreprise (id)
);
CREATE TABLE Manipuler (
utilisateur_id INTEGER,
appareil_id INTEGER,
entreprise_id INTEGER,
PRIMARY KEY(utilisateur_id, appareil_id),
FOREIGN KEY(entreprise_id, utilisateur_id) REFERENCES Utilisateur(entreprise_id, id),
FOREIGN KEY(entreprise_id, appareil_id) REFERENCES Appareil (entreprise_id, id)
);
Un début de jeu d’essai :
insert into Entreprise (id) VALUES (1) ;
insert into Entreprise (id) VALUES (2) ;
insert into Model (entreprise_id, id) VALUES (1, 1) ;
insert into Model (entreprise_id, id) VALUES (1, 2) ;
insert into Model (entreprise_id, id) VALUES (2, 8) ;
insert into Model (entreprise_id, id) VALUES (2, 9) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (1, 1, 11) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (1, 1, 12) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (1, 2, 21) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (1, 2, 22) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 8, 81) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 8, 82) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 9, 91) ;
insert into Appareil (entreprise_id, model_id, id) VALUES (2, 9, 92) ;
insert into Utilisateur (entreprise_id, id) VALUES (1, 1) ;
insert into Utilisateur (entreprise_id, id) VALUES (1, 2) ;
insert into Utilisateur (entreprise_id, id) VALUES (2, 16) ;
insert into Utilisateur (entreprise_id, id) VALUES (2, 17) ;
insert into Manipuler (entreprise_id, utilisateur_id, appareil_id) VALUES (1, 1, 22) ;
insert into Manipuler (entreprise_id, utilisateur_id, appareil_id) VALUES (2, 16, 92) ;
insert into Manipuler (entreprise_id, utilisateur_id, appareil_id) VALUES (2, 1, 92) ; -- délinquant, rejeté
Tout se passera bien sauf pour le dernier insert, car l’utilisateur 1 ne fait pas partie de l’entreprise 2. ∎
Tout ceci ressortit au problème bien connu de la contrainte de chemin.
Partager