créer une contrainte d'exclusion en SQL

Version imprimable

bonjour ,
j ai quatre table qui sont: direction , service , division et poste et un poste est soit lié au direction ou service ou division pas les deux et ou trois en même et pour cela je voudrais creer une contrainte d'exclusion dans la table poste .
voici mes table :

Code:

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
 
//table  direction
 
CREATE TABLE direction(
        id_direction   int (11) Auto_increment  NOT NULL ,
        code_direction Varchar (25) NOT NULL ,
        nom_direction  Varchar (50) NOT NULL ,
        PRIMARY KEY (id_direction )
)ENGINE=InnoDB;
 
// table  service  
CREATE TABLE service(
        id_service   int (11) Auto_increment  NOT NULL ,
        code_service Varchar (25) ,
        nom_service  Varchar (50) NOT NULL ,
        id_direction Int ,
        PRIMARY KEY (id_service )
)ENGINE=InnoDB;
 
// division
 
CREATE TABLE division(
        id_division  int (11) Auto_increment  NOT NULL ,
        nom_division Varchar (25) NOT NULL ,
        id_service   Int ,
        PRIMARY KEY (id_division )
)ENGINE=InnoDB;
 
 
//poste  
 
CREATE TABLE poste(
        id_poste     int (11) Auto_increment  NOT NULL ,
        code_poste   Varchar (25) ,
        nom_poste    Varchar (25) NOT NULL ,
        id_division  Int ,
        id_direction Int ,
        id_service   Int ,
        PRIMARY KEY (id_poste )
)ENGINE=InnoDB;
 
//definition  les contraintes  des cle etranger 
 
ALTER TABLE poste ADD CONSTRAINT FK_poste_id_division FOREIGN KEY (id_division) REFERENCES division(id_division);
ALTER TABLE poste ADD CONSTRAINT FK_poste_id_direction FOREIGN KEY (id_direction) REFERENCES direction(id_direction);
ALTER TABLE poste ADD CONSTRAINT FK_poste_id_service FOREIGN KEY (id_service) REFERENCES service(id_service);

maintenant ce que je veux c'est creer une contrainte d'exlcusion qui va les deux autre si une est selectionner c'est dire mettre les deux en null
merci d'avance

Citation:
j ai quatre table qui sont: direction , service , division et poste et un poste est soit lié au direction ou service ou division pas les deux et ou trois
Code:

1 2 3 4 5 6 7 8 9 CREATE TABLE poste( id_poste int (11) Auto_increment NOT NULL , code_poste Varchar (25) , nom_poste Varchar (25) NOT NULL , id_division Int , id_direction Int , id_service Int , PRIMARY KEY (id_poste ) )ENGINE=InnoDB;
Vous allez donc peupler chaque ligne de la table de 2 bonshommes NULL, ce qui est très mauvais !

En réalité, les règles de gestion des données sont les suivantes :
Un poste peut dépendre d'une division et une division peut avoir plusieurs postes.
Un poste peut dépendre d'une direction et une direction peut avoir plusieurs postes.
Un poste peut dépendre d'un service et une service peut avoir plusieurs postes.

2 solutions :
1) Vous avez trois associations :
poste -0,1----dépendre----0,n- division
|---------0,1----dépendre----0,n- direction
|---------0,1----dépendre----0,n- service

Comme je l'explique dans mon blog, ces associations entraînent la création d'une table associative chacune.

Il faut en plus, selon votre besoin, créer un mécanisme pour implémenter la contrainte d'exclusion entre les trois associations.

2) Vous faites un héritage de données

Règles de gestion :
Une division est une entité et une entité peut être une division.
Une direction est une entité et une entité peut être une direction.
Un service est une entité et une entité peut être un service.

MCD :
division -(1,1)----être----0,1- entité
direction -(1,1)----être----0,1----|
service -(1,1)----être----0,1------|

