У меня есть две таблицы, которые я заполняю: «msrun» и «feature». «feature» имеет внешний ключ, указывающий на столбец «msrun_name» таблицы «msrun». Вставка в таблицы работает нормально. Но когда я пытаюсь удалить из таблицы «функции», я получаю следующую ошибку:
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.
редактировать 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 Manager работает.
Редактировать 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);
Traceback (most recent call last): File "./bla.py", line 218, in <module> cu.execute("DELETE FROM feature WHERE feature_id=?", ("foo", )) pysqlite2.dbapi2.OperationalError: foreign key mismatch
- person   schedule 13.04.2012