How to correctly do CREATE INDEX with SQLite

Question

I'm trying to convert my MySQL create table statements to SQLite create table statements. Most of it I've done, however I don't know how to change MySQL's UNIQUE INDEX to Sqlites CREATE INDEX (I thought that these were roughly the same, please correct me if I'm wrong).

So I have the following MySQL table (it's changed a bit from the :

-- ----------------------------------------------------- -- Table `pyMS`.`feature` -- ----------------------------------------------------- CREATE  TABLE IF NOT EXISTS `pyMS`.`feature` (   `feature_id` VARCHAR(40) NOT NULL ,   `intensity` DOUBLE NOT NULL ,   `overallquality` DOUBLE NOT NULL ,   `quality` DOUBLE NOT NULL ,   `charge` INT NOT NULL ,   `content` VARCHAR(45) NOT NULL ,   `msrun_msrun_id` INT NOT NULL ,   PRIMARY KEY (`feature_id`, `msrun_msrun_id`) ,   UNIQUE INDEX `id_UNIQUE` (`feature_id` ASC) ,   INDEX `fk_feature_msrun1` (`msrun_msrun_id` ASC) ,   CONSTRAINT `fk_feature_msrun1`     FOREIGN KEY (`msrun_msrun_id` )     REFERENCES `pyMS`.`msrun` (`msrun_id` )     ON DELETE NO ACTION     ON UPDATE NO ACTION) ENGINE = InnoDB; 

And I changed the index according to http://www.sqlite.org/lang_createindex.html. I did also change some other things to go from MySQL to SQLite but I tested it and they work. So this is my SQLite code:

-- ----------------------------------------------------- -- Table `feature` -- ----------------------------------------------------- CREATE  TABLE IF NOT EXISTS `feature` (   `feature_id` VARCHAR(40) NOT NULL ,   `intensity` DOUBLE NOT NULL ,   `overallquality` DOUBLE NOT NULL ,   `quality` DOUBLE NOT NULL ,   `charge` INT NOT NULL ,   `content` VARCHAR(45) NOT NULL ,   `msrun_msrun_id` INT NOT NULL ,   CREATE UNIQUE INDEX `id_UNIQUE` ON `feature` (`feature_id` ASC) ,   CREATE INDEX `fk_feature_msrun1` ON `msrun` (`msrun_msrun_id` ASC) ,   CONSTRAINT `fk_feature_msrun1`     FOREIGN KEY (`msrun_msrun_id` )     REFERENCES `msrun` (`msrun_id` )     ON DELETE NO ACTION     ON UPDATE NO ACTION); 

This does not work. When I remove the INDEX lines it does work. As far as I can see the INDEX lines comply to this description http://www.sqlite.org/lang_createindex.html, I don't see where it goes wrong. So how can I change the two lines

CREATE UNIQUE INDEX `id_UNIQUE` ON `feature` (`feature_id` ASC) , CREATE INDEX `fk_feature_msrun1` ON `msrun` (`msrun_msrun_id` ASC) , 

to make their syntax correct?

Answer 1

CREATE UNIQUE INDEX is its own statement and cannot be used within a CREATE TABLE statement.

Move the index statements out of CREATE TABLE:

CREATE  TABLE IF NOT EXISTS `feature` (   `feature_id` VARCHAR(40) NOT NULL ,   `intensity` DOUBLE NOT NULL ,   `overallquality` DOUBLE NOT NULL ,   `quality` DOUBLE NOT NULL ,   `charge` INT NOT NULL ,   `content` VARCHAR(45) NOT NULL ,   `msrun_msrun_id` INT NOT NULL,   CONSTRAINT `fk_feature_msrun1`     FOREIGN KEY (`msrun_msrun_id` )     REFERENCES `msrun` (`msrun_id` )     ON DELETE NO ACTION     ON UPDATE NO ACTION); CREATE UNIQUE INDEX `id_UNIQUE` ON `feature` (`feature_id` ASC); CREATE INDEX `fk_feature_msrun1` ON `feature` (`msrun_msrun_id` ASC);