Discussion :

[CinePhil]

Bonjour,
Un truc que je n'ai encore jamais utilisé : la contrainte CHECK dans une création ou une modification de table.

Mon besoin :
J'ai une table qui a deux clés étrangères pointant sur la même table et je souhaite spécifier que les deux clés doivent avoir une valeur différente.
Je pense à la syntaxe suivante mais est-elle bonne ?

Code :

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

1
2
CREATE TABLE ...
CHECK C_FK_IdAttaquant <> C_FK_IdDefenseur

Question subsidiaire : Si quelqu'un sait comment inscrire cette contrainte directement dans MySQL Workbench je le remercie d'avance.

[Maljuna Kris]

Saluton,

J'ai une table qui a deux clés étrangères pointant sur la même table et je souhaite spécifier que les deux clés doivent avoir une valeur différente.
Je pense à la syntaxe suivante mais est-elle bonne ?

Code :

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

1
2
CREATE TABLE ...
CHECK C_FK_IdAttaquant <> C_FK_IdDefenseur

MySQL ne gère pas nativement ce genre de contrainte à ma connaissance.
Par contre, ça doit pouvoir se faire via un trigger.

[CinePhil]

MySQL ne gère pas nativement ce genre de contrainte à ma connaissance.

D'après la doc c'est prévu pour les tables de type Inno db et c'est ce que j'utilise pour avoir les contraintes de clés étrangères contrôlées par la BDD.

[Maljuna Kris]

Désolé, il faut que je change de lunettes.
A mon corps défendant, je suis plutôt allé consulter la doc du côté de http://dev.mysql.com/doc/refman/5.0/...nstraints.html où il n'en dise pas un mot.
Plus généralement j'ai parcouru cette partie de la doc (accès par l'Index) http://dev.mysql.com/doc/refman/5.0/fr/constraints.html sans trouver la moindre référence à la syntaxe de CHECK dans la définition d'une contrainte.

[CinePhil]

J'ai appelé ça 'contrainte' en français mais effectivement d'après la doc ce n'est pas une 'CONSTRAINT'.

[Antoun]

