Can I do a case-based foreign key command (either cascade or set null)?

I would like to give the user the option when he is deleting an item in the parent table, to either cascade delete or set null to the items in the child table that references the foreign key, is that possible within SQLITE? or do I have to do this within my code? These are the commands I currently use when creating both tables, currently when deleting a category, the items that were assigned this category are being set to null, basically I would like to give the user the option to delete all of them, preferably within the SQL code.

    await database.execute(
      "CREATE TABLE $TABLE_CATEGORIES ("
      "$COLUMN_CATEGORYID INTEGER PRIMARY KEY,"
      "$COLUMN_CATEGORYNAME TEXT"
      ")",
    );
    await database.execute(
      "CREATE TABLE $TABLE_GROCERIES ("
      "$COLUMN_ID INTEGER PRIMARY KEY,"
      "$COLUMN_NAME TEXT,"
      "$COLUMN_PPU REAL,"
      "$COLUMN_BASE REAL,"
      "$COLUMN_STOCK REAL,"
      "$COLUMN_CATEGORYID INTEGER,"
      "FOREIGN KEY($COLUMN_CATEGORYID) REFERENCES $TABLE_CATEGORIES($COLUMN_CATEGORYID) ON DELETE SET NULL"
      ")",
    );

​x
 
    await database.execute(      "CREATE TABLE $TABLE_CATEGORIES ("      "$COLUMN_CATEGORYID INTEGER PRIMARY KEY,"      "$COLUMN_CATEGORYNAME TEXT"      ")",    );    await database.execute(      "CREATE TABLE $TABLE_GROCERIES ("      "$COLUMN_ID INTEGER PRIMARY KEY,"      "$COLUMN_NAME TEXT,"      "$COLUMN_PPU REAL,"      "$COLUMN_BASE REAL,"      "$COLUMN_STOCK REAL,"      "$COLUMN_CATEGORYID INTEGER,"      "FOREIGN KEY($COLUMN_CATEGORYID) REFERENCES $TABLE_CATEGORIES($COLUMN_CATEGORYID) ON DELETE SET NULL"      ")",    );​

Answer

You need to solve this in your code. You can only configure the database to either “SET NULL” or “CASCADE DELETE”. If it would be my task i would use the “ON DELETE SET NULL” and allow the user to remove child rows by firing a second sql query like DELETE FROM child_table WHERE parent_id = $parentid

Advertisement

Answer