Cascade Delete with Optional Key?

[bradhowes]

I'm back with another question, this time about cascade deletes for an association that is optional. Does this work? I was experimenting with the Associations playground, and I created Parent and Child records:

migrator.registerMigration("createLibrary") { db in
  try db.create(table: "child") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("name", .text).notNull()
  }

  try db.create(table: "parent") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("name", .text).notNull()
    t.belongsTo("child", onDelete: .cascade)
  }
}

struct Child: Codable, FetchableRecord, MutablePersistableRecord {
  var id: Int64?
  var name: String

  mutating func didInsert(_ inserted: InsertionSuccess) { id = inserted.rowID }
}

struct Parent: Codable, FetchableRecord, MutablePersistableRecord {
  var id: Int64?
  var name: String
  var childId: Int64?

  mutating func didInsert(_ inserted: InsertionSuccess) { id = inserted.rowID }
}

extension Parent {
  static let child = hasOne(Child.self)

  var child: QueryInterfaceRequest<Child> { request(for: Self.child) }
}

I can insert new Parent records with and without a child just fine, and fetching works as one would expect. However, when I delete a parent with a child, I am not seeing the child going away:

try dbQueue.write { db in
  var c = Child(name: "Child")
  try c.insert(db)
  var p = Parent(name: "Parent", childId: c.id)
  try p.insert(db)
}

for each in try dbQueue.read({ try Parent.fetchAll($0) }) {
  print("1 parent:", each)
}

let p = try dbQueue.read { try Parent.fetchAll($0) }[0]
try dbQueue.write { try p.delete($0) }

for each in try dbQueue.read({ try Parent.fetchAll($0) }) {
  print("2 parent:", each)
}

for each in try dbQueue.read({ try Child.fetchAll($0) }) {
  print("child:", each)
}

The output I get is:

1 parent: Parent(id: Optional(1), name: "Parent", childId: Optional(1))
child: Child(id: Optional(1), name: "Child")

Is this a known limitation?

[groue]

Hello @bradhowes,

When people talk about a parent/child relationships in a database, they usually talk about giving multiple children to one parent. You can compare with nodes in a tree: the root node has many children, and their parent is the root node.

The canonical database schema for "parents" and "children" is thus to have a parentId in the child table:

migrator.registerMigration("createLibrary") { db in
  try db.create(table: "parent") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("name", .text).notNull()
  }
  
  try db.create(table: "child") { t in
    t.autoIncrementedPrimaryKey("id")
    t.column("name", .text).notNull()
    t.belongsTo("parent", onDelete: .cascade)
  }
}

// One parent "has many" children
extension Parent {
  static let children = hasMany(Child.self)
}

// One child "belongs to" its parent
extension Child {
  static let parent = belongsTo(Parent.self)
}

This might sound like useless nitpicking. But we're discussing "parents" and "children", instead of "authors" and "books", or "artists" and "tracks". For clarity's sake, we'd better give their canonical roles to those canonical models.

However, when I delete a parent with a child, I am not seeing the child going away:

This now reads "However, when I delete a child, I am not seeing the parent going away".

And this is to be expected.

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

When rows are deleted from child, no parent is automatically deleted as a consequence. No parent would become invalid, if you think about it.

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

You can also see #1609, which tells how to delete a parent after all its children have been deleted (maybe this is what you are looking after).

[bradhowes]

@groue Thanks for the response, and sorry for the lack of precision in my terminology. Of course you are correct in how the relationship should flow -- I seem to have confused myself while changing terms and relationships found in your playground. Sorry for that, and thanks for your patience and guidance.

Brad