如何使用SQLite正确执行CREATE INDEX [英] How to correctly do CREATE INDEX with SQLite

问题描述

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

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表格（它改变了一点：

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;

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

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

这不起作用。当我删除I​​NDEX线时，它确实有效。据我所知，INDEX行符合此描述 http://www.sqlite.org/lang_createindex.html ，我不知道它出了什么问题。那么如何更改两行

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

他们的语法是否正确？

推荐答案

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

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

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

这篇关于如何使用SQLite正确执行CREATE INDEX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！