Блог

Насколько я понимаю, тип данных sqlite связан не с его столбцом, а с самими данными: это практически означает, что вы можете вставлять любые данные в любой столбец.

Возможно ли запретить такое поведение? Я имею в виду, я хочу, чтобы sqlite выдавал ошибку (или, по крайней мере, предупреждение), когда я случайно пытаюсь вставить текст в целое число, например.

Вы можете использовать ограничения ПРОВЕРКИ и typeof() для проверки фактического типа данных:

 CREATE TABLE MyTable (
    Col1 INTEGER  CHECK (typeof(Col1) = 'integer'),
    Col2 TEXT     CHECK (typeof(Col2) IN ('text', 'null')
);
  

Я полагаю, вы могли бы использовать CHECK ограничение, например

 CREATE TABLE testinsert (
    COL1 INTEGER, 
    COL2 INTEGER CHECK (CAST(COL2||1 AS INTEGER) <> 0) -- column constraint
    CHECK (CAST(COL1||1 AS INTEGER) <> 0) -- table constraint
);
  

• это создает таблицу с двумя столбцами COL1 и COL2.

• оба столбца имеют одинаковое ограничение, но применяются к столбцам по-разному

  • В COL1 ограничение ПРОВЕРКИ применено как ограничение таблицы
  • В COL2 ограничение ПРОВЕРКИ применяется как ограничение столбца

• Ограничение работает путем проверки значения столбца при ПРИВЕДЕНИИ к ЦЕЛОМУ числу. Результатом будет 0 (false), если значение не является целым числом. Однако, чтобы использовать 0 в качестве допустимого значения, 1 объединяется со значением столбца. Таким образом, 0 приведет к 1.

Вы также могли бы использовать TYPEOF функцию, например

 COL3 INTEGER CHECK(typeof(COL3) = 'INTEGER')
  

• Отмечая, что существует тонкая разница между использованием ПРИВЕДЕНИЯ и TYPEOF. Предыдущие примеры, использующие ПРИВЕДЕНИЕ, допускают использование целых чисел в виде строк, в то время как использование функции typeof допускает только целые числа.

Ниже приведены некоторые примеры ВСТАВОК с результатом :-

 INSERT INTO testinsert VALUES(100,100); -- OK
INSERT INTO testinsert VALUES('100',100); -- OK
INSERT INTO testinsert VALUES('100','abc'); -- ouch for COL2 (abc)
INSERT INTO testinsert VALUES(0,100); -- OK
INSERT INTO testinsert VALUES('0',100); -- OK
INSERT INTO testinsert VALUES('abc',100); -- ouch  for COL1 (abc)
  

С добавлением COL3 в таблицу :-

 INSERT INTO testinsert VALUES(100,100,100); -- OK (all integers)
INSERT INTO testinsert VALUES('100','100',100); -- OK (CAST can accept integers as strings)
INSERT INTO testinsert VALUES('100','100','100'); -- Ouch typeof will consider '100' as a string, not an integer
  

Если вы хотите игнорировать, а не прерывать (по умолчанию), вы могли бы использовать :-

 INSERT OR IGNORE INTO testinsert VALUES('100','abc'); -- insert Skipped - no fail
  

Для тестирования вышеуказанного использовался SQLite Manager.

Вот пример сбоя КОНФЛИКТА :-

 SQLiteManager:  -- OK
INSERT INTO testinsert VALUES('100','abc'); [ CHECK constraint failed: testinsert ]
Exception Name: NS_ERROR_STORAGE_CONSTRAINT
Exception Message: Component returned failure code: 0x80630003 (NS_ERROR_STORAGE_CONSTRAINT) [mozIStorageStatement.execute]
  

Возможно, вы захотите взглянуть на :-

• СОЗДАТЬ ТАБЛИЦУ
• таблица-ограничение
• ограничение столбца
• В предложении CONFLICT
• выражения — ПРИВЕДЕНИЕ выражений (внизу)
• typeof(X)

Начиная с версии 3.37.0 SQLite, можно принудительно проверять типы данных с помощью СТРОГИХ таблиц:

В инструкции CREATE TABLE, если ключевое слово «STRICT» table-option добавляется в конец после закрывающего «)», то к этой таблице применяются строгие правила ввода.

Вы можете использовать один из следующих типов данных для определений столбцов:

• INT
• ЦЕЛОЕ число
• реальный
• ТЕКСТ
• Большой двоичный объект
• Любой

Смотрите упрощенную демонстрацию.