sqlite rtree : constraint failed (works with usual table)

Question

I am trying to use Rtree and faced this strange beheaviour: an INSERT statement which works fine with a normal table fails with an rtree table:

This example works fine:

DROP TABLE IF EXISTS ltssoffsets;
CREATE  TABLE ltssoffsets(id TEXT NOT NULL, 
    offset_start INT NOT NULL,
    gene_start INT NOT NULL,
    chr TEXT NOT NULL, start INT, end INT,
    PRIMARY KEY (id, offset_start) );

INSERT INTO ltssoffsets VALUES("first", -10, 45, "chr2", 30, 40);
INSERT INTO ltssoffsets VALUES("first", -5, 45, "chr2", 30, 40);

This one fails:

DROP TABLE IF EXISTS ltssoffsets;
CREATE VIRTUAL TABLE ltssoffsets USING rtree(id TEXT NOT NULL, 
    offset_start INT NOT NULL,
    gene_start INT NOT NULL,
    chr TEXT NOT NULL, start INT, end INT,
    PRIMARY KEY (id, offset_start) );

INSERT INTO ltssoffsets VALUES("first", -10, 45, "chr2", 30, 40);
INSERT INTO ltssoffsets VALUES("first", -5, 45, "chr2", 30, 40);

->  Error: constraint failed

What can be wrong here?

Answer 1

Virtual tables do not behave like regular tables. How column types and constraints are handled depends on the virtual table implementation.

The R-tree documentation says:

The first column of an SQLite R*Tree must always be an integer primary key. The min/max-value pair columns are stored as 32-bit floating point values [...]

Your R-tree table simply has an integer primary key column id, and four coordinate columns. All your types and constraints are ignored.

An R-tree is supposed to be used as an index, not as a table.

The only information that an R*Tree index stores about an object is its integer ID and its bounding box. Additional information needs to be stored in separate tables and related to the R*Tree index using the primary key.