Discussion :

[DiverSIG]

Bonjour,

Je crée une table en SQL, j'ai 2 variables de ma table qui doivent être en NOT NULL exclusif, je m'explique:
- elles ne doivent pas être toutes les 2 null
- elles ne doivent pas être toutes les 2 not null
donc si l'une est null l'autre doit être renseigné, et inversement.

Comment exprimer cette contrainte en SQL lors de la création de la table ?

Merci,

Nico

[Artemus24]

Salut DiverSIG.

Fusionne tes deux colonnes en une seul et ainsi elle ne sera jamais NULL mais toujours renseignée.
Si tu dois distinguer leur origine, tu peux ajouter une colonne pour dire à qui appartient la valeur.

[Séb.]

Tu peux utiliser une contrainte de table et l'opérateur logique XOR.

Avec MySQL :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
create table test (
    id int primary key auto_increment,
    v1 int,
    v2 int,
    constraint `une seule colonne valorisée` check (v1 is not null xor v2 is not null)
);
 
insert into test set v1 = 123, v2 = null; -- OK
insert into test set v1 = null, v2 = 234; -- OK
insert into test set v1 = 123, v2 = 234; -- Error Code: 3819. Check constraint 'une seule colonne valorisée' is violated.
insert into test set v1 = null, v2 = null; -- Error Code: 3819. Check constraint 'une seule colonne valorisée' is violated.

[SQLpro]

XOR n'existe pas en SQL.... Mais OR oui !

Code :

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

1
2
3
 
...
    constraint CK_ALTERNATE_NOT_NULL check (v1 is null AND v2 is not null OR v1 is not null AND v2 is null)

[escartefigue]

Effectivement XOR n'existe que dans le jargon MySQL/MariaDB, mais visiblement certains l'ignorent, j'en veux pour preuve les deux votes négatifs attribués à tort à la réponse de Fred (SQLPro).

[Artemus24]

Salut à tous.

Code mysql :

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

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
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
USE `base`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned NOT NULL auto_increment primary key,
  `v1`     integer unsigned     NULL,
  `v2`     integer unsigned     NULL,
   constraint `On_Col` check (v1 is not null xor v2 is not null)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`v1`,`v2`) values (123, null)
--------------
 
--------------
insert into `test` (`v1`,`v2`) values (null,234)
--------------
 
--------------
insert into `test` (`v1`,`v2`) values (234,456)
--------------
 
ERROR 3819 (HY000) at line 24: Check constraint 'On_Col' is violated.
--------------
insert into `test` (`v1`,`v2`) values (null,null)
--------------
 
ERROR 3819 (HY000) at line 25: Check constraint 'On_Col' is violated.
--------------
select * from `test`
--------------
 
+----+------+------+
| id | v1   | v2   |
+----+------+------+
|  1 |  123 | NULL |
|  2 | NULL |  234 |
+----+------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Je viens de le tester en reprenant l'exemple de Séb. et a priori le XOR existe bien sous Mysql.
Sauf qu'il aurait été pertinent de préciser dans quel SGBDR nous devons résoudre cette contrainte.
Sinon, oui, SQLPRO a raison de souligner que XOR n'existe pas en SQL.

Pour revenir sur le problème soulevé par DiverSIG, je ne vois pas l'intérêt de créer deux colonnes sachant qu'on peut les fusionner en une seule.
Ne serait il pas plus simple d'en créer une seule ?
Mais vu les explications succinctes, il serait bien de détailler pourquoi cette contrainte
Ne peut on pas faire autrement ?

[SQLpro]

...
Pour revenir sur le problème soulevé par DiverSIG, je ne vois pas l'intérêt de créer deux colonnes sachant qu'on peut les fusionner en une seule.
Ne serait il pas plus simple d'en créer une seule ?...

Non, car les deux informations n'ont rien à voir ensemble et il faudrait rajouter une colonne de discrimination pour savoir si c'est une information de v1 ou de v2 qu'il s'agit !

A +

[escartefigue]

Bonjour,

Mais vu les explications succinctes, il serait bien de détailler pourquoi cette contrainte

En effet, faute d'en savoir plus, il est difficile de se prononcer sur le "comment".

Si cette contrainte est la conséquence d'une mauvaise modélisation d'une contrainte de partition de type XT, exclusion et totalité, comme il semble que ce soit le cas, alors rien ne justifie la présence d'attributs, encore moins d'attributs "nullables".
Rappelons que le marqueur "null" complexifie les tables de décisions (voir ICI), et qu'il est sage de l'éviter autant que faire se peut

