2 votes

Niek de Klein avatar

OperationalError : incompatibilité de clé étrangère

Demandé el 12 de Avril, 2012
Quand la question a-t-elle été
5953 affichage
Nombre de visites la question a
0 Réponses
Nombre de réponses aux questions
Résolu
Situation réelle de la question

J'ai deux tableaux à remplir, 'msrun' et 'feature'. feature' a une clé étrangère qui pointe vers la colonne 'msrun_name' de la table 'msrun'. L'insertion dans les tables fonctionne bien. Mais lorsque j'essaie de supprimer des données de la table 'feature', j'obtiens l'erreur suivante :

pysqlite2.dbapi2.OperationalError: foreign key mismatch

D'après les règles des clés étrangères dans le manuel de 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.

Je ne vois rien que je viole. Mes tables de création ressemblent à ceci :

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

D'après ce que je peux voir, la table parent existe, la clé étrangère pointe vers la bonne clé parent, la clé parent est une clé primaire et la clé étrangère spécifie la colonne de la clé primaire.

Le script qui produit l'erreur :

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

Edit :

Ce sont les tables qui ont des clés étrangères liées aux caractéristiques. Elles ne sont pas encore remplies :

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

L'instruction de suppression fonctionne lorsque je la fais à partir du gestionnaire SQLite.

modifier 2 :

Trace complète :

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

Faire

DELETE FROM `feature` WHERE feature_id = 'f_13020522388175237334'

dans SQLite Manager fonctionne.

Edit 3

Toutes les tables sont incluses :

--------------------------------------------------------
-- 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);
Demandé el 12 de Avril, 2012 par Niek de Klein

Questions en vedette

Top Tags

Dans notre réseau

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

Yandex
X