Discussion :

[Grimly]

Bonjour,

J'aurais une question sur les clés étrangères/contraintes en MySQL (et si possible compatible avec SQLite pour mon environnement de test). J'ai une table de références que je dois utiliser avec un identifiant numérique et deux colonnes permettant de catégoriser mes références. Un exemple ce trouve dans le CSV ci-après.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
ID;Fonctionnalité;Champ;Valeur;Valeur par défaut;Commentaires; 
83;Transmission;Type d'entité;;;; 
84;Transmission;Délai;Clôture sur transfert;;Equivalent jours : 0 j; 
85;Transmission;Délai;1 h;;Equivalent jours : 0 j; 
86;Transmission;Délai;48 h;;Equivalent jours : 2 j; 
87;Transmission;Délai;72 h;;Equivalent jours : 3 j; 
88;Transmission;Délai;5 j;;Equivalent jours : 5 j; 
89;Transmission;Délai;30 j;;Equivalent jours : 30 j; 
90;Alertes & Actions;Type;Alerte;X;; 
91;Alertes & Actions;Type;Action;;; 
92;Alertes & Actions;Mode de transmission;Transfert Téléphone;;; 
93;Alertes & Actions;Mode de transmission;Notification;;; 
94;Alertes & Actions;Mode de transmission;Email;;;

Ma question est la suivante : Comment déclarer une contrainte pour que le SGBD vérifie la catégorie de ma référence ?

Un code faux que j'ai essayé est le suivant :

Code SQL :

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

1
2
3
CONSTRAINT `AlerteType`
            FOREIGN KEY (`type`, 'Alerte & Action', 'Type')
            REFERENCES `references` (`id`, `fonctionnalite`, `champ`)

Le champ `alerte`.`type` doit alors ne permettre que les valeurs 90 et 91 (ou NULL si le champ le permet)

En attendant, toutes les vérifications sont purement applicatives mais une contrainte en base reste souhaitable.

[punkoff]

bonjour,


Si on prend le MCD suivant :
Personne-0,n------Possede-------1,1-Voiture

Une personne possède 0 ou n voiture
Une voiture est possédé par 1 et 1 seule personne.

On en déduit le MPD suivant :
Personne(ID_PERSONNE, ...)
Voiture(ID_VOITURE #ID_PERSONNE, .....)


Pour la contrainte d'intégrité entre les deux tables on aura :

Code :

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

1
2
3
4
5
6
 
Create table personne (ID_PERSONNE INTEGER,  PRIMARY KEY(PERSONNE));
 
CREATE TABLE VOITURE (ID_VOITURE,
ID_VOITURE INTEGER REFERENCES PERSONNE (ID_PERSONNE),
INTEGER PRIMARY KEY (VOITURE));

[Grimly]

bonjour,


Si on prend le MCD suivant :
Personne-0,n------Possede-------1,1-Voiture

Une personne possède 0 ou n voiture
Une voiture est possédé par 1 et 1 seule personne.

On en déduit le MPD suivant :
Personne(ID_PERSONNE, ...)
Voiture(ID_VOITURE #ID_PERSONNE, .....)


Pour la contrainte d'intégrité entre les deux tables on aura :

Code :

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

1
2
3
4
5
6
 
Create table personne (ID_PERSONNE INTEGER,  PRIMARY KEY(PERSONNE));
 
CREATE TABLE VOITURE (ID_VOITURE,
ID_VOITURE INTEGER REFERENCES PERSONNE (ID_PERSONNE),
INTEGER PRIMARY KEY (VOITURE));

Je ne vois pas en quoi cet exemple peux m'aider à trouver la solution.

Ce que j'ai réussi à faire :
Le type d'une alerte est une des références
Dans ton exemple : une personne est dans une voiture

Ce que je cherche à faire :
Le type d'une alerte est une des références de catégorie "Alerte & Action" & "Type"
Dans ton exemple : une personne est dans une voitures de couleur rouge uniquement.



EDIT :

Autre tentative échouée :
J'ai voulu utiliser une vue pour isoler uniquement les références d'une catégorie. Cependant on ne peux pas faire de clé étrangère sur une vue :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW alerteTypes AS SELECT * FROM `references` WHERE fonctionnalite = 'Alertes Actions' AND champ = 'Type';
 
CREATE TABLE `alerte` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `AlerteType` (`type`),
  CONSTRAINT `AlerteTypeFK` FOREIGN KEY (`type`) REFERENCES `alerteTypes` (`id`)
) ENGINE=InnoDB;
-- Error Code: 1005. Can't create table 'test.alerte' (errno: 150)
-- L'erreur est bien la clé étrangère

[punkoff]

Le type d'une alerte est une des références de catégorie "Alerte & Action" & "Type"

Oki, dans ce cas seul un trigger pourra régler ceci. (voir une référence de type ENUM mais je ne maitrise pas cet aspect là)