[Artemus24]

@ SQLPRO : en effet, nous ne savons rien des valeurs de ces deux colonnes.

@ Escartefigue : je suis du même avis, je pense qu'il y a un problème de modélisation.

[fred1599]

Hello,

Il est tout de même bizarre que sans connaître le SGBD et sa version (CHECK incertains), on puisse avancer sur ce sujet...

Et si vous voulez taper dans le 1000, il serait intéressant de connaître les noms de colonnes définis par le PO.

[Michel.Priori]

il serait intéressant de connaître les noms de colonnes définis par le PO.

J'ai vécu l'époque où la modélisation objet a mis en évidence que -dans les faits- les méthodes "traditionnelles" passaient à coté des factorisations.
Par exemple il n'était pas rare d'avoir les informations d'adresse dans la table des fournisseurs et ces mêmes informations dans la table des clients.

Maintenant il est commun de considérer qu'il faille avoir une table "adresse" commune à tous les tiers.
Se pose alors la question de comment construire les FK de la table "adresse" vers les tables "fournisseur" et "client".
La première solution consistant à avoir plusieurs colonnes du type id_fournisseur et id_client ; ainsi les FK déclaratives fonctionnent bien
Mais, effectivement, comment s'assurer qu'une adresse référence au moins un tiers, qu'il soit fournisseur ou client ?

Mon exemple ne doit pas être le bon car il n'est pas fondamentalement exclu qu'un fournisseur soit aussi un client, et donc que la même adresse référence les 2 'tiers'
[EDIT] et le post initial portait sur le XOR, donc soit l'un soit l'autre [\EDIT]
Mais j'imagine que c'est un truc du genre.

Note : dans ce que je décris ici je sais bien que l'erreur de conception est de ne pas avoir factorisé la table des tiers.

Mon petit doigt me dit qu'il va y avoir un joli débat autour de ça
Espérons que l'auteur du post se manifeste avant de constater qu'on ne répond plus à sa demande ...

[tatayo]

Bonjour,
De mon point de vue, pour les adresses la liaison doit se faire dans l'autre sens: l'id de l'adresse dans les tables Fournisseur, Client...
Et si on peut avoir plusieurs adresses pour un même client (livraison, facturation) alors il faut une table de liaison, avec le type d'adresse (dans une autre table, bien sûr).

Tatayo.

[Michel.Priori]

De mon point de vue, pour les adresses la liaison doit se faire dans l'autre sens: l'id de l'adresse dans les tables Fournisseur, Client...
Et si on peut avoir plusieurs adresses pour un même client (livraison, facturation) alors il faut une table de liaison, avec le type d'adresse (dans une autre table, bien sûr).

Aussi
Cela inverse la vision de ce qu'est une adresse :
* est-ce un point d'entrée dont un ou plusieurs tiers dépendent ?
* est-ce un attribut (simple ou multiple) d'un tiers ?

Notons que dans cette dernière construction il est pénible, partant d'une ligne adresse, de retrouver la table mère (toujours pareil, s'il y a plusieurs tables de tiers)
La table de liaison reste une bonne option pour concilier les 2 visions, sans toutefois résoudre la problématique de retrouver les tiers référençant une ligne d'adresse.

[escartefigue]

Bonjour,

Il est possible qu'on s'éloigne du sujet initial.
Quoi qu'il en soit, raisonner sur les tables est une mauvaise approche, la modélisation doit être le résultat de règles de gestion.

À partir des règles de gestion validées par le donneur d'ordre, on établit le MCD et les tables ne sont qu'une conséquence du MCD.

Si l'on considère qu'une même personne peut être à la fois un client, un fournisseur, un mandataire, un délégataire, ce qui n'est pas rare, alors on a tout intérêt à modéliser un type d'entité personne associé à un type d'entité adresse
Ainsi, on évite les redondances d'adresses pour les personnes qui ont plusieurs rôles.
Ce faisant, l'adresse peut être considérée comme une entité-type faible de la personne et de ce fait, être identifiée relativement à la personne.
Le type d'entité personne sera décliné en sous-types personne morale et personne physique les attributs étant spécifiques.
Par contre, il n'est pas forcément utile de spécialiser les types de personnes. Par exemple, pour savoir si une personne est cliente, il suffit de vérifier si elle a émis des commandes et si elle est fournisseur, alors c'est qu'on lui a envoyé des commandes...