Deleting in a many-to-many relationship

[phi161]

Following this issue's example with songs and albums, I was wondering if it's possible to have an album automatically removed after I delete its last song.

With the suggested setup, when I delete a song I can see the entry from albumSong removed, but the referenced album row is still there. I also tried to delete rows from albumSong directly, but cascade didn't work for song/album.

[groue]

Hello @phi161,

I was wondering if it's possible to have an album automatically removed after I delete its last song.

When I delete a song I can see the entry from albumSong removed, but the referenced album row is still there.

Indeed, deleting albums that have no song has to be done by the application.

Assuming you have defined an Album.albumSongs hasMany association, you can run the following line:

// Delete empty albums that do not contain any song.
try Album.having(Album.albumSongs.isEmpty).deleteAll(db)

See Association Aggregates for more information about isEmpty.

I also tried to delete rows from albumSong directly, but cascade didn't work for song/album.

When rows are deleted from song, SQLite deletes the matching rows in albumSong automatically - that's what the cascade does. Similarly, when rows are deleted from album, SQLite also deletes the matching rows in albumSong. The goal of a cascade is to guarantee database integrity: SQLite makes sure that the database can not store an invalid albumSong row that refers to a song or album that does not exist.

When rows are deleted from albumSong, directly or indirectly because song or album rows are deleted, no album or song is automatically deleted as a consequence. No album or song would become invalid, if you think about it.

For more information about foreign keys and cascades, please see SQLite documentation.

[phi161]

you can run the following line

This is great - I was doing something similar but your code is much more descriptive :)

Also thanks a lot for the nice explanation about cascade, everything is clear now!

[morstin]

Just as an alternative idea - I decided to leave it with the database if a record can be deleted

• When creating the table albumSong I use onDelete: .restrict on the column referencing to the album table
  t.column("albumId", .integer).notNull().indexed().references("album", onDelete: .restrict)
• Then I can easily try to delete albums which executes when there is no song left or which otherwise results in an error which I catch with catch ResultCode.SQLITE_CONSTRAINT

I think this is better to manage at db level. Imagine that later you introduce another table needing referencial integrity to the table album. You would need to remember this place of code where you test / delete and then adapt try Album.having(Album.albumSongs.isEmpty).deleteAll(db) to the new complexity of the db.

Hope this makes sense

[groue]

Yes, this makes sense to me as well :-)

---

Unrelated:

t.column("albumId", .integer).notNull().indexed().references("album", onDelete: .restrict)

You can describe the exact same column with:

t.belongsTo("album", onDelete: .restrict).notNull()

Actually, since "albumId" is likely a member of the "albumSong" primary key, you can even drop the notNull():

try db.create(table: "albumSong") { t in
    try t.primaryKey {
        // NOT NULL is implicit while defining a primary key
        t.belongsTo("album", onDelete: .restrict)
        t.belongsTo("song", onDelete: .cascade)
    }
    ...
}

The documentation of GRDB 7 beta has a new Modifying the Database Schema page that provides an overview of schema definition methods.