Tables :
te_entite_ent (ent_id, [colonnes communes à toutes les entités]....)
th_division_div (div_id_entite, [colonnes spécifiques aux divisions]...)
th_direction_dir (dir_id_entite, [colonnes spécifiques aux directions]...)
th_service_srv (srv_id_entite, [colonnes spécifiques aux services...)

Il est alors facile de n'associer chaque poste qu'à une seule entité qui sera soit une division, soit une direction, soit un service, sans implémenter de contrainte d'exclusivité.

MCD :
division -(1,1)----être----0,1- entité -0,n----contenir----1,1- poste
direction -(1,1)----être----0,1----|
service -(1,1)----être----0,1------|

Table supplémentaire :
te_poste_pst (pst_id, pst_id_entite, ...)

Salut diengkals.

Quel est l'intérêt de séparer physiquement les trois tables direction, service et division ?
A vrai dire, ces trois tables ont exactement la même structure et sont porteuses du même type d'information.
Donc faire une seule table en regroupant les trois anciennes tables serait une meilleure idée, non ?

Pour faire la distinction entre ces trois types de tables, j'utilise une nouvelle colonne de nom 'type', qui prend les valeurs suivantes : 'M'anagment, 'S'ervice et 'D'ivision.
Afin de simplifier l'auto incrément de la colonne 'entite', j'ai créé un trigger qui gère cela. Vous n'avez plus à rentrer une quelconque valeur dans vos insertions.
La nouvelle structure de la table devient :
Code:

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 SET AUTOCOMMIT = 0; START TRANSACTION; -- ============ -- Base `Essai` -- ============ DROP DATABASE IF EXISTS `essai`; CREATE DATABASE `essai` DEFAULT CHARACTER SET `utf8` DEFAULT COLLATE `utf8_general_ci`; USE `essai`; COMMIT; -- ============== -- Table `entite` -- ============== DROP TABLE IF EXISTS `entite`; CREATE TABLE `entite` ( `type` char(1) NOT NULL, `entite` int NOT NULL, `code` varchar(255) NOT NULL, `nom` varchar(255) NOT NULL, primary key (`type`, `entite`) ) ENGINE=InnoDB DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci` ROW_FORMAT=COMPRESSED; -- ================= -- Trigger `ajouter` -- ================= DROP TRIGGER IF EXISTS `ajouter`; DELIMITER $$ CREATE TRIGGER `ajouter` BEFORE INSERT ON `entite` FOR EACH ROW BEGIN SET NEW.entite=ifnull((select max(entite)+1 from `entite` where type=NEW.type),1); END$$ DELIMITER ; -- ======================== -- Insertion dans `Entite` -- ======================== insert into `entite` (`Type`,`Code`, `Nom`) values ('M','x','g'), ('M','y','h'), ('S','z','i'), ('S','t','j'), ('D','a','k'), ('D','b','l');
Dans l'insertion, je n'ai pas mis la colonne 'entite' car c'est le trigger que la gère.
A vous de faire un vidage de cette table pour voir comment l'incrémentation s'est faite.

Je n'ai pas bien compris votre phrase, mais s'agit-il d'avoir parmi ces trois pères un et un seul pour la table poste ? Je fais prendre cette hypothèse.
En regroupant ces trois tables en une seule, il n'y a plus d'ambiguïté pour la contrainte.
Vous devez mettre qu'une seule et une seule référence de la table père (entité) pour la table fils (poste).
La table poste ne change pratiquement pas et devient :
Code:

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 -- ============= -- Table `poste` -- ============= CREATE TABLE `poste` ( `id` int NOT NULL Auto_increment primary key, `code` varchar (25) NOT NULL, `nom` varchar (25) NOT NULL, `type` char (1) NOT NULL, `entite` int, CONSTRAINT FOREIGN KEY (`type`,`entite`) REFERENCES `entite`(`type`,`entite`) ) ENGINE=InnoDB DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci` ROW_FORMAT=COMPRESSED; -- ====================== -- Insertion dans `Poste` -- ====================== insert into `poste` (`code`, `nom`,`type`,`entite`) values ('alpha','un', 'M',1), ('beta', 'deux', 'M',2), ('gamma','trois', 'S',1), ('delta','quatre','S',2), ('iota', 'cinq', 'D',1), ('kappa','six', 'D',2);
Si par contre, vous avez besoin de gérer vos trois ancienne tables séparément, vous pouvez utiliser une view pour chacune d'elle, comme si après :
Code:

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 -- ============== -- Vue `division` -- ============== drop view IF EXISTS `division`; create view `division` (div_id, div_code, div_nom) as select entite,code,nom from `entite` where type='D'; -- ============== -- Vue `service` -- ============== drop view IF EXISTS `service`; create view `service` (ser_id, ser_code, ser_nom) as select entite,code,nom from `entite` where type='S'; -- =============== -- Vue `direction` -- =============== drop view IF EXISTS `direction`; create view `direction` (man_id, man_code, man_nom) as select entite,code,nom from `entite` where type='M'; COMMIT; SET AUTOCOMMIT = 1;
Donc vous aurez alors vos trois tables séparés logiquement mais pas physiquement.
En espérant avoir répondu à votre attente.

@+

14/04/2015, 07h25
pier.antoine

Bonjour

Quelle est la différence entre la proposition d'Artemus 24 et la proposition d'héritage de Cinephil?

Pierre
14/04/2015, 09h12
CinePhil

Citation:

Quelle est la différence entre la proposition d'Artemus 24 et la proposition d'héritage de Cinephil?

Ma proposition fait trois tables différentes pour Division, Direction et Service + une table mère pour toutes les entités alors que Artemus propose de ne faire qu'une table pour les entités et d'ajouter une colonne pour distinguer les types d'entités.

L'héritage ne se justifie que s'il y a des propriétés ou associations spécifiques aux divisions, aux services, aux directions.

Dans le schéma de diengkals, on voit qu'il y a des associations entre les trois types d'entités. Une division appartient à un service qui appartient à une direction.
Artemus n'a pas repris ces associations dans son modèle mais ça peut se faire

entite -0,n----contenir
|---------0,n----------|

tj_ent_contenir_ent_ece (ece_id_entite_contenante, ece_id_entite_contenue...)

Et cette fois, il faudrait implémenter les contraintes suivantes :
1) Une direction ne peut contenir que des services.
2) Un service ne peut contenir que des divisions.
3) Une division ne peut rien contenir.

