Ograniczenia UNIQUE w SQLite: kolumny, klucze złożo…

Q: Jak dodać ograniczenie UNIQUE w SQLite?

Dopisz UNIQUE do definicji kolumny (email TEXT UNIQUE) albo użyj klauzuli UNIQUE(col1, col2) na poziomie tabeli, gdy unikalność dotyczy wielu kolumn. SQLite wymusza ją, tworząc w tle indeks unikalny i odrzucając każdy INSERT lub UPDATE, który stworzyłby duplikat.

Q: Czym różni się UNIQUE od PRIMARY KEY w SQLite?

Tabela może mieć tylko jeden PRIMARY KEY, ale wiele ograniczeń UNIQUE. PRIMARY KEY oznacza też NOT NULL (w tabelach STRICT i dla INTEGER PRIMARY KEY), a kolumny UNIQUE mogą zawierać wiele wartości NULL. Klucza głównego używaj jako tożsamości wiersza, a UNIQUE dla innych kolumn, które nie mogą się powtarzać.

Q: Dlaczego SQLite pozwala na wiele NULL w kolumnie UNIQUE?

Bo SQL traktuje NULL jako „nieznane”, a dwie nieznane wartości nie są uznawane za równe. Dlatego kolumna UNIQUE przyjmuje dowolnie wiele wierszy z NULL: różne muszą być tylko wartości inne niż NULL. Jeśli potrzebujesz najwyżej jednego NULL, dodaj NOT NULL albo użyj częściowego indeksu unikalnego.

Q: Jak naprawić błąd 'UNIQUE constraint failed'?

Ten błąd oznacza, że INSERT albo UPDATE stworzyłby zduplikowaną wartość w kolumnie UNIQUE (albo PRIMARY KEY). Zmień wstawianą wartość, najpierw usuń istniejący wiersz albo użyj INSERT ... ON CONFLICT (upsert), żeby powiedzieć SQLite, co zrobić w razie konfliktu.

UNIQUE oznacza „duplikaty niedozwolone”

Ograniczenie UNIQUE mówi SQLite, że wartości w kolumnie (albo w grupie kolumn) nie mogą się powtarzać między wierszami. Tak wyrażasz reguły w rodzaju „dwóch użytkowników nie może mieć tego samego adresu e-mail” albo „kod produktu występuje najwyżej raz”.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada Two');

SELECT * FROM users;

Trzecie wstawienie kończy się błędem UNIQUE constraint failed: users.email. SQLite sprawdza ograniczenie przy każdym zapisie i odrzuca wszystko, co stworzyłoby duplikat. Pierwsze dwa wiersze zostają zapisane; trzeci nigdy nie trafia do tabeli.

W tle UNIQUE jest zaimplementowane jako indeks unikalny (ta sama struktura danych, której SQLite używa do szybkiego wyszukiwania), więc sprawdzenie jest tanie, a kolumna automatycznie dostaje indeks.

Składnia na poziomie kolumny i tabeli

UNIQUE możesz zapisać na dwa sposoby: bezpośrednio przy kolumnie albo jako osobną klauzulę na końcu definicji tabeli:

-- Na poziomie kolumny: prosto, jedna kolumna.
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- Na poziomie tabeli: ten sam efekt, inne miejsce.
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

Dla jednej kolumny obie formy są równoważne, wybierz tę, która czyta się lepiej. Forma na poziomie tabeli staje się niezbędna w chwili, gdy potrzebujesz unikalności obejmującej więcej niż jedną kolumnę.

Złożone UNIQUE: kilka kolumn razem

Czasem pojedyncza kolumna sama w sobie nie jest unikalna, ale kombinacja powinna być. Użytkownik może zapisać się na wiele kursów, a kurs może mieć wielu użytkowników, ale ta sama para (user_id, course_id) nie powinna wystąpić dwa razy:

