Cannot add a REFERENCES column with non-NULL default value
Cu cheile străine activate (PRAGMA foreign_keys = ON), ALTER TABLE ADD COLUMN nu poate adăuga o coloană cu REFERENCES și DEFAULT diferit de NULL, fiindcă nu poate verifica valoarea în rândurile existente. Adaug-o cu DEFAULT NULL, apoi completeaz-o cu UPDATE.
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
Când adaugi o coloană cu ALTER TABLE ... ADD COLUMN, SQLite nu rescrie rândurile existente: ele „văd” automat valoarea din DEFAULT. Dacă noua coloană e o cheie străină (REFERENCES clienti(id)) și cheile străine sunt active, valoarea implicită ar trebui verificată pentru fiecare rând existent. SQLite nu face această verificare la ALTER TABLE, așa că refuză orice DEFAULT diferit de NULL.
NULL e permis pentru că o cheie străină NULL nu face referire la nimic, deci nu încalcă legătura.
Soluția are doi pași: adaugi coloana fără default (sau cu DEFAULT NULL), apoi o completezi cu un UPDATE, care e verificat normal de cheia străină. Pe tabele goale SQLite acceptă și default-ul, de aceea eroarea apare abia pe baza cu date.
De ce apare: cauzele frecvente
1Default pentru o coloană nouă cu cheie străină
PRAGMA foreign_keys = ON;
ALTER TABLE comenzi
ADD COLUMN depozit_id INTEGER REFERENCES depozite(id) DEFAULT 1;PRAGMA foreign_keys = ON;
ALTER TABLE comenzi
ADD COLUMN depozit_id INTEGER REFERENCES depozite(id);
UPDATE comenzi SET depozit_id = 1;Coloana se adaugă cu NULL, iar UPDATE-ul pune valoarea și e verificat de cheia străină.
2Default adăugat ca să evite NOT NULL
ALTER TABLE comenzi
ADD COLUMN client_id INTEGER NOT NULL DEFAULT 0
REFERENCES clienti(id);ALTER TABLE comenzi
ADD COLUMN client_id INTEGER REFERENCES clienti(id);
UPDATE comenzi SET client_id = 1 WHERE client_id IS NULL;0 nu există în clienti, deci ar fi încălcat oricum cheia străină. Dacă ai nevoie de NOT NULL, recreează tabelul după ce completezi datele.
Repară eroarea, direct în pagină
Comenzile trebuie legate de depozitul din care pleacă. Scriptul activează cheile străine și adaugă coloana depozit_id cu DEFAULT 1, dar dă Cannot add a REFERENCES column with non-NULL default value. Repară-l ca toate comenzile existente să primească depozitul 1 și afișează id și depozit_id, ordonat după id.
PRAGMA foreign_keys = ON;
ALTER TABLE comenzi
ADD COLUMN depozit_id INTEGER REFERENCES depozite(id) DEFAULT 1;
SELECT id, depozit_id FROM comenzi ORDER BY id;id | depozit_id 10 | 1 11 | 1 12 | 1
Indiciu
Adaugă coloana fără default, apoi completeaz-o cu UPDATE.
Reparația
PRAGMA foreign_keys = ON;
ALTER TABLE comenzi
ADD COLUMN depozit_id INTEGER REFERENCES depozite(id);
UPDATE comenzi SET depozit_id = 1;
SELECT id, depozit_id FROM comenzi ORDER BY id;De ce merge
ALTER TABLE nu rescrie rândurile existente, deci nu ar fi putut verifica dacă valoarea implicită 1 există în depozite. De aceea, cu cheile străine active, SQLite permite doar NULL ca default.
Cu UPDATE-ul, valoarea se scrie efectiv în fiecare rând și e verificată de cheia străină; depozitul 1 există, deci toate cele trei comenzi primesc depozit_id = 1.
Alte forme ale mesajului
sqlite3.OperationalError: Cannot add a REFERENCES column with non-NULL default value
Cum o eviți pe viitor
Când adaugi o cheie străină unui tabel cu date, fă-o în doi pași: coloana fără default, apoi UPDATE. Verifică după aceea cu PRAGMA foreign_key_check; că toate valorile au corespondent. Pentru coloane care trebuie să fie și NOT NULL, folosește procedura de recreare a tabelului.
Întrebări frecvente
De ce fără PRAGMA foreign_keys merge?
Cu cheile străine dezactivate (implicit în SQLite), REFERENCES nu e verificat deloc, așa că default-ul e acceptat. Dar asta înseamnă că nici legătura nu e păzită.
Pot activa cheile străine după ALTER TABLE?
Da, dar nu ocoli problema așa: rândurile cu valori fără corespondent vor da FOREIGN KEY constraint failed la următoarele modificări.
Erori înrudite
FOREIGN KEY constraint failed Rândul inserat sau modificat trimite la un rând părinte care nu există (de exemplu o carte cu autor_id = 9, dar nu există autorul 9), sau încerci să ștergi un părinte care mai are copii. Folosește un id existent, creează întâi părintele sau șterge întâi rândurile copil (ori declară ON DELETE CASCADE). Cannot add a NOT NULL column with default value NULL ALTER TABLE ... ADD COLUMN cu NOT NULL, dar fără DEFAULT: rândurile existente ar primi NULL, ceea ce încalcă regula. Adaugă o valoare implicită (ADD COLUMN activ INTEGER NOT NULL DEFAULT 1) sau adaugă coloana fără NOT NULL și completeaz-o după. unknown column "autor" in foreign key definition În clauza FOREIGN KEY (autor) ai scris un nume de coloană care nu există în tabelul pe care îl creezi, de obicei pentru că coloana se numește altfel (autor_id). Pune în paranteză exact numele coloanei din tabel: FOREIGN KEY (autor_id) REFERENCES autori(id). Învață SQL de la zero, cu exerciții la fiecare lecție
Curs SQL: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 15 lecții sunt gratuite.
