OperationalError:外键错配

Question

我有两个表，我正在填充，'msrun‘和’功能‘。“功能”有一个外键，指向'msrun‘表的'msrun_name’列。插入表可以正常工作。但是，当我尝试从“feature”表中删除时，我会得到以下错误：

pysqlite2.dbapi2.OperationalError: foreign key mismatch

根据SQLite手册中的外键规则：

- The parent table does not exist, or
- The parent key columns named in the foreign key constraint do not exist, or
- The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE, or
- The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.

我看不出我违反了什么。我的创建表如下所示：

DROP TABLE IF EXISTS `msrun`;
-- -----------------------------------------------------
-- Table `msrun`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `msrun` (
  `msrun_name` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `description` VARCHAR(500) NOT NULL );

DROP TABLE IF EXISTS `feature`;
-- -----------------------------------------------------
-- Table `feature`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `feature` (
  `feature_id` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `intensity` DOUBLE NOT NULL ,
  `overallquality` DOUBLE NOT NULL ,
  `charge` INT NOT NULL ,
  `content` VARCHAR(45) NOT NULL ,
  `msrun_msrun_name` VARCHAR(40) NOT NULL ,
  CONSTRAINT `fk_feature_msrun1`
    FOREIGN KEY (`msrun_msrun_name` )
    REFERENCES `msrun` (`msrun_name` )
    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_name` ASC);

据我所知，父表存在，外键指向正确的父键，父键是主键，外键指定主键列。

产生错误的脚本：

from pysqlite2 import dbapi2 as sqlite
import parseFeatureXML


connection = sqlite.connect('example.db')
cursor = connection.cursor()    
cursor.execute("PRAGMA foreign_keys=ON")

inputValues = ('example', 'description')
cursor.execute("INSERT INTO `msrun` VALUES(?, ?)", inputValues)
featureXML = parseFeatureXML.Reader('../example_scripts/example_files/input/featureXML_example.featureXML')

for feature in featureXML.getSimpleFeatureInfo():
    inputValues = (featureXML['id'], featureXML['intensity'],
                   featureXML['overallquality'], featureXML['charge'], 
                   featureXML['content'], 'example')
    # insert the values into msrun using ? for sql injection safety
    cursor.execute("INSERT INTO `feature` VALUES(?,?,?,?,?,?)", inputValues)
connection.commit()

for feature in featureXML.getSimpleFeatureInfo():
    cursor.execute("DELETE FROM `feature` WHERE feature_id = ?", (str(featureXML['id']),))    

编辑：

这些表具有链接到功能的外键。它们尚未填补：

DROP TABLE IF EXISTS `convexhull`;
-- -----------------------------------------------------
-- Table `convexhull`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `convexhull` (
  `convexhull_id` INT PRIMARY KEY NOT NULL ,
  `mz` DOUBLE NOT NULL ,
  `rt` DOUBLE NOT NULL ,
  `feature_feature_id` VARCHAR(40) NOT NULL ,
  CONSTRAINT `fk_convexhull_feature`
    FOREIGN KEY (`feature_feature_id` )
    REFERENCES `feature` (`feature_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_convexhull_feature` ON `convexhull` (`feature_feature_id` ASC);

DROP TABLE IF EXISTS `position`;
-- -----------------------------------------------------
-- Table `position`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `position` (
  `position_id` INT PRIMARY KEY NOT NULL ,
  `dim0` INT NOT NULL ,
  `dim1` INT NOT NULL ,
  `feature_feature_id` VARCHAR(40) NOT NULL ,
  CONSTRAINT `fk_position_feature1`
    FOREIGN KEY (`feature_feature_id` )
    REFERENCES `feature` (`feature_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_position_feature1` ON `position` (`feature_feature_id` ASC);

DROP TABLE IF EXISTS `userParam_names`;
-- -----------------------------------------------------
-- Table `userParam_names`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `userParam_names` (
  `userParam_id` INT PRIMARY KEY NOT NULL ,
  `Name` VARCHAR(45) NOT NULL );

DROP TABLE IF EXISTS `feature_has_userParam_names`;
-- -----------------------------------------------------
-- Table IF EXISTS `feature_has_userParam_names`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `feature_has_userParam_names` (
  `feature_feature_id` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `userParam_names_userParam_id` INT NOT NULL ,
  CONSTRAINT `fk_feature_has_userParam_names_feature1`
    FOREIGN KEY (`feature_feature_id` )
    REFERENCES `feature` (`feature_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_feature_has_userParam_names_userParam_names1`
    FOREIGN KEY (`userParam_names_userParam_id` )
    REFERENCES `userParam_names` (`userParam_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_feature_has_userParam_names_userParam_names1` ON `feature_has_userParam_names` (`userParam_names_userParam_id` ASC);
  CREATE INDEX `fk_feature_has_userParam_names_feature1` ON `feature_has_userParam_names` (`feature_feature_id` ASC);

DROP TABLE IF EXISTS `userParam_value`;
-- -----------------------------------------------------
-- Table IF EXISTS `userParam_value`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `userParam_value` (
  `iduserParam_value` INT PRIMARY KEY NOT NULL ,
  `userParam_name` VARCHAR(45) NOT NULL ,
  `value` VARCHAR(45) NOT NULL );

DROP TABLE IF EXISTS `feature_has_userParam_names_has_userParam_value`;
-- -----------------------------------------------------
-- Table `feature_has_userParam_names_has_userParam_value`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `feature_has_userParam_names_has_userParam_value` (
  `feature_has_userParam_names_feature_feature_id` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `feature_has_userParam_names_userParam_names_userParam_id` INT NOT NULL ,
  `userParam_value_iduserParam_value` INT NOT NULL ,
  CONSTRAINT `fk_feature_has_userParam_names_has_userParam_value_feature_ha1`
    FOREIGN KEY (`feature_has_userParam_names_feature_feature_id` , `feature_has_userParam_names_userParam_names_userParam_id` )
    REFERENCES `feature_has_userParam_names` (`feature_feature_id` , `userParam_names_userParam_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_feature_has_userParam_names_has_userParam_value_userParam_1`
    FOREIGN KEY (`userParam_value_iduserParam_value` )
    REFERENCES `userParam_value` (`iduserParam_value` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_feature_has_userParam_names_has_userParam_value_userParam_1` ON  `feature_has_userParam_names_has_userParam_value` (`userParam_value_iduserParam_value` ASC);
  CREATE INDEX `fk_feature_has_userParam_names_has_userParam_value_feature_ha1` ON  `feature_has_userParam_names_has_userParam_value` (`feature_has_userParam_names_feature_feature_id` ASC, `feature_has_userParam_names_userParam_names_userParam_id` ASC);

当我从SQLite管理器执行该语句时，delete语句确实工作。

编辑2：

完整回溯：

Traceback (most recent call last):
  File "/homes/ndeklein/workspace/MS/Trunk/PyMS_dev/database/test.py", line 25, in <module>
    cursor.execute("DELETE FROM `feature` WHERE feature_id = 'f_13020522388175237334'")
pysqlite2.dbapi2.OperationalError: foreign key mismatch

正在做什么

DELETE FROM `feature` WHERE feature_id = 'f_13020522388175237334'

在SQLite管理器中工作。

编辑3

包括所有表格：

--------------------------------------------------------
-- pyMS database. Drops all tables before it makes them, should be changed before release
--------------------------------------------------------


DROP TABLE IF EXISTS `msrun`;
-- -----------------------------------------------------
-- Table `msrun`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `msrun` (
  `msrun_name` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `description` VARCHAR(500) NOT NULL );


DROP TABLE IF EXISTS `feature`;
-- -----------------------------------------------------
-- Table `feature`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `feature` (
  `feature_id` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `intensity` DOUBLE NOT NULL ,
  `overallquality` DOUBLE NOT NULL ,
  `charge` INT NOT NULL ,
  `content` VARCHAR(45) NOT NULL ,
  `msrun_msrun_name` VARCHAR(40) NOT NULL ,
  CONSTRAINT `fk_feature_msrun1`
    FOREIGN KEY (`msrun_msrun_name` )
    REFERENCES `msrun` (`msrun_name` )
    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_name` ASC);


DROP TABLE IF EXISTS `convexhull`;
-- -----------------------------------------------------
-- Table `convexhull`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `convexhull` (
  `convexhull_id` INT PRIMARY KEY NOT NULL ,
  `mz` DOUBLE NOT NULL ,
  `rt` DOUBLE NOT NULL ,
  `feature_feature_id` VARCHAR(40) NOT NULL ,
  CONSTRAINT `fk_convexhull_feature`
    FOREIGN KEY (`feature_feature_id` )
    REFERENCES `feature` (`feature_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_convexhull_feature` ON `convexhull` (`feature_feature_id` ASC);


DROP TABLE IF EXISTS `position`;
-- -----------------------------------------------------
-- Table `position`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `position` (
  `position_id` INT PRIMARY KEY NOT NULL ,
  `dim0` INT NOT NULL ,
  `dim1` INT NOT NULL ,
  `feature_feature_id` VARCHAR(40) NOT NULL ,
  CONSTRAINT `fk_position_feature1`
    FOREIGN KEY (`feature_feature_id` )
    REFERENCES `feature` (`feature_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_position_feature1` ON `position` (`feature_feature_id` ASC);


DROP TABLE IF EXISTS `userParam_names`;
-- -----------------------------------------------------
-- Table `userParam_names`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `userParam_names` (
  `userParam_id` INT PRIMARY KEY NOT NULL ,
  `Name` VARCHAR(45) NOT NULL );


DROP TABLE IF EXISTS `feature_has_userParam_names`;
-- -----------------------------------------------------
-- Table IF EXISTS `feature_has_userParam_names`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `feature_has_userParam_names` (
  `feature_feature_id` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `userParam_names_userParam_id` INT NOT NULL ,
  CONSTRAINT `fk_feature_has_userParam_names_feature1`
    FOREIGN KEY (`feature_feature_id` )
    REFERENCES `feature` (`feature_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_feature_has_userParam_names_userParam_names1`
    FOREIGN KEY (`userParam_names_userParam_id` )
    REFERENCES `userParam_names` (`userParam_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_feature_has_userParam_names_userParam_names1` ON `feature_has_userParam_names` (`userParam_names_userParam_id` ASC);
  CREATE INDEX `fk_feature_has_userParam_names_feature1` ON `feature_has_userParam_names` (`feature_feature_id` ASC);


DROP TABLE IF EXISTS `userParam_value`;
-- -----------------------------------------------------
-- Table IF EXISTS `userParam_value`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `userParam_value` (
  `iduserParam_value` INT PRIMARY KEY NOT NULL ,
  `userParam_name` VARCHAR(45) NOT NULL ,
  `value` VARCHAR(45) NOT NULL );


DROP TABLE IF EXISTS `feature_has_userParam_names_has_userParam_value`;
-- -----------------------------------------------------
-- Table `feature_has_userParam_names_has_userParam_value`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `feature_has_userParam_names_has_userParam_value` (
  `feature_has_userParam_names_feature_feature_id` VARCHAR(40) PRIMARY KEY NOT NULL ,
  `feature_has_userParam_names_userParam_names_userParam_id` INT NOT NULL ,
  `userParam_value_iduserParam_value` INT NOT NULL ,
  CONSTRAINT `fk_feature_has_userParam_names_has_userParam_value_feature_ha1`
    FOREIGN KEY (`feature_has_userParam_names_feature_feature_id` , `feature_has_userParam_names_userParam_names_userParam_id` )
    REFERENCES `feature_has_userParam_names` (`feature_feature_id` , `userParam_names_userParam_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_feature_has_userParam_names_has_userParam_value_userParam_1`
    FOREIGN KEY (`userParam_value_iduserParam_value` )
    REFERENCES `userParam_value` (`iduserParam_value` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_feature_has_userParam_names_has_userParam_value_userParam_1` ON  `feature_has_userParam_names_has_userParam_value` (`userParam_value_iduserParam_value` ASC);
  CREATE INDEX `fk_feature_has_userParam_names_has_userParam_value_feature_ha1` ON  `feature_has_userParam_names_has_userParam_value` (`feature_has_userParam_names_feature_feature_id` ASC, `feature_has_userParam_names_userParam_names_userParam_id` ASC);


DROP TABLE IF EXISTS `precursor`;
-- -----------------------------------------------------
-- Table `precursor`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `precursor` (
  `precursor_id` INT PRIMARY KEY NOT NULL ,
  `ion_mz` DOUBLE NOT NULL ,
  `charge_state` INT NOT NULL ,
  `peak_intensity` DOUBLE NOT NULL );


DROP TABLE IF EXISTS `spectrum`;
-- -----------------------------------------------------
-- Table `spectrum`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `spectrum` (
  `spectrum_index` INT PRIMARY KEY NOT NULL ,
  `ms_level` INT NOT NULL ,
  `base_peak_mz` DOUBLE NOT NULL ,
  `base_peak_intensity` DOUBLE NOT NULL ,
  `total_ion_current` DOUBLE NOT NULL ,
  `lowest_observes_mz` DOUBLE NOT NULL ,
  `highest_observed_mz` DOUBLE NOT NULL ,
  `scan_start_time` DOUBLE NOT NULL ,
  `ion_injection_time` DOUBLE NOT NULL ,
  `msrun_msrun_name` VARCHAR(40) NOT NULL ,
  `precursor_precursor_id` INT NOT NULL ,
  CONSTRAINT `fk_spectrum_msrun1`
    FOREIGN KEY (`msrun_msrun_name` )
    REFERENCES `msrun` (`msrun_name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_spectrum_precursor1`
    FOREIGN KEY (`precursor_precursor_id` )
    REFERENCES `precursor` (`precursor_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_spectrum_msrun1` ON `spectrum` (`msrun_msrun_name` ASC);
  CREATE INDEX `fk_spectrum_precursor1` ON `spectrum` (`precursor_precursor_id` ASC);


DROP TABLE IF EXISTS `spectrum_has_feature`;
-- -----------------------------------------------------
-- Table `spectrum_has_feature`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `spectrum_has_feature` (
  `spectrum_spectrum_index` INT PRIMARY KEY NOT NULL ,
  `spectrum_msrun_msrun_name` VARCHAR(40) NOT NULL ,
  `spectrum_precursor_precursor_id` INT NOT NULL ,
  `feature_feature_id` VARCHAR(40) NOT NULL ,
  `feature_msrun_msrun_name` VARCHAR(40) NOT NULL ,
  CONSTRAINT `fk_spectrum_has_feature_spectrum1`
    FOREIGN KEY (`spectrum_spectrum_index` , `spectrum_msrun_msrun_name` , `spectrum_precursor_precursor_id` )
    REFERENCES `spectrum` (`spectrum_index` , `msrun_msrun_msrun_name` , `precursor_precursor_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_spectrum_has_feature_feature1`
    FOREIGN KEY (`feature_feature_id` , `feature_msrun_msrun_name` )
    REFERENCES `feature` (`feature_id` , `msrun_msrun_msrun_name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX `fk_spectrum_has_feature_feature1` ON `spectrum_has_feature` (`feature_feature_id` ASC, `feature_msrun_msrun_name` ASC);
  CREATE INDEX `fk_spectrum_has_feature_spectrum1` ON `spectrum_has_feature` (`spectrum_spectrum_index` ASC, `spectrum_msrun_msrun_name` ASC, `spectrum_precursor_precursor_id` ASC);

Answer 1

您提到它在SQLite管理器中工作。我也无法复制，所以我想知道pysqlite2的版本(或者它的sqlite版本)是否有问题，或者代码中其他地方的一些副作用。

你能贴出pysqlite2和它的sqlite版本吗？我就是这样做的：

>>> from pysqlite2 import dbapi2 as sqlite
>>> sqlite.version
'2.3.5'
>>> sqlite.sqlite_version
'3.7.7.1'

更新

你能不能运行这个程序，如果你还收到错误，让我知道？

#!/usr/bin/python
from pysqlite2 import dbapi2 as sqlite

conn = sqlite.connect(':memory:')
cu = conn.cursor()
cu.executescript("""
DROP TABLE IF EXISTS msrun;
CREATE  TABLE IF NOT EXISTS msrun (
  msrun_name VARCHAR(40) PRIMARY KEY NOT NULL ,
  description VARCHAR(500) NOT NULL );

DROP TABLE IF EXISTS feature;
CREATE  TABLE IF NOT EXISTS feature (
  feature_id VARCHAR(40) PRIMARY KEY NOT NULL ,
  msrun_msrun_name VARCHAR(40) NOT NULL ,
  CONSTRAINT fk_feature_msrun1
    FOREIGN KEY (msrun_msrun_name )
    REFERENCES msrun (msrun_name )
    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_name ASC);

DROP TABLE IF EXISTS convexhull;
CREATE  TABLE IF NOT EXISTS convexhull (
  convexhull_id INT PRIMARY KEY NOT NULL ,
  mz DOUBLE NOT NULL ,
  rt DOUBLE NOT NULL ,
  feature_feature_id VARCHAR(40) NOT NULL ,
  CONSTRAINT fk_convexhull_feature
    FOREIGN KEY (feature_feature_id )
    REFERENCES feature (feature_id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX fk_convexhull_feature ON convexhull (feature_feature_id ASC);

DROP TABLE IF EXISTS position;
CREATE  TABLE IF NOT EXISTS position (
  position_id INT PRIMARY KEY NOT NULL ,
  dim0 INT NOT NULL ,
  dim1 INT NOT NULL ,
  feature_feature_id VARCHAR(40) NOT NULL ,
  CONSTRAINT fk_position_feature1
    FOREIGN KEY (feature_feature_id )
    REFERENCES feature (feature_id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX fk_position_feature1 ON position (feature_feature_id ASC);

DROP TABLE IF EXISTS userParam_names;
CREATE  TABLE IF NOT EXISTS userParam_names (
  userParam_id INT PRIMARY KEY NOT NULL ,
  Name VARCHAR(45) NOT NULL );

DROP TABLE IF EXISTS feature_has_userParam_names;
CREATE  TABLE IF NOT EXISTS feature_has_userParam_names (
  feature_feature_id VARCHAR(40) PRIMARY KEY NOT NULL ,
  userParam_names_userParam_id INT NOT NULL ,
  CONSTRAINT fk_feature_has_userParam_names_feature1
    FOREIGN KEY (feature_feature_id )
    REFERENCES feature (feature_id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_feature_has_userParam_names_userParam_names1
    FOREIGN KEY (userParam_names_userParam_id )
    REFERENCES userParam_names (userParam_id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX fk_feature_has_userParam_names_userParam_names1 ON feature_has_userParam_names (userParam_names_userParam_id ASC);
  CREATE INDEX fk_feature_has_userParam_names_feature1 ON feature_has_userParam_names (feature_feature_id ASC);

DROP TABLE IF EXISTS userParam_value;
CREATE  TABLE IF NOT EXISTS userParam_value (
  iduserParam_value INT PRIMARY KEY NOT NULL ,
  userParam_name VARCHAR(45) NOT NULL ,
  value VARCHAR(45) NOT NULL );

DROP TABLE IF EXISTS feature_has_userParam_names_has_userParam_value;
CREATE  TABLE IF NOT EXISTS feature_has_userParam_names_has_userParam_value (
  feature_has_userParam_names_feature_feature_id VARCHAR(40) PRIMARY KEY NOT NULL ,
  feature_has_userParam_names_userParam_names_userParam_id INT NOT NULL ,
  userParam_value_iduserParam_value INT NOT NULL ,
  CONSTRAINT fk_feature_has_userParam_names_has_userParam_value_feature_ha1
    FOREIGN KEY (feature_has_userParam_names_feature_feature_id , feature_has_userParam_names_userParam_names_userParam_id )
    REFERENCES feature_has_userParam_names (feature_feature_id , userParam_names_userParam_id )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_feature_has_userParam_names_has_userParam_value_userParam_1
    FOREIGN KEY (userParam_value_iduserParam_value )
    REFERENCES userParam_value (iduserParam_value )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

  CREATE INDEX fk_feature_has_userParam_names_has_userParam_value_userParam_1 ON  feature_has_userParam_names_has_userParam_value (userParam_value_iduserParam_value ASC);
  CREATE INDEX fk_feature_has_userParam_names_has_userParam_value_feature_ha1 ON  feature_has_userParam_names_has_userParam_value (feature_has_userParam_names_feature_feature_id ASC, feature_has_userParam_names_userParam_names_userParam_id ASC);
""")

conn.commit()
cu.execute("PRAGMA foreign_keys=ON")
cu.execute("INSERT INTO msrun VALUES (?,?)", ('bar', 'bar'))
cu.execute("INSERT INTO feature VALUES (?,?)", ('foo','bar'))
cu.execute("INSERT INTO feature VALUES (?,?)", ('foo1','bar'))
conn.commit()

cu.execute("SELECT * FROM feature")
print(cu.fetchall())

cu.execute("DELETE FROM feature WHERE feature_id=?", ("foo", ))
cu.execute("DELETE FROM feature WHERE feature_id=?", (str("foo1"), ))
conn.commit()

cu.execute("SELECT * FROM feature")
print(cu.fetchall())

更新2

您没有得到一些错误这一事实应该排除了features表及其依赖项与您发布的模式有任何问题。根据sqlite，我现在假设您有来自/到features表的任何子代/占优势的其他外键，而您没有包括这些外键(例如，一些没有提到的表依赖于您提到的依赖于features的表)。

下面是外键上的sqlite的一部分，让我相信：

如果数据库架构包含需要查看多个表定义才能识别的外键错误，则在创建表时不会检测到这些错误。相反，这些错误阻止应用程序准备SQL语句，这些语句以使用外键的方式修改子表或父表的内容。更改内容时报告的错误为"DML错误“，模式更改时报告的错误为"DDL错误”。因此，换句话说，配置错误的外键约束，需要查看子键和父键，都是DML错误。外键DML错误的英文错误消息通常是“外键不匹配”，但如果父表不存在，也可以是“没有此类表”。

如果您没有注意到，您得到的错误与有关FK违规的错误不同：

pysqlite2.dbapi2.IntegrityError: foreign key constraint failed

更新3

您的问题在于引用了一个不存在的spectrum_has_feature字段的FK feature。

CREATE  TABLE IF NOT EXISTS spectrum_has_feature (
<...>
FOREIGN KEY (feature_feature_id , feature_msrun_msrun_name )
REFERENCES feature (
    feature_id , 
    msrun_msrun_msrun_name ) -- <<< shouldn't this be msrun_msrun_name?
<...>

Answer 2

我在更新到Django==2.0后来到这里。我所需要的解决方法就是放弃我用Django==1.11.6编写的所有迁移，并使用2.0运行一个新的makemigrations。当然，如果我有任何数据库托管我的应用程序，需要维护迁移文件，我就不可能做到这一点。