Moor: Foreign keys not working

[simolus3]

I can't make it work any way. I tried both overriding from the "Table" class and calling it on the property.

Overriding:

```c#
class Requests extends Table {
IntColumn get requestId => integer()();
IntColumn get requestTypeId => integer()();
IntColumn get numberOfDocuments => integer()();

@override
Set get primaryKey => {requestId};
}

class RequestDocuments extends Table {
IntColumn get requestId =>
integer()();
IntColumn get documentId => integer()();
IntColumn get documentStateId => integer()();

@override
Set get primaryKey => {requestId, documentId};

@override
List get customConstraints => [
'FOREIGN KEY(request_id) REFERENCES requests(request_id)',
];
}

On the property:
```c#
class Requests extends Table {
  IntColumn get requestId => integer()();
  IntColumn get requestTypeId => integer()();
  IntColumn get numberOfDocuments => integer()();

  @override
  Set<Column> get primaryKey => {requestId};
}

class RequestDocuments extends Table {
  IntColumn get requestId =>
      integer().customConstraint('REFERENCES requests(request_id)')();
  IntColumn get documentId => integer()();
  IntColumn get documentStateId => integer()();

  @override
  Set<Column> get primaryKey => {requestId, documentId};
}

What I am doing wrong?

_Originally posted by @JPiris88 in https://github.com/simolus3/moor/issues/14#issuecomment-502613545_

[simolus3]

(moved to a new open issue for a better overview)
Both ways should work, actually. The custom constraint will only be written when the table is created. Perhaps you already had a file of the database on your device before you added the constraint?

Also, I think sqlite doesn't enforce references by default, so this library should have a way to turn that on explicitly.

[JPiris88]

@simolus3: I uninstalled my app from the emulator and also wiped the emulator data in the AVD Manager, so the database file has been created again. I do this very often.

Regarding your second suggestion, I also thought that so I tried to enable the foreign keys running the next command, but it doesn't work:

FlutterQueryExecutor.inDatabaseFolder(path: 'db.sqlite', logStatements: false).runCustom("PRAGMA foreign_keys = ON")

[simolus3]

That's actually a good idea: When passing the FlutterQueryExecutor as a constructor and calling it there, the custom statement is executed.

@UseMoor(...)
class Database extends _$Database {
  Database()
      : super(
          FlutterQueryExecutor.inDatabaseFolder(
            path: 'db.sqlite',
            logStatements: true,
          )..doWhenOpened((e) => e.runCustom('PRAGMA foreign_keys = ON')),
        );

Arguably, this really isn't an acceptable solution. I'll implement some proper post-opening callbacks in the next versions which hopefully make this easier.

[simolus3]

In the next version (1.5), you will be able to do the following:

@override
MigrationStrategy get migration {
  return MigrationStrategy(
    beforeOpen: (engine, details) async {
      await engine.customStatement('PRAGMA foreign_keys = ON;');
    },
  );
}

The beforeOpen callback is new and will be called whenever the database is opened. The customStatement method is also new and can run arbitrary statements that don't necessarily have a return value.