MySQL 如何使用 SQLite 正确执行 CREATE INDEX

How to correctly do CREATE INDEX with SQLite

提问by Niek de Klein

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).

我正在尝试将我的 MySQL 创建表语句转换为 SQLite 创建表语句。我已经完成了大部分工作，但是我不知道如何将 MySQL 的 UNIQUE INDEX 更改为 Sqlites CREATE INDEX（我认为这些大致相同，如果我错了，请纠正我）。

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

所以我有以下 MySQL 表（它与以下内容有所不同：

-- -----------------------------------------------------
-- 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:

我根据http://www.sqlite.org/lang_createindex.html更改了索引。我确实也改变了一些其他的东西，从 MySQL 到 SQLite，但我测试了它并且它们工作。所以这是我的 SQLite 代码：

-- -----------------------------------------------------
-- 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

这不起作用。当我删除 INDEX 行时，它确实有效。据我所见，INDEX 行符合此描述http://www.sqlite.org/lang_createindex.html，我看不出哪里出错了。那么我怎样才能改变这两行

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?

使他们的语法正确？

回答by Justin ??????

CREATE UNIQUE INDEXis its own statement and cannot be used within a CREATE TABLEstatement.

CREATE UNIQUE INDEX是它自己的语句，不能在CREATE TABLE语句中使用。

Move the index statements out of CREATE TABLE:

将索引语句移出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);

回答by Mike Sherrill 'Cat Recall'

You need to look at CREATE TABLEsyntax, not CREATE INDEX. SQLite will accept this form of a UNIQUE constraint in a CREATE TABLE statement.

您需要查看CREATE TABLE语法，而不是 CREATE INDEX。SQLite 将在 CREATE TABLE 语句中接受这种形式的 UNIQUE 约束。

CREATE  TABLE IF NOT EXISTS `feature` (
...
  CONSTRAINT `id_UNIQUE` UNIQUE (`feature_id`),
...
);