Почему SQLite вставляет дубликаты составных первичных ключей?
Код:
import sqlite3
c = sqlite3.Connection(':memory:')
c.execute('CREATE TABLE foo(a INTEGER, b VARCHAR(8), PRIMARY KEY(a, b))')
c.execute('INSERT INTO foo(a) VALUES (1)')
c.execute('INSERT INTO foo(a) VALUES (1)')
print(c.execute('SELECT * FROM foo').fetchall())
Выход:
[(1, None), (1, None)]
Почему SQLite вставляет строки с повторяющимися первичными ключами? Как это исправить?
2 ответа
SQL PK (PRIMARY KEY) означает, что UNIQUE NOT NULL. Вы не должны ожидать, что сможете иметь значение NULL для PK, не говоря уже только об одном. Вы должны объявить PK-столбцы NOT NULL и не указывать в них NULL.
Каждая строка в таблице с первичным ключом должна иметь уникальную комбинацию значений в столбцах первичного ключа. В целях определения уникальности значений первичного ключа значения NULL считаются отличными от всех других значений, включая другие значения NULL. Если оператор INSERT или UPDATE пытается изменить содержимое таблицы, чтобы две или более строки имели одинаковые значения первичного ключа, это является нарушением ограничения.
Согласно стандарту SQL, PRIMARY KEY всегда должен подразумевать NOT NULL. К сожалению, из-за ошибки в некоторых ранних версиях, это не так в SQLite. Если столбец не является INTEGER PRIMARY KEY или таблица не является таблицей БЕЗ ROWID или столбец объявлен как NOT NULL, SQLite допускает значения NULL в столбце PRIMARY KEY. SQLite может быть исправлен в соответствии со стандартом, но это может привести к поломке устаревших приложений. Следовательно, было решено просто задокументировать тот факт, что SQLite допускает значения NULL в большинстве столбцов PRIMARY KEY.
Поскольку NULL в PK противоречит SQL, кажется, что SQLite решает, когда ограничивает и манипулирует таблицами с NULL в PK. Но он использует обычную интерпретацию SQL, что NULL не равен NULL для целей UNIQUE. Это похоже на то, когда вы объявляете набор столбцов UNIQUE NULL. Таким образом, в качестве ограничения, SQLite PK является синонимом UNIQUE вместо UNIQUE NOT NULL.
Ограничение UNIQUE аналогично ограничению PRIMARY KEY, за исключением того, что в одной таблице может быть любое количество ограничений UNIQUE. Для каждого ограничения UNIQUE в таблице каждая строка должна содержать уникальную комбинацию значений в столбцах, определенных ограничением UNIQUE. В целях ограничений UNIQUE значения NULL считаются отличными от всех других значений, включая другие значения NULL.
SQLite, как и многие другие базы данных SQL, учитывает два NULLкак различные значения для уникальности (частично потому, что в SQL NULL == NULL ложно).
Я не верю, что есть способ изменить это поведение. В качестве обходного пути вы можете использовать пустую строку в столбце b как "нет значения".