When to INSERT or UPDATE with composite primary key models in ORM?

Question

I've got a homemade ORM system that currently only supports auto-incrementing primary keys. This has worked well, but the time has come to support composite primary keys. I'm aware of the holy war between surrogate vs. composite primary keys, and I personally think there's a place for each. Anyway... :)

With an auto-incrementing primary key, it's easy to tell if an INSERT or UPDATE statement has to be run when the model is saved: it depends on whether the primary key is set or not.

But with (non-auto-incrementing) composite primary keys, it's not easy to tell, since you have to set all the composite primary key field values even if you're doing an INSERT.

So, to my question: How do I determine if an INSERT or UPDATE statement should be run when working with a composite primary key?

Here are the solutions I've come up with:

1. Query for whether a record exists with the composite primary key beforehand. This seems like the best option, even though an additional query has to be run on every save.
2. Use MySQL's INSERT ... ON DUPLICATE KEY UPDATE syntax. But there are other operations that my Model class completes that require knowing whether a record is new or not, like whether to validate all columns (when the record is new) or just the modified columns (when the record already exists).

Do you have a better solution? Or is one of these my best bet? Thanks for your insight!

Answer 1

I really wouldn't tie myself to RDBMS specific syntax if I could avoid it, so I'd rule number 2 out.

A third option is to let the user decide and handle errors, providing both Create() and Update() methods in your ORM, counting on the RDBMS to error out with a duplicate key error if you Create() when you shouldn't have.

I personally would provide both the first and third approaches in an ORM, so people can choose the best suited alternative (ease of use for non critical code paths, or control for places where performance matters).

Answer 2

I believe you can use REPLACE or use

"INSERT ... ON DUPLICATE KEY UPDATE" as you said .. here is something which might help from the manual.

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.

Answer 3

If I have to use your tool, this is the behavior I would want (and the behavior other OR/M use):

1. Maintain a IsNew property on your classes. When you fetch data, set that property to false. When the user create a new class, set it to true.

2. When the save action comes, now you know whether perform an INSERT or an UPDATE based on that property (IsNew).

Benefits:

• No additional queries.
• If the user wants to make an update without fetch the data first, you can make that property public so they can set IsNew=false, then an update will take place.
• Doesn't matter if you have single or composite PKs, autoincrement columns, etc.