INSERT INTO seulement s'il n'existe pas dans la table.

Version imprimable

16/09/2014, 09h05
kOrt3x

INSERT INTO seulement s'il n'existe pas dans la table.

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:

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.
16/09/2014, 09h07
CinePhil

Si la date doit être unique, pose une contrainte d'unicité sur la colonne de date.
16/09/2014, 09h10
kOrt3x

Citation:

Envoyé par CinePhil

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 ?
16/09/2014, 09h19
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.
16/09/2014, 09h22
kOrt3x
C'est bon, j'ai trouvé, j'ai recréer ma table avec les infos que tu m'as donné :
Code:

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 :

Citation:

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

donc c'est bon, il le voit bien en doublon.
16/09/2014, 09h23
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:

1 2 ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
16/09/2014, 14h18
SQLpro

Citation:

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

Oui, c'est la seule solution viable !

Citation:

- 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

Citation:

- 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 +
16/09/2014, 14h38
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 ?
16/09/2014, 15h49
StringBuilder

Citation:

Envoyé par SQLpro

NON, voir ci après

Merge ne gère pas les accès concurrent ?
16/09/2014, 16h00
Waldar

Citation:

Envoyé par Drizzt [Drone38]

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é.
16/09/2014, 16h32
StringBuilder
Citation:

Envoyé par Waldar

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:

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 ?
16/09/2014, 16h35
SQLpro

Citation:

Envoyé par Waldar

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 +

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

Code:

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:

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

16/09/2014, 17h13
SQLpro

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

A +