SQLite Forum

index question
> create table dance (id int primary key, select int not null, name text unique)

Do you mean INTEGER PRIMARY KEY?  If so, you have misspelled INTEGER.

 > is the index going to be used? Will the value of "select" be stored in the index in addition to the table, or only in the index?

Yes, the index will be used.  Yes the value of select is stored in both the table and the index.

 > What does it mean to use a WHERE clause in the CREATE INDEX as

 > CREATE UNIQUE INDEX IF NOT EXISTS index_dn ON dance (name,select) WHERE select>0

 > Does that mean that there is no index entry if select==0?

Yes, only records with select > 0 appear in the index.  But why are you adding select to the index?  If the value is not 0 does it matter what the value is?

If select can only contain the values 0 and 1 (true and false), then why are you not requiring that?

create table dance
   id integer primary key, 
   select int not null check (select in (True, False)), 
   name text unique
create unique index dance_true on dance(name) where select is true;
create unique index dance_false on dance(name) where select is false;

And you selects would be something like:

select id, name from dance where name == 'waltz' and select is true;