dexblog.ro
Acces rapid: Învață AI Învață Programare Specificații Dispozitive Comparații
SQLITE Toate comenzile SQLite

INSERT OR IGNORE / OR REPLACE în SQLite

Inserare și modificare SQLite 3.0.0

Alege ce face SQLite când un INSERT sau UPDATE încalcă o constrângere: oprește comanda, sare peste rând sau înlocuiește rândul vechi.

Sintaxă 
INSERT OR {ABORT | FAIL | IGNORE | REPLACE | ROLLBACK} INTO tabel ...;
UPDATE OR {ABORT | FAIL | IGNORE | REPLACE | ROLLBACK} tabel SET ...;
REPLACE INTO tabel ...;              -- la fel ca INSERT OR REPLACE
CREATE TABLE t (x TEXT UNIQUE ON CONFLICT IGNORE);

Ce face INSERT OR IGNORE / OR REPLACE

Când o comandă încalcă o constrângere UNIQUE, NOT NULL, CHECK sau PRIMARY KEY, SQLite aplică un „algoritm de rezolvare a conflictului”. Implicit este ABORT: comanda curentă este anulată (rândurile pe care le-a modificat deja revin la valoarea de dinainte), dar tranzacția rămâne deschisă. Cu INSERT OR ... sau UPDATE OR ... alegi alt algoritm doar pentru acea comandă.

IGNORE sare peste rândul cu probleme și continuă cu celelalte. REPLACE șterge rândurile care intră în conflict și pune rândul nou în locul lor; pentru NOT NULL folosește valoarea implicită a coloanei. FAIL oprește comanda, dar păstrează modificările făcute deja de ea, iar ROLLBACK anulează toată tranzacția. REPLACE INTO este o prescurtare pentru INSERT OR REPLACE INTO.

Algoritmul poate fi scris și direct în definiția tabelului, de exemplu UNIQUE ON CONFLICT IGNORE. Atunci se aplică la fiecare INSERT care nu cere explicit altceva. Aceasta este o extensie SQLite; în alte baze de date nu există.

Parametri

Parametru Descriere
ABORT Implicit. Anulează modificările comenzii curente și dă eroare; tranzacția continuă.
FAIL Dă eroare, dar păstrează rândurile modificate deja de aceeași comandă.
IGNORE Sare peste rândul care produce conflictul, fără eroare.
REPLACE Șterge rândul vechi care intră în conflict și inserează rândul nou.
ROLLBACK Dă eroare și anulează toată tranzacția curentă.

Exemple

Exemplele de pe această pagină pornesc de la datele de mai jos. Rulează mai întâi acest cod, apoi exemplul.

CREATE TABLE elevi (
  cod   TEXT PRIMARY KEY,
  nume  TEXT NOT NULL,
  clasa TEXT,
  nota  INTEGER
);
INSERT INTO elevi VALUES
  ('E01', 'Ana Ionescu',  '9A', 10),
  ('E02', 'Mihai Popa',   '9A', 8),
  ('E03', 'Ioana Rusu',   '9B', 9);

Exemplul 1: OR IGNORE: inserează doar rândurile noi

Codul E01 există deja, așa că acel rând este sărit. E04 este nou și ajunge în tabel. changes() confirmă că s-a inserat un singur rând.

INSERT OR IGNORE INTO elevi VALUES
  ('E01', 'Alt nume', '9C', 5),
  ('E04', 'Radu Stan', '9B', 7);
SELECT changes() AS inserate;

SELECT * FROM elevi ORDER BY cod;

Rezultat

╭──────────╮
│ inserate │
╞══════════╡
│        1 │
╰──────────╯
╭─────┬─────────────┬───────┬──────╮
│ cod │    nume     │ clasa │ nota │
╞═════╪═════════════╪═══════╪══════╡
│ E01 │ Ana Ionescu │ 9A    │   10 │
│ E02 │ Mihai Popa  │ 9A    │    8 │
│ E03 │ Ioana Rusu  │ 9B    │    9 │
│ E04 │ Radu Stan   │ 9B    │    7 │
╰─────┴─────────────┴───────┴──────╯

Exemplul 2: OR REPLACE înlocuiește tot rândul

Am dat doar cod și nume. Rândul vechi E02 a fost șters, iar cel nou are clasa și nota goale (NULL). Un upsert cu ON CONFLICT DO UPDATE ar fi păstrat clasa și nota.

INSERT OR REPLACE INTO elevi (cod, nume) VALUES ('E02', 'Mihai Popa');

SELECT * FROM elevi ORDER BY cod;

Rezultat

╭─────┬─────────────┬───────┬──────╮
│ cod │    nume     │ clasa │ nota │
╞═════╪═════════════╪═══════╪══════╡
│ E01 │ Ana Ionescu │ 9A    │   10 │
│ E02 │ Mihai Popa  │       │      │
│ E03 │ Ioana Rusu  │ 9B    │    9 │
╰─────┴─────────────┴───────┴──────╯

Exemplul 3: Regula de conflict pusă în tabel

Coloana email are UNIQUE ON CONFLICT IGNORE, deci adresa repetată este ignorată fără ca INSERT să mai ceară OR IGNORE.

CREATE TABLE abonati (email TEXT UNIQUE ON CONFLICT IGNORE);
INSERT INTO abonati VALUES ('ana@exemplu.ro');
INSERT INTO abonati VALUES ('vlad@exemplu.ro');
INSERT INTO abonati VALUES ('ana@exemplu.ro');

SELECT rowid, email FROM abonati;

Rezultat

╭───────┬─────────────────╮
│ rowid │      email      │
╞═══════╪═════════════════╡
│     1 │ ana@exemplu.ro  │
│     2 │ vlad@exemplu.ro │
╰───────┴─────────────────╯

Greșeli frecvente

  • Folosești OR REPLACE pe un tabel referit prin chei străine. Rândul vechi este șters, deci cu PRAGMA foreign_keys = ON se aplică ON DELETE (de exemplu CASCADE șterge și rândurile copil).
  • Te bazezi pe OR IGNORE și nu mai vezi greșelile. Ignoră și încălcările NOT NULL sau CHECK, nu doar dublurile; un rând invalid dispare în tăcere.
  • Crezi că OR REPLACE păstrează rowid-ul. Rândul nou poate primi alt rowid dacă nu dai cheia explicit.

Comenzi înrudite

  • INSERT ... ON CONFLICT (upsert): Inserează un rând sau, dacă cheia există deja, actualizează rândul existent (ori nu face nimic), într-o singură comandă.
  • RETURNING: Face ca INSERT, UPDATE sau DELETE să returneze rândurile modificate, de exemplu id-ul generat, fără un SELECT separat.
  • BEGIN, COMMIT, ROLLBACK: Grupează mai multe modificări într-o tranzacție: fie se salvează toate cu COMMIT, fie niciuna cu ROLLBACK.
  • PRAGMA foreign_keys: Pornește verificarea cheilor străine. În SQLite este oprită implicit și trebuie pornită la fiecare conexiune.