CODEWITHSUNDEEP
sqlite
Questionable
Questionable

Reputation: 878

SQLite3 Virtual Tables and Not Null

I appear to be having a problem with Virtual Tables in SQLite3.

Create Tables:

CREATE TABLE Test1 (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Value TEXT);
CREATE VIRTUAL TABLE Test2 USING fts4 (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Value TEXT);

As you can see they are essentially identical except one is Virtual for FTS4 support.

INSERT INTO Test1 (Value) values("test");
INSERT INTO Test2 (Value) values("test");

Results for Test1:

id          Value

---------- ----------

1           test

Results for Test2:

id          Value

---------- ----------

            test

Is there a way to get SQLite3 to force the NOT NULL characteristic for Virtual Tables?

Thanks!

Upvotes: 1

Views: 612

Answers (2)

CL.
CL.

Reputation: 180020

Virtual tables are not implemented by SQLite itself but by some separate piece of code.

The FTS module does not support data types or constraints; its virtual tables have only plain text columns, and all columns (including your id column) are indexed for full-text searches.

As shown in the documentation, FTS tables have an implicit INTEGER PRIMARY KEY column which can be accessed as docid or rowid.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

I believe you can use the rowid column, which is available in both standard and virtual SQLite tables, to achieve what you want.

CREATE VIRTUAL TABLE Test2 USING fts4 (Value TEXT);

When you want to insert a new record, just pass null as the value of the rowid:

INSERT INTO Test2(rowid, Value) VALUES(null, 'hello');

The default behavior in SQLite for assigning the rowid when the insert value is null is to increment the previous largest value by 1. This will behave similarly to what you had in mind when using AUTOINCREMENT.

To obtain the rowid in a query on your table, you can try something like this:

SELECT rowid, * FROM Test2;

Here is a link to the SQLite documentation which discusses the behavior of null with rowid. And here is a link which mentions that FTS tables do have a rowid column like regular tables.

Upvotes: 1

Related Questions