OperationalError: foreign key mismatch
- by Niek de Klein
I have two tables that I'm filling, 'msrun' and 'feature'. 'feature' has a foreign key pointing to the 'msrun_name' column of the 'msrun' table. Inserting in the tables works fine. But when I try to delete from the 'feature' table I get the following error:
pysqlite2.dbapi2.OperationalError: foreign key mismatch
From the rules of foreign keys in the manual of 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.
I can see nothing that I'm violating. My create tables look like this:
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);
As far as I can see the parent table exists, the foreign key is pointing to the right parent key, the parent key is a primary key and the foreign key specifies the primary key column.
The script that produces the error:
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']),))
