FOREIGN KEY constraint failed error, InsertMode.insertOrReplace

[albe-jj]

I have a dart script that connects to the database (WAL mode) and tries to do an insert

running:

  final attrId = await db.into(db.datasetAttrs).insert(
  DatasetAttrsCompanion.insert(
    key: 'sample',
    value: '123456-1234-12',
    scopeId: 32, 
  ),
  mode: InsertMode.insertOrReplace,
);

I get the error below

SqliteException(787): while executing statement, FOREIGN KEY constraint failed, constraint failed (code 787)
  Causing statement: INSERT OR REPLACE INTO "dataset_attr" ("key", "value", "scope_id") VALUES (?, ?, ?), parameters: sample, 123456-1234-12, 32

However running the raw sql query on a terminal INSERT OR REPLACE INTO "dataset_attr" ("key", "value", "scope_id") VALUES ("sample", " 123456-1234-12", 32); I would not get any error, and also would not get error trying to do the same insert from python.

Note if I set InsertMode.insertOrIgnore I get no error.

Note if the following I get the same error

final attrId = await db.customInsert(
  'INSERT OR REPLACE INTO "dataset_attr" ("key", "value", "scope_id") VALUES (?, ?, ?)',
  variables: [
    Variable.withString('sample'),
    Variable.withString('123456-1234-12'),
    Variable.withInt(32),
  ],
  updates: {db.datasetAttrs},
);

Below the table definition

@DataClassName("DatasetAttr")
class DatasetAttrs extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get key => text()();
  TextColumn get value => text()();
  IntColumn get scopeId => integer().references(Scopes, #id)();

  @override
  List<Set<Column>> get uniqueKeys => [{key, value, scopeId}];

  @override
  String get tableName => 'dataset_attr';
}

drift: ^2.20.2 Flutter (Channel stable, 3.24.1, on macOS 15.0 24A335 darwin-arm64, locale en-GB)

[simolus3]

Note if I set InsertMode.insertOrIgnore I get no error.

Right, but is the row inserted in that case?

[simolus3]

However running the raw sql query on a terminal INSERT OR REPLACE INTO "dataset_attr" ("key", "value", "scope_id") VALUES ("sample", " 123456-1234-12", 32); I would not get any error, and also would not get error trying to do the same insert from python.

Are you enabling foreign keys in drift (with e.g. a pragma foreign_keys = ON; in beforeOpen) or with a setup callback? This option is not persisted into the database file and needs to be re-enabled when opening the database in the sqlite command line tool or in Python. So it's possible that you're simply not getting the error in the other tools because foreign key checks have not been enabled there while they're enabled in your drift database.

[albe-jj]

Thanks for the reply.

You are right I was not enabling the foreign key in python, and I I would get foreign key error because the replace was trying to delete this row to replace it but it was referenced in another table.

Sorry for opening the issue. Thanks for the help

[albe-jj]

Closing this