CREATE TABLE enrollments (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE(user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);
INSERT INTO enrollments (user_id, course_id) VALUES (1, 101);  -- OK, inny kurs
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- OK, inny użytkownik
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- błąd: para już istnieje

SELECT * FROM enrollments;

Ograniczenie dotyczy pary, a nie żadnej z kolumn osobno. Użytkownik 1 może zapisać się na wiele kursów, kurs 100 może mieć wielu użytkowników, ale każda kombinacja może wystąpić tylko raz.

To podstawowy wzorzec dla tabel łączących w relacjach wiele do wielu.

UNIQUE vs PRIMARY KEY

Brzmią podobnie i są ze sobą powiązane, ale to nie to samo:

Tabela ma najwyżej jeden PRIMARY KEY. Może mieć wiele ograniczeń UNIQUE.
PRIMARY KEY to tożsamość wiersza: to na niego wskazują klucze obce i to jego aliasem jest rowid.
UNIQUE oznacza tylko „ta wartość (albo kombinacja) się nie powtarza”.
W zwykłej tabeli kolumna UNIQUE może zawierać wartości NULL; PRIMARY KEY nie może (z jednym historycznym wyjątkiem, który pominiemy).

Typowy układ:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- wewnętrzna tożsamość, nigdy się nie zmienia
    email    TEXT NOT NULL UNIQUE,  -- identyfikator widoczny dla użytkownika, musi być unikalny
    username TEXT NOT NULL UNIQUE   -- kolejny unikalny identyfikator
);

Do id odwołuje się reszta bazy danych. email i username są unikalne, bo wymaga tego aplikacja, a nie dlatego, że stanowią tożsamość. Jeśli użytkownik zmieni adres e-mail, id pozostaje takie samo: właśnie po to się je rozdziela.

Osobliwość z NULL

Na tym za pierwszym razem potyka się prawie każdy. Kolumna UNIQUE w SQLite przyjmuje dowolnie wiele wartości NULL:

