是否可以强制SQLite数据类型？

Question

据我了解，SQLite的数据类型与其列并不相关，而是与数据本身相关：这实际上意味着您可以将任何数据插入到任何列中。

能否禁止这种行为？我的意思是，当我意外地尝试将文本插入整数列时，我希望SQLite能够引发错误（或至少警告）。

- akai

---

听起来不像是这样... https://www.sqlite.org/faq.html#q3 - CBroe

Answer 1

你可以使用CHECK约束和typeof()函数来检查实际数据类型：

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

- CL.

Answer 2

我认为你可以使用一个 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作为表约束来应用CHECK约束
  • COL2作为列约束应用CHECK约束

• 该约束通过将列转换为INTEGER类型并检查其值来工作。如果该值不是INTEGER，则结果将为0（false）。但是，为了处理0作为有效值，将1连接到列的值中。因此，0将导致1。

您还可以使用TYPEOF 函数，例如：

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

• 注意使用CAST和TYPEOF之间的微小差异。前面的示例使用CAST，将允许提供为字符串的整数，而使用typeof函数只允许整数。

以下是一些示例INSERTS及其结果：

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被用于测试上述内容。

以下是CONFLICT失败的示例：

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]

您可能需要查看以下内容：

• CREATE TABLE（创建表）
• table-constraint（表约束）
• column-constraint（列约束）
• ON CONFLICT clause（冲突解决语句）
• expression - CAST expressions (near the bottom) （表达式 - CAST表达式（接近底部））
• typeof(X)（类型查询函数）

- MikeT

Answer 3

自 SQLite 版本 3.37.0 开始，可以使用 STRICT Tables 强制进行数据类型检查：

在 CREATE TABLE 语句中，如果在 ")" 后添加 "STRICT" 表选项关键字，则该表将应用严格的类型规则。

您可以使用以下列定义中的一种数据类型：

• INT
• INTEGER
• REAL
• TEXT
• BLOB
• ANY

请参见简化版 演示。

- forpas