Salut.

Si tu as besoin de créer des relations père-fils, il suffit alors de modifier ma table en ajoutant une nouvelle colonne 'lien'.

Code:

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
-- ==============
-- Table `entite`
-- ==============
 
DROP TABLE IF EXISTS `entite`;
 
CREATE TABLE `entite` (
  `entite`        int          NOT NULL auto_increment primary key,
  `type`          char(1)      NOT NULL,
  `code`          varchar(255) NOT NULL,
  `nom`           varchar(255) NOT NULL,
  `lien`          int              NULL,
  CONSTRAINT FOREIGN KEY (`lien`) REFERENCES `entite`(`entite`)
  ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
  ROW_FORMAT=COMPRESSED;
 
 
-- ========================
-- Insertion dans  `Entite`
-- ========================
 
insert into `entite` (`Type`,`Code`,`Nom`,`lien`) values 
('M','x','g',NULL),
('M','y','h',NULL),
('S','z','i',1),
('S','t','j',2),
('D','a','k',3),
('D','b','l',4);

Lien doit être une colonne qui admet la valeur 'NULL'.

On crée un lien entre Direction vers service, puis service vers division.
Enfin, c'est une convention que tu définies une fois pour toute.
Cela respecte la structure de ses trois tables et de la contrainte de référence.

@+