Sqlite insert into avec des noms uniques, obtention de l'id.

Question

J'ai une liste de chaînes de caractères à insérer dans une base de données. Elles DOIVENT être uniques. Lorsque je les insère, je voudrais leur ID (pour l'utiliser comme clé étrangère dans une autre table), j'utilise donc last_insert_rowid. Je rencontre deux problèmes.

1. Si j'utilise replace, leur id (INTEGER PRIMARY KEY) se met à jour ce qui casse ma base de données (les entrées pointent vers ID inexistants)
2. Si j'utilise ignore, le rowid n'est pas mis à jour et je n'obtiens pas l'ID correct.

Comment puis-je obtenir leurs identifiants ? Si je n'en ai pas besoin, je ne voudrais pas utiliser une instruction de sélection pour vérifier et insérer la chaîne si elle n'existe pas. Comment dois-je faire ?

Answer 1

Lorsqu'une violation de la contrainte UNIQUE se produit, l'algorithme REPLACE supprime les rangées préexistantes qui sont à l'origine de la violation de la contrainte avant d'insérer ou de mettre à jour la rangée actuelle et la commande continue de s'exécuter normalement. Cela entraîne une modification du rowid et crée le problème suivant

Y:> **sqlite3 test**  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> **create table b (c1 integer primary key, c2 text UNIQUE);**  
sqlite> **insert or replace into b values (null,'test-1');**  
sqlite> **select last_insert_rowid();**  
1  
sqlite> **insert or replace into b values (null,'test-2');**  
sqlite> **select last_insert_rowid();**  
2  
sqlite> **insert or replace into b values (null,'test-1');**  
sqlite> **select last_insert_rowid();**  
3  
sqlite> **select * from b;**  
2|test-2  
3|test-1  

La solution consiste à modifier la définition de la colonne c2 comme suit

create table b (c1 integer primary key, c2 text UNIQUE ON CONFLICT IGNORE);

et de supprimer la clause "ou remplacer" de vos encarts ;

puis lors du test après votre insertion, vous devrez exécuter le sql suivant : select last_insert_rowid(), changes();

sqlite> **create table b (c1 integer primary key, c2 text UNIQUE ON CONFLICT IGNORE);**  
sqlite> **insert into b values (null,'test-1');**  
sqlite> **select last_insert_rowid(), changes();**  
1|1  
sqlite> **insert into b values (null,'test-2');**  
sqlite> **select last_insert_rowid(), changes();**  
2|1  
sqlite> **insert into b values (null,'test-1');**  
sqlite> **select last_insert_rowid(), changes();**  
2|0  

La valeur de retour des modifications après la troisième insertion sera une notification à votre application que vous devrez rechercher le rowid de "test-1", puisqu'il était déjà dans le fichier. Bien entendu, s'il s'agit d'un système multi-utilisateurs, vous devrez également intégrer tout cela dans une transaction.

Answer 2

J'utilise actuellement les éléments suivants

insert into tbl(c_name) select 'val' where not exists(select id from tbl where c_name ='val');
select id from tbl where c_name ='val';

Answer 3

Par "ils DOIVENT être uniques", veulent-ils dire que vous êtes sûrs qu'ils le sont, ou que vous voulez une erreur comme résultat s'ils ne le sont pas ? Si vous faites simplement de la chaîne elle-même une clé dans sa table, alors je ne comprends pas comment 1 ou 2 pourrait être un problème -- vous obtiendrez une erreur comme souhaité en cas de duplication non désirée, sinon l'ID correct. Peut-être pouvez-vous clarifier votre question avec un petit exemple de code SQL que vous utilisez, la table en question, le comportement que vous observez, et le comportement que vous voudriez à la place... ?

Modifié : merci pour la modification mais je ne sais toujours pas quel SQL vous pose problème ! Si votre table vient de, par exemple :

CREATE TABLE Foo(
  theid INTEGER PRIMARY KEY AUTOINCREMENT,
  aword TEXT UNIQUE ABORT
  )

alors toute tentative d'INSERTION d'un mot dupliqué échouera (l'option ABORT est facultatif, puisqu'il s'agit de la valeur par défaut de l'option UNIQUE ) - n'est-ce pas ce que vous veulent étant donné que vous dites que les mots "DOIVENT être uniques", c'est-à-dire que c'est une erreur s'ils ne le sont pas ?

Answer 4

La réponse correcte à votre question est : Cela ne peut pas être fait dans sqlite. Vous devez faire une requête de sélection supplémentaire. En citant le docs pour last_insert_rowid :

Un INSERT qui échoue à cause d'une violation de contrainte n'est pas un INSERT réussi et ne change pas la valeur retournée par cette routine. Par conséquent, INSERT OR FAIL, INSERT OR IGNORE, INSERT OR ROLLBACK et INSERT OR ABORT ne modifient pas la valeur de retour de cette routine lorsque leur insertion échoue.

Answer 5

J'ai le même problème en 2022, mais depuis SQLite3 version 3.35.0 (2021-03-12), nous avons RETOURNER . Combiné avec UPSERT il est désormais possible d'y parvenir

sqlite> create table test (id INTEGER PRIMARY KEY, text TEXT UNIQUE);
sqlite> insert into test(text) values("a") on conflict do update set id = id returning id;
1
sqlite> insert into test(text) values("a") on conflict do update set id = id returning id;
1
sqlite> insert into test(text) values("b") on conflict do update set id = id returning id;
2
sqlite> insert into test(text) values("b") on conflict do update set id = id returning id;
2
sqlite> select * from test;
1|a
2|b
sqlite> insert into test(text) values("b") on conflict do nothing returning id;
sqlite>

Malheureusement, il s'agit toujours d'une solution de contournement plutôt que d'une solution élégante... En cas de conflit, l'insertion devient une mise à jour. Cela signifie que vos déclencheurs de mise à jour se déclencheront, donc vous voudrez peut-être rester à l'écart de cette solution !

Lorsque l'insertion est convertie en une mise à jour, elle besoins pour faire quelque chose (cf. lien). Cependant, nous ne voulons pas faire quoi que ce soit, alors nous faisons un no-op en mettant à jour id avec lui-même. Alors, returning id nous donne ce que nous voulons.

Notes :

• Notre no-op fait en fait une mise à jour, donc cela coûte du temps, et le déclencheur on update fera feu. Mais sans déclencheur, il n'a aucun effet sur les données.
• Utilisation de on conflict do nothing returning id n'échoue pas, mais ne renvoie pas l'id.
• Si elle est utilisable (vérifiez à nouveau vos déclencheurs), et si toutes vos tables utilisent la clé primaire id alors cette technique ne nécessite aucune spécialisation : il suffit de copier/coller on conflict do update set id = id returning id;