Contrainte d'unicité deferrable

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:

---

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:

---

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

Citation:

---

Envoyé par 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.

---

NON

Citation:

---

...
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 +

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:

---

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

Citation:

---

(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

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:

---

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 !

Citation:

---

Envoyé par 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:

---

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 +

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.

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

Bonjour,

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

Citation:

---

Envoyé par soazig

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:

---

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

---

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