Long value data type cannot be indexed

Nov 2, 2011 at 9:23 PM

Hi, I tried using the tools on my sqlite DB (from an android app) and I hit this error:

SQLite2CE Error: Could not create table: cards
Create table command: CREATE TABLE [cards] ([cardid] integer IDENTITY NOT NULL, [name] ntext UNIQUE NOT NULL, [manacost] ntext NOT NULL, [type] ntext NOT NULL, [subtype] ntext NULL, [cmc] integer NULL, [power] integer NULL, [toughness] integer NULL, [color] integer NULL, [cardtext] ntext NULL,  PRIMARY KEY (cardid ))
Reason: Long value data type cannot be indexed.

------------ SQLite Database Schema ----------------------------------

CREATE TABLE cards (cardid INTEGER primary key autoincrement, name TEXT not null unique, manacost TEXT not null, type TEXT not null, subtype TEXT, cmc INTEGER, power INTEGER, toughness INTEGER, color INTEGER, cardtext TEXT)

I tried modifying settings.xml from:

    <integerTypeName>bigint</integerTypeName>

to:

    <integerTypeName>integer</integerTypeName>

but that didn't fix the problem.  What am I missing?  I can't immagine sqlce really can't index an int column, but I don't have a lot of DBA type experience.  I can pretty easily modify the sqlite DB before importing, but I don't want to poke with a stick if I can find out exactly what the problem is.

Thanks!

Charles.

Nov 4, 2011 at 12:25 AM

The problem would be with  the "[name] ntext UNIQUE NOT NULL" column. MS SQL (server and CE) does not support creating an index on the ntext datatype. You will need to modify your SQLLite schema and make that key non primary. The tool should work after that.

I will add this to the release notes.

Thanks for reporting it!

Nov 4, 2011 at 8:40 PM

Thanks for the quick response!

So it's the UNIQUE constraint on the name ntext column that is the issue.  I can probably enforce the unique when populating the DB, but it'd be unfortunate for it not to be indexed for performance. Can any SQLCE text fields be indexed to speed up direct queries on that column?

Nov 15, 2011 at 11:32 PM

@langdroid, have you tried nvarchar or varchar? The length needs to be less than MAX since anything more than MAX is mapped to ntext. I believe such columns can be indexed.