Flutter SQFlite one to many relationship setup

user_5786574 0
Viewed 14256

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);
sqflite dart flutter android sql
4 Answers

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);
user_51509260

Foreign key relationship in SQFlite (Flutter) can be done as follows,

 createTable() async{
  final db = await database;
  var raw = await db.execute("CREATE TABLE MarketInfoes ("
          "id integer primary key, userId integer, typeId integer,"
          "gradeId integer, price DOUBLE" 
          "FOREIGN KEY (typeId) REFERENCES Type (id) ON DELETE NO ACTION ON UPDATE NO ACTION," 
          "FOREIGN KEY (userId) REFERENCES User (id) ON DELETE NO ACTION ON UPDATE NO ACTION," 
          "FOREIGN KEY (gradeId) REFERENCES Grade (id) ON DELETE NO ACTION ON UPDATE NO ACTION"
          ")");
}
user_72928980

Take a look here at this reference.

Create Tables and SQL Relationships with Foreign Keys

user_87685240

When you need to use the FOREIGN KEY, you HAVE to put the INTEGER reference inside the same sql queue

user_123745830
Related