Discussion :

[kOrt3x]

Bonjour à tous,

J'aimerai savoir comment faire pour insérer dans ma table une ligne dont la colonne "date" serait une sorte d'id unique.

Voici ma commande INSERT :

Code sql :

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

INSERT INTO planning VALUES ('2014-09-16','','');

Ce que je veux, c'est savoir comment éviter d'inserer une nouvelle ligne si la date (unique) est déjà dans la table.

Sachant que ma colonne "date" est de type DATE (format YYYY-MM-DD).

Par avance, merci pour votre aide.

[CinePhil]

Si la date doit être unique, pose une contrainte d'unicité sur la colonne de date.

[kOrt3x]

Si la date doit être unique, pose une contrainte d'unicité sur la colonne de date.

Merci pour ta réponse, mais comment fait-on cela ?

[CinePhil]

Voyez l'aide de votre SGBD l'ordre ALTER TABLE.

Si vous avez un outil visuel de gestion de votre BDD, ça doit pouvoir se faire en deux clics.

[kOrt3x]

C'est bon, j'ai trouvé, j'ai recréer ma table avec les infos que tu m'as donné :

Code :

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

1
2
3
4
5
6
CREATE TABLE `planning` (
  `date` date NOT NULL UNIQUE,
  `planning1` text NOT NULL,
  `planning2` text NOT NULL,
   UNIQUE (`date`)
)

Et là j'ai bien une erreur qui m'indique :

error sqlDuplicate entry '2014-09-17' for key 'date'

donc c'est bon, il le voit bien en doublon.

[Drizzt [Drone38]]

Tu as plusieurs possibilité pour répondre à ton problème initial.

- Ajouter effectivement une contrainte unique. Dans ce cas, si tu essayes une seconde insertion sur la même date, une erreur de type violation de contrainte unique sera remontée, erreur que tu devras gérer par ton programme appelant.
- Selon ton SGBD tu peux utiliser l'instruction MERGE INTO, qui te permet de faire une action de type INSERT si ta clef n'existe pas et UPDATE sinon. L'UPDATE pouvant être facultatif.
- Sinon il faut faire en deux requêtes, une première pour tester l'existence, une seconde pour faire l'insertion.


Pour ajouter une contrainte unique (à adapter en fonction du SGBD):

Code :

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

1
2
ALTER TABLE Persons
 ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

[SQLpro]

Tu as plusieurs possibilité pour répondre à ton problème initial.

- Ajouter effectivement une contrainte unique. Dans ce cas, si tu essayes une seconde insertion sur la même date, une erreur de type violation de contrainte unique sera remontée, erreur que tu devras gérer par ton programme appelant.

Oui, c'est la seule solution viable !

- Selon ton SGBD tu peux utiliser l'instruction MERGE INTO, qui te permet de faire une action de type INSERT si ta clef n'existe pas et UPDATE sinon. L'UPDATE pouvant être facultatif.

NON, voir ci après

- Sinon il faut faire en deux requêtes, une première pour tester l'existence, une seconde pour faire l'insertion.

NON. Vous oubliez une des principales composantes des SGBDR : la concurrence d'accès. Si deux utilisateurs lancent le même bout de code en même temps (et fatalement cela arrivera plus d'une fois) alors vous obtiendrez in fine des doublons.

A +

[Drizzt [Drone38]]

Oui j'aurais du préciser que dans tous les cas il faut mettre la contrainte d'unicité.

Par contre je suis surpris sur le MERGE INTO.
Si je fais deux MERGE INTO en simultané j'aurais en résultat 2 INSERT ? Comment garantir l'intégrité d'un MERGE INTO dans ce cas ?
N'y a-t-il pas des LOCK qui vont se positionner sur les tables pour éviter ce genre de problème de concurrence d'accès ?

[StringBuilder]

NON, voir ci après

Merge ne gère pas les accès concurrent ?

[Waldar]

Si je fais deux MERGE INTO en simultané j'aurais en résultat 2 INSERT ?

