I am creating an app and need a database. The database contains a location table and point of interests table.
It's a one to many relationship.
A location has several point of interests.
Now I tried to do this relationship in flutter with sqflite, but failed.
I already tried to add a Foreign Key, but it did not work.
This is only the most important part of the code.
static final String locationTable = 'location';
static final String columnLocationId = 'id';
static final String columnLocationTitle = 'title';
static final String pointOfInterestTable = 'point_of_interest';
static final String columnPointOfInterestId = 'id';
static final String columnPointOfInterestTitle = 'title';
static final String createTableLocation = 'CREATE TABLE $locationTable('
'$columnLocationId INTEGER PRIMARY KEY AUTOINCREMENT,'
'$columnLocationTitle TEXT NOT NULL)';
static final String createTableLocation = 'CREATE TABLE $pointOfInterestTable('
'$columnPointOfInterestId INTEGER PRIMARY KEY AUTOINCREMENT,'
'$columnPointOfInterestTitle TEXT NOT NULL)';
Future<List> getPointOfInterestsOfLocations(int id) async {
final db = await this.db;
final maps = await db.query(
locationTable,
where: '$columnLocationId = ?',
whereArgs: [id],
);
return maps.toList();
}
And this is the line of code for loading it in a list:
List pointOfViews = await _pointOfViewDb.getPointOfInterestsOfLocations(id: location.id);
It seems you need to enable foreign key support in sqlite yourself.
In sqflite you can define a function like so:
static Future _onConfigure(Database db) async {
await db.execute('PRAGMA foreign_keys = ON');
}
and add it to your openDatabase function like so:
openDatabase(version: _databaseVersion, onCreate: _onCreate,
onUpdate: _onUpdate, onDelete: _onDelete,
onConfigure: _onConfigure);