Si vous lisez de très près (voire même entre les lignes), vous devriez conclure comme moi que les CHECK ne sont pas implémentés (même s'ils sont acceptés), ni par InnoDB, ni par un autre moteur.

En MySQL version 3.23.44 et plus récent, les tables InnoDB supportent la vérification de clé étrangères. See Chapitre 15, Le moteur de tables InnoDB. Notez que la syntaxe des clés étrangères FOREIGN KEY de InnoDB est plus restrictive que la syntaxe présentée ci-dessus. InnoDB ne permet pas la spécification d'un index_name, et les colonnes de la table référencée doivent être explicitement nommées. Depuis la version 4.0.8, InnoDB supporte les clauses ON DELETE et ON UPDATE avec les clés étrangères. Voyez le manuel InnoDB pour la syntaxe précise. Section 15.7.4, « Contraintes de clés étrangères FOREIGN KEY ».

Pour les autres types de tables, le serveur MySQL n'analyse pas les clauses FOREIGN KEY, CHECK et REFERENCES dans les commandes CREATE TABLE, et aucune action n'est réalisée. See Section 1.5.5.5, « Les clés étrangères ».

[Maljuna Kris]

Pour les autres types de tables, le serveur MySQL n'analyse pas les clauses FOREIGN KEY, CHECK et REFERENCES dans les commandes CREATE TABLE, et aucune action n'est réalisée. See Section 1.5.5.5, « Les clés étrangères ».

Je comprends plutôt que cela n'est pas implémenté dans les tables qui ne sont pas de type InnoDB, (les autres types de tables) et donc que cela l'est dans celles-ci.

[Antoun]

Remonte d'un paragraphe !

[CinePhil]

Je suis d'accord avec Maljuna Kris. Le premier paragraphe parle des tables InnoDB et des CONSTRAINT FOREIGN KEY et le second dit que pour les autres tables (pas InnoDB donc) :

le serveur MySQL n'analyse pas les clauses FOREIGN KEY, CHECK et REFERENCES

Donc pour InnoDB il les analyse !

Je verrai bien, je n'en suis pas encore à l'implémentation.

Pas d'idée pour insérer mon CHECK directement dans MySQL Workbench ?
Comme l'implémentation se fera via un fichier SQL parce que je n'ai pas la version complète, je pourrai toujours modifier le dit fichier à la main pour rajouter les clauses manquantes mais si ça pouvait être automatique ce serait mieux.

[Antoun]

Puisque vous vous laissez avoir par la traduction, passez à la version anglaise :

The CHECK clause is parsed but ignored by all storage engines.

[CinePhil]

OK. C'est confirmé par la doc sur ALTER TABLE :

La clause CHECK est analysée mais ignorée par tous les moteurs de stockage. See Section 13.2.5, « Syntaxe de CREATE TABLE ». La raison pour accepter mais ignorer ces clauses est que cela renforce la compatibilité avec le code des autres serveurs SQL, et qu'il est possible de créer des tables avec des références. See Section 1.5.5, « Différences entre MySQL et le standard SQL-92 ».

Je viens de tester.

Code :

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

1
2
ALTER TABLE test
ADD CONSTRAINT CHECK col2 <> col3

C'est accepté par MySQL mais ensuite si j'insère une ligne violant cette contrainte, la ligne est acceptée.

Et il s'agit bien d'une CONSTRAINT parce que la syntaxe :

Code :

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

1
2
ALTER  TABLE test 
CHECK col1 <> col2

est refusée par MySQL.

Il faudrait que MySQL accorde sa doc entre les différents chapitres, ainsi que les traductions !

[Maljuna Kris]

La traduction de la doc en espagnol semble plus fidèle que celle en français

Para otros motores de almacenamiento, MySQL Server parsea la sintaxis FOREIGN KEY y REFERENCES en comandos CREATE TABLE , pero no hace nada. La cláusula CHECK se parsea pero se ignora en todos los motores de almacenamiento.

Il ne me reste plus qu'à en proposer une en espéranto

[CinePhil]

Je réactive ce sujet parce que je reprends ce projet qui était quelque peu en sommeil...

Donc j'aimerais toujours implanter une contrainte CHECK qui n'est malheureusement pas prise en compte par MySQL.

Il parait qu'on peut faire ça avec un trigger. Comme je n'en ai jamais encore utilisé, je tâtonne un peu.
Est-ce que la syntaxe ci-dessous vous paraît correcte ?

Code :

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

1
2
CREATE TRIGGER ctrlAttaqDef BEFORE INSERT ON Combats
FOR EACH ROW Co_IdAttaquant <> Co_IdDefenseur

PS :
Dans MySQL Workbench, dans l'onglet où on peut écrire les triggers, il y a ce code :

Code :

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

1
2
3
-- Trigger DDL Statements
USE `swing`;
DELIMITER //

Est-il utile ?
Je comprends qu'il commence par dire d'utiliser le schéma 'swing' mais à quoi sert le DELIMITER ?

[Antoun]

Le problème est qu'il n'y a pas de moyen direct d'annuler l'ordre en cours dans un trigger. L'astuce habituelle consiste à provoquer une erreur, par exemple en mettant une colonne obligatoire à NULL... kazou a très bien traité le problème : http://alain-defrance.developpez.com...integre-error/

(l'histoire du DELIMITER y est également expliquée)

[CinePhil]

Si j'ai bien compris ce que dit le tutoriel, un trigger tel que ci-dessous permettrait d'empêcher l'insertion en générant une erreur par MySQL, à charge du programme applicatif de se débrouiller avec pour la gérer ?

Code :

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
USE `swing`;
DELIMITER //
 
CREATE TRIGGER TR_AttaqDef
BEFORE INSERT
ON Combats
FOR EACH ROW
BEGIN
    DECLARE IdDefenseur MEDIUMINT;
 
    SELECT O_FK_IdJoueur INTO IdDefenseur
    FROM ObjetSpatiaux
    WHERE O_Id = NEW.Co_FK_IdObjetDefenseur;
 
    IF IdDefenseur = NEW.Co_FK_IdAttaquant THEN
        SET NEW.CO_FK_IdAttaquant = NULL;
        SET NEW.CO_FK_IdObjetDefenseur = NULL;
    END IF;
END;
//