SQLite FOREIGN KEY constraint not being triggered from NodeJS / JavaScript

Question

I am trying to create a POST route as shown below. My Timesheet table has a FOREIGN KEY constraint set on it referencing the Employee table. When I try and insert a timesheet from an employee that doesn't exist using DB Browser I get the correct error triggered by the constraint. However, when I run the code below, it ignores this constraint and inserts the record. Have I set this up incorrectly or is this not a valid constraint under this scenario?

timesheetRouter.post("/", getParams, (req, res, next) => {
    const tsData = req.body.timesheet;
    console.log("ID", req.empID);
    db.run(`insert into Timesheet (hours, rate, date, employee_id) 
        values ($hours, $rate, $date, $emp);`, {
        $hours: tsData.hours,
        $rate: tsData.rate,
        $date: tsData.date,
        $emp: req.empID
    }, function(err) {
        if (err) {
            return res.status(404).send(err);
        } else {
            db.get("select * from Timesheet where id = $id", {$id: this.lastID},
                (err, row) => {
                    return res.status(201).send({timesheet: row});
                });
        }
    });
});

Table creation code:

db.serialize(() => {
    db.run("DROP TABLE IF EXISTS Timesheet", err => {
        if (err) {
            throw new Error(err);
        }
    });
    db.run(`create table Timesheet (id integer PRIMARY KEY,
        hours integer NOT NULL,
        rate integer NOT NULL,
        date integer NOT NULL,
        employee_id integer NOT NULL, FOREIGN KEY (employee_id) REFERENCES Employee (id))`,
    err => {
        if (err) {
            throw new Error(err);
        }
    });
});

Note: getParams is a function to determine the value for empID.

szmate1618 · Accepted Answer · 2018-11-08 17:46:16Z

18

Currently, in SQLite version 3.25.2 foreign key constraints are disabled by default. You need to turn them on by

sqlite> PRAGMA foreign_keys = ON;

Or, in your case

db.get("PRAGMA foreign_keys = ON")

I assume.

It works on a per-connection basis, DB Browser probably automatically sets it on start up, that's why it triggers an error in that case.

edited Nov 8, 2018 at 17:46

answered Oct 31, 2018 at 14:00

szmate1618

1,7851 gold badge20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nathanjw Over a year ago

So this would need to be run each time the query is run? Just checking what you mean by per-connection.

szmate1618 Over a year ago

Not for every query, just once for every db object. I assume you have a let db = new sqlite3.Database(':memory:'); or something similar somewhere. You just need to turn foreign key support on after that, and it will remain enabled until you close the db or re-assign a new connection to it.

Collectives™ on Stack Overflow

SQLite FOREIGN KEY constraint not being triggered from NodeJS / JavaScript

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related