Non, la première transaction validée sera bien enregistrée, la seconde sera refusée car violation de la contrainte d'unicité.

[StringBuilder]

Non, la première transaction validée sera bien enregistrée, la seconde sera refusée car violation de la contrainte d'unicité.

Hmmm, ok, et y'a pas moyen de locker de façon exclusives les lignes en cours de mise à jour ?
En effet, je trouve plus propre d'avoir N merge concurrents qui sont mis en file d'attente car non sérialisables, que de me prendre des exceptions d'unicité dans la tête, puis faire des update, sur une ligne qui a potentiellement été supprimée entre le moment où j'ai eu l'exception et le moment où j'ai lancé mon update.

En gros, imaginons le cas suivant :

Code sql :

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

1
2
3
4
5
6
 
create table testmerge
(
     jour date primary key, -- ou unique not null si on veut rester dans l'exemple initial
     donnee varchar(50) not null
);

Session 1 souhaite :
- Dire que le 01/01/1900, on avait la donnée 'A'
- Dire que le 02/01/1900, on avait la donnée 'B'
- Supprimer le 02/01/1900 si la donnée est 'B'
- Dire que le 01/01/1900, on avait finalement la donnée 'C'

Session 2 souhaite :
- Dire que le 01/01/1900, on avait la donnée 'D'
- Un traitement un peu long mais pas trop
- Dire que le 02/01/1900, on avait la donnée 'E'

Les deux sessions tournent en //, et les conflits sont résolus de la façon "le dernier qui parle à gagné".

Lorsque la session 2 tente de faire un INSERT pour le 02/01/1900, on se prend une erreur dans la tête, car la ligne existe.
Quelques cycles CPU plus tard, la session 1 supprimer la donnée du 02/01/1900 qui n'a pas été modifiée.
La session 2 tente la mise à jour de la donnée du 02/01/1900... qui n'existe cette fois plus !

Du coup moi je trouve ça très moyen comme approche.
Avec des merge (avec un niveau de lock suffisant) on aurait bien la valeur 'E' dans la table, car soit la ligne aurait été supprimée par la première session, et on aurait donc inséré une nouvelle ligne, soit on l'aurait mise à jour dans la session 2 avant que la session 1 ne tente de la supprimer, et vu que data n'était plus égale à 'B', alors session 1 ne l'aurait pas supprimée.

Comment faire du coup ?

[SQLpro]

Non, la première transaction validée sera bien enregistrée, la seconde sera refusée car violation de la contrainte d'unicité.

Non, mais sans contrainte d'unicité ça génère des doublons. En fait il faut savoir que les requêtes ensemblistes (UNION, INSTERSECT et EXCEPT), tout comme le MERGE sont des requêtes assemblées par partie (SELECTs indépendants...) et ne sont pas intégralement transactionnelles (norme SQL). Du coup il est possible de se retrouver dans des problématiques de doublons...

A +

[StringBuilder]

Aucune idée si c'est vraiment une bonne solution, mais en tout cas ça marche :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
 
begin transaction
 
merge into testmerge with (xlock rowlock) as target
using (select '1900-01-01' jour, 'A' data union select '1900-01-02' jour, 'B' data) as source (jour, donnee)
on (target.jour = source.jour)
when matched then
	update set donnee = source.donnee
when not matched then
	insert (jour, donnee) values (source.jour, source.donnee);

Si je lance dans la foulée, sans commit, dans une autre sessions :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
 
begin transaction
 
merge into testmerge with (xlock rowlock) as target
using (select '1900-01-01' jour, 'C' data union select '1900-01-02' jour, 'D' data) as source (jour, donnee)
on (target.jour = source.jour)
when matched then
	update set donnee = source.donnee
when not matched then
	insert (jour, donnee) values (source.jour, source.donnee);

Alors la seconde reste en attente.
Si je commit ou rollback la première, la seconde se lance normalement.

Et du coup j'ai pas de souci.

SQL Server 2014 Express

[SQLpro]

Le blocage va dépendre de ton niveau d'isolation et de tes index.

A +