Hey im trying to use foreign keys, but it does not work.

This is my schema:

CREATE TABLE museum (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT, 
        address TEXT 
        );

CREATE TABLE exhibition (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL, 
        museum_id INTEGER,
        FOREIGN KEY(museum_id) REFERENCES museum(id) 
        );

This gets filled with data:

let db = new sqlite3.Database(DBSOURCE, (err) => {
if (err) {
    // Cannot open database
    console.error(err.message)
    throw err
} else {
    console.log('Connected to the SQLite database.')
    db.exec(fs.readFileSync('schema.sql').toString(),
        (err) => {
            if (err) {
                console.error(err.message)
            } else {
                // Table just created, creating some rows
                var insert = 'INSERT INTO museum (name, address) VALUES (?,?)'
                db.run(insert, ["museum1", "adress 1"])
                db.run(insert, ["museum2", "adress 2"])

                var insert = 'INSERT INTO exhibition (name, museum_id) VALUES (?,?)'
                db.run(insert, ["exhi1", 1])
                db.run(insert, ["exhi2", 2])
            }
        });
}
});

I read that I need to enable PRAGMA foreign_keys.

I tried that in various different ways (in the schema, outside, as seperate command, etc).

The result is all the time the same:

[Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
Emitted 'error' event on Statement instance at:
] {
  errno: 19,
  code: 'SQLITE_CONSTRAINT'
}

The outcome is like this:

id	   name	   museum_id
1	   exhi1   1

So for some reason just the connection to the first museum worked while the second failed? When not enabeling the pragma I will get no error using the same schema, but I asume then I dont have the foreign keys? I also would asume that I get an error when I use the foreign key without having the feature activated?

Any help is appreciated, ty.

I was unable to see what you are doing ineffectively. So I added this to your DDL:

INSERT INTO museum (name, address) VALUES
 ('museum1', 'address 1'), ('museum2', 'address 2');

PRAGMA foreign_keys=1;

INSERT INTO exhibition (name, museum_id) VALUES
 ('exhi1', 1), ('exhi2', 2);

.header on
SELECT * FROM museum;
SELECT * FROM exhibition;

It runs in the sqlite3 shell without any problem or complaint.

The cure for your trouble is likely to be a closer match between the above and whatever it is that you are doing.

I agree with the technical thinking behind this suggestion. However, generally, I do not undertake a revamp where somebody poses a specific problem which appears to simply illustrate their difficulty. This is in support of an incremental approach to discovering what has gone wrong.

Thank you for the response! I already read the doc multiple times, also looked through many stackoverflow posts. Basically trying to fix this issue since like 6-7 hours now.

Now I did what you said:

var insert = 'INSERT INTO museum (name, address) VALUES (?,?)'
                db.run(insert, ["museum1", "adress 1"])
                db.run(insert, ["museum2", "adress 2"])

                db.run("PRAGMA foreign_keys=1")

var insert = 'INSERT INTO exhibition (name, museum_id) VALUES (?,?)'
                db.run(insert, ["exhi1", 1])
                db.run(insert, ["exhi2", 2])

Still the same issue. Same error and the tables are also no filled correct. Just one exhibition is added.

Could there be another reason?

What happens if you run the following?

PRAGMA foreign_keys=1;
INSERT INTO museum (name, address) VALUES ('museum1', 'address 1');
INSERT INTO exhibition (name, museum_id) VALUES ('exhi1', last_insert_rowid());
INSERT INTO museum (name, address) VALUES ('museum2', 'address 2');
INSERT INTO exhibition (name, museum_id) VALUES ('exhi2', last_insert_rowid());

What is the total content (select * from museum;) of the museum table, and what is the total content (select * from exhibition;) of the exhibition table?

Mayhaps that sheds light?

hmm I used the node-sqlite3 for nodejs. It seems that this is just buggy or I dont know. I just tried another not so common package called better-sqlite3 and with that it works flawless.

thank you both very much for trying to help!! =)