MSSQL IGNORE_DUP_KEY and postInsert on new entity

Description

MSSQL has a handy feature with unique indexes called IGNORE_DUP_KEY. It is off by default, but when enabled, it will just disregard any request to INSERT a record that would violate a unique constraint instead of throwing an error. While this isn't always desirable, it can save you a lot of trouble having to always go checking for a duplicate record before an insert.

It breaks Quick because – since it's not erroring – Quick expects it to return the key of the record it just saved. Since there is no record, there is no key, and line 21 of (for example) AutoIncrementingKeyType.cfc will fail.

I don't know whether this sort of thing is unique to MSSQL, but I wonder what the behavior should be. The CF code creating the entity will expect to see the entity it just saved. If you're relying on attributes existing on that entity, you probably want it to throw an exception. But in the typical use case where we employ this index option, we aren't relying on values existing; we're just processing an insert and moving on and so we don't want Quick to throw an error.

Should there be an option, either on the module or perhaps even the specific .save() call level, that says 'if you don't get back a key, just ignore it and keep going?' I don't want Quick to get lost in the weeds on db-specific index options, but this is a handy feature and it'd be a shame to not be able to use it.

Another alternative would be a blueprint for InsertWhereNotExists that could handle the relevant grammar for each DB, but this is a whole different can of worms because of race conditions, transaction locks, and other things that make the answer to the question 'does this record exist' unreliable almost the moment you get it.

Activity

Show:
Eric Peterson
October 22, 2019, 10:46 PM
Edited

I wonder if a solution to this would be dropping down the the query builder to insert.

The QueryBuilder insert method doesn’t care about keys. Then you could go on your way.

Eric Peterson
October 22, 2019, 10:48 PM

But I’m not sure about modifying Quick for this use case. Mostly because so far Quick is concerned with entities, objects with identity. If an object doesn’t really care about its identity, then maybe it isn’t an entity? Not sure….

Samuel W. Knowlton
October 23, 2019, 12:22 AM

The use case here is that we build up one or more entities and then call .save(), and in the unlikely-but-possible event that one of them has already been created, we just skip it. We don’t want to interrupt the flow of what’s going on and we don’t want to throw an error. But that is an edge case where ordinarily we would want those things.

So in this case, it’s not that we can say ‘the object doesn’t care about its identity,' but rather ‘in the ordinary operation of things, the object cares greatly about its identity, but if we don’t have an identity, just forget about it.'

Eric Peterson
January 3, 2020, 5:15 AM

For now the way to handle this would be a custom key type that would ignore any exceptions about unique key constraints.

Won't Fix

Assignee

Eric Peterson

Reporter

Samuel W. Knowlton

Labels

None

Priority

Major