Discussion :

[soazig]

Bonjour,
Est-ce que la notion de contrainte d'unicité deferrable existe sous sql server.
Mes scripts doivent être compatibles avec SQL SERVER 2005,2008,2012.
Voilà le problème simplifié, j'ai une table contenant notamment
deux colonnes.
Clef varchar(10)
ordre int
pour l'exemple la clé est en varchar mais en vrai c'est un entier.
La colonne Clef est la clé primaire, et je souhaiterais que l'ordre soit unique, avec une contrainte d'unicité donc
La colonne ordre comme son nom l'indique indique un ordre d'affichage.
Supposons le contenu suivant
Etat 1

Code :

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

1
2
3
4
Clef   Ordre
toto   1
titi   2
tutu   3

Finalement on se rend compte que l'affichage doit être dans le nouvel ordre suivant
Etat 2

Code :

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

1
2
3
4
Clef   Ordre
toto   2
titi   3
tutu   1

A un moment donné pendant les updates entre l'état 1 et l'etat 2 ma contrainte d'unicité va péter, c'est pour cela que j'avais pensé à la créer en deferrable mais dans mes recherches, j'ai l'impression que SQL server ne l'a pas implémenté.

Ai-je raté quelque chose.
Soazig

[SQLpro]

Bonjour,
Est-ce que la notion de contrainte d'unicité deferrable existe sous sql server.
Mes scripts doivent être compatibles avec SQL SERVER 2005,2008,2012.

NON

...
A un moment donné pendant les updates entre l'état 1 et l'etat 2 ma contrainte d'unicité va péter, c'est pour cela que j'avais pensé à la créer en deferrable mais dans mes recherches, j'ai l'impression que SQL server ne l'a pas implémenté.

Ai-je raté quelque chose.
Soazig

Oui, vous avez raté l'essentiel ! Un bon SGBDR est par nature ensembliste (ce que ni MySQL, ni PostGreSQL respecte)
Donc le passage de l'état 1 de vos données unique à l'état 2 de vos données, toujours unique doit se faire par une seule requête et il n'y aura en aucun cas viol de l'unicité (un SGBD réellement relationnel traite ses données de manière ensembliste et non pas itérative...)

A +

[soazig]

Bonjour,
Je suis d'accord pour l'effet des transactions, c'est pour cela que je voulais rendre ma contrainte deferrable.

Je viens de faire le test suivant:

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
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
create table TEST
(
CLEF varchar(10),
ORDRE int,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED 
(
	CLEF ASC
));
 
alter table TEST
add constraint TEST_ORDRE_CONT unique (ordre);
 
GO
insert into TEST
(CLEF,ORDRE)
VALUES
('toto','1');
 
insert into TEST
(CLEF,ORDRE)
VALUES
('titi','2');
 
 
insert into TEST
(CLEF,ORDRE)
VALUES
('tutu','3');
 
 
select * from TEST ;
begin transaction
update TEST
SET 
ORDRE=2
where CLEF='toto';
 
update TEST
SET 
ORDRE=3
where CLEF='titi';
 
update TEST
SET 
ORDRE=1
where CLEF='tutu';
 
commit transaction
 
select * from TEST ;

Voilà ce que j'obtiens

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
CLEF ORDRE
toto 1
titi 2
tutu 3

(3 row(s) affected)

Msg 2627, Level 14, State 1, Line 20
Violation de la contrainte UNIQUE KEY 'TEST_ORDRE_CONT'. Impossible d'insérer une clé en double dans l'objet 'dbo.TEST'.
L'instruction a été arrêtée.
Msg 2627, Level 14, State 1, Line 25
Violation de la contrainte UNIQUE KEY 'TEST_ORDRE_CONT'. Impossible d'insérer une clé en double dans l'objet 'dbo.TEST'.
L'instruction a été arrêtée.
Msg 2627, Level 14, State 1, Line 30
Violation de la contrainte UNIQUE KEY 'TEST_ORDRE_CONT'. Impossible d'insérer une clé en double dans l'objet 'dbo.TEST'.
L'instruction a été arrêtée.
CLEF ORDRE
toto 1
titi 2
tutu 3

(3 row(s) affected)

C'est à peu prés à cela que je m'attendais, maintenant peux tu m'éclairer?
Avec une contrainte deferrable en revanche je n'aurais pas eu d'erreur
Merci
Soazig

[plume13]

