RE: [Bug 11270] New: Interaction between in-line keys and key generators

From: Jonas Sicking [mailto:jonas@sicking.cc] 
Sent: Wednesday, November 10, 2010 2:08 PM

>> On Wed, Nov 10, 2010 at 1:50 PM, Tab Atkins Jr. <jackalmage@gmail.com> wrote:
>> > On Wed, Nov 10, 2010 at 1:43 PM, Pablo Castro
>> > <Pablo.Castro@microsoft.com> wrote:
>> >>
>> >> From: public-webapps-request@w3.org [mailto:public-webapps-request@w3.org] On Behalf Of bugzilla@jessica.w3.org
>> >> Sent: Monday, November 08, 2010 5:07 PM
>> >>
>>
>> I'm fine with either solution here. My database experience is too weak
>> to have strong opinions on this matter.
>>
>> What do databases usually do with columns that use autoincrement but a
>> value is still supplied? My recollection is that that is generally
>> allowed?

It does happen in practice that sometimes you need to use explicit keys. The typical case is when you're initializing a database with base data and you want to have known keys. 

As for what databases do, I'll use SQL Server as an example (for no particular reason :) ). In SQL Server by default if you try to insert a row with a value in an "identity" column you get an error and the operation is aborted; however, developers can issue a command (SET IDENTITY_INSERT <table> ON) to turn it off temporarily and insert rows with an explicitly provided primary key. Usually when you do this you have to be careful to use keys that are either way out of the range of keys the generator will use (or you may not be able to insert keys anymore) or you have to reset the next key (using an obscure DBCC CHECKIDENT (<table>, RESEED, <next-key>) command). 

I don't know much about Oracle, but I believe the typical pattern is still to use a sequence object and set the default value for the key column to < sequence>.nextval, thus allowing callers to override the next value in the sequence by just providing one, and if necessary they may need to go and fix up the sequence. 

>From writing the above paragraph I'm realizing one more detail we need to be explicit about: the fact that you do an add() with an explicit key does not mean the implementation will fix up the next key it'll assign. You'll still get the value that comes after the one generated last, and if you inserted that value in the store explicitly you just made the store unable to add new objects with generated keys until you delete it.

If that's too much fine-print then we should just disallow it. I like the ability to set explicit key values, but it does come with some extra care that both implementers and users will have to have.

-pablo

Received on Wednesday, 10 November 2010 22:58:57 UTC