A new record is not inserted one-to-many

Related Projects GRDB
1

I have two default tables "product" and "order" with one-to-many relationships

Product
| ----- id ---- | ----- name -----|

Order
| ----- id ---- | ----- productId -----| -------- createdAt ---------------|

Here is my migration file

      try database.create(table: "product", body: { table in
        table.column("id", .integer)
          .primaryKey(onConflict: .replace, autoincrement: true)
          .notNull()
        
        table.column("name", .text).notNull()
      })
      
      try database.create(table: "order", body: { table in
        table.column("id", .integer)
          .primaryKey(autoincrement: true)
          .notNull()
        
        table.column("productId", .integer)
          .notNull()
          .references("product",
                      column: "id",
                      onDelete: .cascade,
                      onUpdate: .cascade)
        
        table.column("createdAt", .datetime).notNull()
      })

And my code

    var productId: Int64? = getProductIdIfExisted() // 1 ..... id or nil
    var product = Product(id: productId, name: "NAME") // 2
    try product.insert(db) // 3
    var order = Order(id: nil, productId: product.id, createdAt: Date()) 
    try order.insert(db)

So both product and order entities has auto-increment primary key. On line "1" I try to find if the product is existed before. If the product does not exist, I insert it . if exists, I replace it. Then I insert order. However I have a problem because new order is not inserted and replaced previous, but I do not set id for order.
For example if I have empty tables and run this method in the first time everything is OK.
image

image
image728×112 6.7 KB

However when I run more than one time with product id = 1 I expect that a new order to be inserted. But I always see the id of the previous order updated. So after ten approaches there will be something like this

image
image694×124 6.8 KB

Why the previous record is replaced if I doesn't specify OrderID?

After first iteration if I comment line 3 everything is Ok

Thanks for any help :pray:

2

Hello,

However when I run more than one time with product id = 1 I expect that a new order to be inserted. But I always see the id of the previous order updated.

An insert that updates? This is a consequence of .primaryKey(onConflict: .replace) policy on the product table.

Please do check the documentation on the ON CONFLICT clause if needed.

I suggest that you trace your SQL statements, spot the inserts that trigger the conflict clause, so that you can fix them:

// Start tracing
db.trace { print($0.expandedDescription) }

// Run your db code
...

// Stop tracing
db.trace(options: [])
3

I want orders to be inserted into the database, but instead the id on the same record is updated.
I expect:
product
(1, «NAME»)

order
(1,1,2022-02-08 15:34:00.000)
(2,1,2022-02-08 15:35:00.000)
(3,1,2022-02-08 15:36:00.000)
(4,1,2022-02-08 15:37:00.000)
(5,1,2022-02-08 15:38:00.000)

But I get:
product
(1, «NAME»)

order
(5,1,2022-02-08 15:38:00.000)

4

Yes, this is well understood.

It looks like you don't know what the ON CONFLICT clause is. Probably you do not need it. And it prevents you from understanding why your program is misbehaving.

So update your migration as below (remove the onConflict argument):

      try database.create(table: "product", body: { table in
        table.column("id", .integer)
          .primaryKey(autoincrement: true)
          .notNull()
        
        table.column("name", .text).notNull()
      })

Then erase your application so that the migrator can rebuild the database from scratch, and run your application again.

You should then better understand what is wrong. You will have to read error messages. Yes, do read them! Get ready for learning a little bit about SQLite! That's how you'll make progress. Hold on, and you'll fix your issue.

5

Firstly I want to pay my respect for you! :smiling_face: Yes, I basically did not know it before. So it was helpful.
How did I solve my problem? I removed onConflict: .replace then replaced product.insert(db) by product.save(db). So I had another problem. When I "saved" existed record GRDB always tried to perform inserting instead of updating, because it could not find the primary key despite I passed it.

However when I replaced container[.rowID] = id by container["id"] = id it started working as I wanted.

Thanks a lot!

1 Like
6

Glad you found the solution :slight_smile:

1 Like