CREATE TABLE invites (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES ('ada@example.com');
INSERT INTO invites (email) VALUES ('ada@example.com');  -- to się nie uda

SELECT * FROM invites;

Trzy wartości NULL, żaden problem. Dwa razy 'ada@example.com', i jest konflikt.

Powód: SQL traktuje NULL jako „nieznane”, a dwie nieznane wartości nie są uznawane za równe, więc sprawdzenie unikalności nie może uznać ich za duplikaty. Jeśli potrzebujesz najwyżej jednego NULL, najczystszym rozwiązaniem jest NOT NULL UNIQUE. Jeśli NULL jest dozwolony, ale tylko jeden na kombinację wartości innych kolumn, sięgnij po indeks częściowy (omawiamy go później w rozdziale o indeksach).

Obsługa konfliktów: ON CONFLICT

Domyślnie naruszenie UNIQUE przerywa instrukcję. Czasem jednak chcesz innego zachowania: zastąpić istniejący wiersz, zignorować nowy albo zaktualizować konkretne kolumny. SQLite daje dwa sposoby, żeby o to poprosić.

Pierwszy jest wbudowany w ograniczenie przez ON CONFLICT:

CREATE TABLE settings (
    key   TEXT UNIQUE ON CONFLICT REPLACE,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');
INSERT INTO settings (key, value) VALUES ('theme', 'dark');   -- zastępuje pierwszy
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

Przy drugim wstawieniu theme istniejący wiersz zostaje usunięty, a nowy zajmuje jego miejsce. Inne opcje to IGNORE (po cichu pomiń), ABORT (domyślna), FAIL i ROLLBACK.

Drugi sposób działa na poziomie instrukcji, ze składnią upsert. Zwykle jest bardziej elastyczny, bo pozwala zaktualizować konkretne kolumny:

CREATE TABLE counters (
    name  TEXT UNIQUE,
    count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, count) VALUES ('hits', 1);
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;

SELECT * FROM counters;

Pierwsze wstawienie tworzy wiersz. Dwa kolejne trafiają na ograniczenie UNIQUE i przechodzą do gałęzi DO UPDATE, zwiększając count. To wzorzec upsert INSERT ... ON CONFLICT, któremu później poświęcamy osobną stronę.

Ograniczenie UNIQUE vs indeks UNIQUE

CREATE UNIQUE INDEX robi to samo co ograniczenie UNIQUE. Właściwie ograniczenie UNIQUE tworzy w tle indeks unikalny: to niemal ten sam mechanizm w innym przebraniu.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_users_email ON users(email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');  -- błąd

Kiedy co wybrać:

Ograniczenie, gdy unikalność jest częścią definicji tabeli. Jest udokumentowana tuż obok kolumn.
Indeks unikalny, gdy chcesz indeks częściowy (klauzula WHERE), potrzebujesz konkretnej nazwy albo chcesz dodać go do istniejącej tabeli bez jej przepisywania. ALTER TABLE w SQLite nie potrafi dodać ograniczenia, ale indeks zawsze można dodać.

Zachowanie przy zapisach jest identyczne. Wybór dotyczy głównie tego, gdzie w schemacie ma się znajdować reguła.

Dodawanie UNIQUE do istniejącej tabeli

ALTER TABLE w SQLite jest celowo ograniczone: nie ma ALTER TABLE ... ADD CONSTRAINT. Są dwie praktyczne opcje:

-- Opcja 1: dodaj indeks unikalny. Działa na istniejących tabelach, wymusza to samo.
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (email) VALUES ('ada@example.com');
INSERT INTO customers (email) VALUES ('boris@example.com');

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (email) VALUES ('ada@example.com');  -- teraz błąd

Opcja 2, gdy naprawdę chcesz mieć klauzulę UNIQUE wpisaną w definicję tabeli, to przepisanie tabeli: utwórz nową tabelę z ograniczeniem, skopiuj dane, usuń starą i zmień nazwę nowej. Omawiamy to na następnej stronie.

Uwaga: jeśli dodajesz unikalność do kolumny, która już ma duplikaty, CREATE UNIQUE INDEX się nie powiedzie. Najpierw usuń zduplikowane wiersze, a potem dodaj indeks.

Gdy UNIQUE zawodzi: czytanie błędu

Komunikat błędu mówi dokładnie, które ograniczenie zostało naruszone:

Error: UNIQUE constraint failed: users.email
Error: UNIQUE constraint failed: enrollments.user_id, enrollments.course_id

Pierwsza forma to ograniczenie jednej kolumny users.email. Druga to ograniczenie złożone: wymienione są obie kolumny, bo już istnieje taka kombinacja. Gdy to zobaczysz:

Ustal, który wiersz ma już kolidującą wartość (SELECT ... WHERE email = '...').
Zdecyduj, czy chcesz zaktualizować ten wiersz, pominąć wstawienie, czy użyć innej wartości.
Jeśli duplikaty są spodziewane i chcesz je scalać, przejdź na INSERT ... ON CONFLICT DO UPDATE.

Błąd jest głośny, bo zazwyczaj naprawdę chcesz o tym wiedzieć: ciche duplikaty byłyby gorsze niż nieudany zapis.

Dalej: usuwanie i modyfikowanie tabel

Ograniczeń UNIQUE nie da się dodać do istniejącej tabeli prostym ALTER TABLE. Z powodu tego ograniczenia SQLite ma specjalny sposób na zmiany schematu, czyli przepisanie tabeli. To temat następnej strony, razem z podstawami czystego usuwania tabel.

Najczęściej zadawane pytania

Jak dodać ograniczenie UNIQUE w SQLite?

Dopisz UNIQUE do definicji kolumny (email TEXT UNIQUE) albo użyj klauzuli UNIQUE(col1, col2) na poziomie tabeli, gdy unikalność dotyczy wielu kolumn. SQLite wymusza ją, tworząc w tle indeks unikalny i odrzucając każdy INSERT lub UPDATE, który stworzyłby duplikat.

Czym różni się UNIQUE od PRIMARY KEY w SQLite?

Tabela może mieć tylko jeden PRIMARY KEY, ale wiele ograniczeń UNIQUE. PRIMARY KEY oznacza też NOT NULL (w tabelach STRICT i dla INTEGER PRIMARY KEY), a kolumny UNIQUE mogą zawierać wiele wartości NULL. Klucza głównego używaj jako tożsamości wiersza, a UNIQUE dla innych kolumn, które nie mogą się powtarzać.

Dlaczego SQLite pozwala na wiele NULL w kolumnie UNIQUE?

Bo SQL traktuje NULL jako „nieznane”, a dwie nieznane wartości nie są uznawane za równe. Dlatego kolumna UNIQUE przyjmuje dowolnie wiele wierszy z NULL: różne muszą być tylko wartości inne niż NULL. Jeśli potrzebujesz najwyżej jednego NULL, dodaj NOT NULL albo użyj częściowego indeksu unikalnego.

Jak naprawić błąd 'UNIQUE constraint failed'?

Ten błąd oznacza, że INSERT albo UPDATE stworzyłby zduplikowaną wartość w kolumnie UNIQUE (albo PRIMARY KEY). Zmień wstawianą wartość, najpierw usuń istniejący wiersz albo użyj INSERT ... ON CONFLICT (upsert), żeby powiedzieć SQLite, co zrobić w razie konfliktu.

Powiązane zagadnienia