jeudi 1 octobre 2015

sqlite rtree : constraint failed (works with usual table)

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?

Aucun commentaire:

Enregistrer un commentaire