How can I enforce SQLite foreign keys in python using pysqlite2?

Question

I have a SQLite database. According to this answer you need version 3.6.19+, which I have. I made it using version 3.7.10 of SQLite:

>>> from pysqlite2 import dbapi2 as sqlite
>>> print sqlite.sqlite_version
3.7.10

So it should support foreign keys.

I have the following sqlite database create statements:

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_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_name` ASC); 

So msrun_msrun_name in the feature table is a foreign key of msrun_name in the msrun table. However, when I'm filling up the feature table, I can fill in anything I want for msrun_msrun_name. The foreign key is not being enforced.

According to this question the enforcing needs to be turned on, and it gives an answer how to do it with SQLAlchemy, but I'm not using SQLAlchemy.

How can I enforce foreign keys with pysqlite2?

Answer 1

Try using SQLite's PRAGMA foreign_keys=ON; command, it is disabled by default.