OperationalError creating an index in sqlite

Question

EDIT: TL;DR version

I typed this

CREATE INDEX IF NOT EXISTS IDX_FILE_SIZE table_name (file_size); 

instead of this

CREATE INDEX IF NOT EXISTS IDX_FILE_SIZE ON table_name (file_size); 

Don't do that.

Answer 1

Some silly questions:

Is it a concidence that the offending statement is missing the word ON?

CREATE INDEX IF NOT EXISTS IDX_FILE_FULLPATH_FILE_PARENT_DIR ON table_name (file_fullpath, file_parent_dir);
CREATE INDEX IF NOT EXISTS IDX_FILE_SIZE table_name (file_size); -- missing ON
CREATE INDEX IF NOT EXISTS IDX_TAG_TITLE ON table_name (tag_title);

Somewhere in all the verbiage in your question, did I see the phrase "syntax error"?

Did you try the simple step of running the SQL statements in the sqlite3 command interpreter and seeing which syntax error you were actually getting?

E.g.

SQLite version 3.6.14
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table foo (bar int, zot int);
sqlite> create index barx on foo(bar);
sqlite> create index zotx    foo(zot);
SQL error: near "foo": syntax error
sqlite>

Have you considered perusing TFRRD (The Fantastic Rail-Road Diagram) in the docs?

You wrote: """when I run that command in the smaller script (verifyIndexSmaller), it gives no error. If I then try to run the larger script again, even though the index has been created by the smaller script, I still get the error""".

Have you considered the possibility that you didn't run that command in the smaller script, but actually ran another (fixed!) version of that command?

Do you now understand why S.Lott was trying to get you to cut the waffle and focus on the piece of SQL that was causing the error?