Ce que je comprends de la réponse de SQLPro, c'est qu'il faut :
- soit faire une seule requête Update,
- soit passer par une valeur intermédiaire non utilisée, en utilisant une transaction évidemment.

Pour la première solution, on pourrait faire une requête du genre :

Code :

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

1
2
3
4
5
6
7
8
UPDATE t
SET ordre = new_ordre
FROM test t
INNER JOIN (
	SELECT CASE clef WHEN 'toto' THEN 2 WHEN 'titi' THEN 3 ELSE 1 END AS new_ordre, clef, ordre
	FROM test
	WHERE clef IN ('toto', 'titi', 'tutu')
) t2 ON t2.clef = t.clef

Après je ne sais pas quelle est la meilleure solution !

[SQLpro]

Bonjour,
Je suis d'accord pour l'effet des transactions, c'est pour cela que je voulais rendre ma contrainte deferrable.

Je viens de faire le test suivant:

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
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
create table TEST
(
CLEF varchar(10),
ORDRE int,
CONSTRAINT [PK_TEST] PRIMARY KEY CLUSTERED 
(
	CLEF ASC
));
 
alter table TEST
add constraint TEST_ORDRE_CONT unique (ordre);
 
GO
insert into TEST
(CLEF,ORDRE)
VALUES
('toto','1');
 
insert into TEST
(CLEF,ORDRE)
VALUES
('titi','2');
 
 
insert into TEST
(CLEF,ORDRE)
VALUES
('tutu','3');
 
 
select * from TEST ;
begin transaction
update TEST
SET 
ORDRE=2
where CLEF='toto';
 
update TEST
SET 
ORDRE=3
where CLEF='titi';
 
update TEST
SET 
ORDRE=1
where CLEF='tutu';
 
commit transaction
 
select * from TEST ;

Voilà ce que j'obtiens


C'est à peu prés à cela que je m'attendais, maintenant peux tu m'éclairer?
Avec une contrainte deferrable en revanche je n'aurais pas eu d'erreur
Merci
Soazig

Effectivement vous faites 3 requêtes donc il y a une violation de clef.
Relisez moi. je vous ait dit de ne faire qu'une seule requête !
Les SGBDR sont fait pour traiter des ensembles de données, par faire du ligne à ligne... Sinon, retournez à CoBOL ce sera plus efficace !!! ;-)

A +

[Waldar]

Si c'est dans une application avec un utilisateur qui déplace ses ordres, on peut les stocker dans une table temporaire le temps de la modification et la fusionner ensuite dans la table à la validation.

Si la génération de multiple ordre SQL n'est pas contournable, il reste la possibilité de passer par une valeur "bidon" (null, -1, 99999999), mais ce n'est pas super propre.

[soazig]

Bonjour,
J'adore les réponses pondérées du modérateur SQLPRO, qui donne envie de comprendre.

Plume13, j'avais compris cela aussi.

Il me semblait que la raison d'être du deferrable était justement de faire le jeu de test que je vous ai mentionné.

Vu que ma table n'a que 6 lignes et qu'elles seront mise à jour par moi, je ne vais pas m'embêter la vie, mais je voulais me cultiver. Mais a priori pour certains, les forums d'entraide ne sont pas des lieux propices à la culture, mais des lieux pour se faire tacler, rabrouer.

Néanmoins j'ai ma réponse, la notion de deferrable n'existe pas sous sql server.

Et malheureusement oui on m'oblige aussi à faire du cobol, et cela ne me remplit pas de joie. Ma récréation reste le SQL!

[edit]Je me suis rendue compte après coup que j'avais mal lu le premier poste de SQLPRO, j'avais lu transaction à la place de requête d'où mon jeu de tests.
Donc sur ce coup là, je suis en tort!
[/Edit]

Cordialement
Soazig

[aieeeuuuuu]

Bonjour,

En effet, pas de contrainte déférable sous SQL Server.

Vu que ma table n'a que 6 lignes et qu'elles seront mise à jour par moi, je ne vais pas m'embêter la vie, mais je voulais me cultiver.

Dans ce cas, je préfére la solution de plume, que l'on peut même ecrire de façon plus lisible ainsi :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
UPDATE T
	SET ORDRE = N.ordre
FROM TEST T
INNER JOIN (
	VALUES
	 ('toto',2)
	,('titi',3)
	,('tutu',1)
) N(clef, ordre)
	ON N.clef = T.CLEF

[plume13]

Ooooooh, je ne connaissais pas cette syntaxe ! Je mémorise