Flutter SQFlite many to many relationship setup

Question

I have a Flutter app that will need two tables, table A and table B, now each item from A has many items from B, and each item from B can belong to many items of A, so it's a many-to-many relationship.

this is the code I've written:

static Future<Database> database() async {
    final dbPath = await sql.getDatabasesPath();
    return sql.openDatabase(path.join(dbPath, 'my_db.db'),
        onCreate: (db, version) {
      return db.execute(
          'CREATE TABLE A(id INT PRIMARY KEY, title TEXT, description TEXT); CREATE TABLE B(id INT PRIMARY KEY, title TEXT, description TEXT);');
    }, version: 1);
  }

I'm not doing any kind of relating the two tables to each other, because I don't know how to do that in SQFLite.

Do I need to make a third table?

Any help would be appreciated.

Answer 1

First one thing you should notice is that isn't that different of using just Sql

Assuming you have table A and B you can set up as it follows

Future _create(Database db, int version) async {
    await db.execute("""
            CREATE TABLE C (
              id INTEGER PRIMARY KEY, 
              A_id INTEGER NOT NULL,
              B_id INTEGER NOT NULL,
              FOREIGN KEY (A_id) REFERENCES A (id) 
                ON DELETE NO ACTION ON UPDATE NO ACTION,
              FOREIGN KEY (B_id) REFERENCES B (id) 
                ON DELETE NO ACTION ON UPDATE NO ACTION
            )""");
  }

And you can other columns as you wish accordingly to a specific use-case