db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hilz <hs...@hotmail.com>
Subject Re: "generated by default" question
Date Fri, 09 Jun 2006 04:28:10 GMT
Well...
A good solution would probably be if someone would look at the HSQLDB 
code and see how they do it because it works like a charm over there!



Craig L Russell wrote:
> Hi G,
> 
> Do you have a good solution that you can write up in detail and post to 
> JIRA? Then someone with an itch can fix it. Do-ocracy in action.
> 
> Craig
> 
> On May 30, 2006, at 7:38 AM, 
> derby@segel.com wrote:
> 
>>
>> Hmmm,
>>
>> Yeah, that's what I'm afraid of.
>>
>> Essentially what they are asking is that if you try to insert a row 
>> and it
>> fails, you have two options.
>>
>> 1) Increment your count and try again (Restart) until you succeed, or, 
>> find
>> the max value, and reset to max value + 1.
>>
>> The trouble in using #2, is that lets say you have rows 1,2,3,4,5 
>> entered,
>> and someone manually enters 10, 1001, 1002, 1004. So when you search 
>> for the
>> max value, you'll end up 1004, and restart with 1005.
>>
>> This is something that should be done behind the scenes.
>>
>> SO its not a "bug" but a design defect.
>>
>> -G
>>
>>> -----Original Message-----
>>> From: news [mailto:news@sea.gmane.org] On 
>>> Behalf Of hilz
>>> Sent: Monday, May 29, 2006 4:19 PM
>>> To: derby-user@db.apache.org
>>> Subject: Re: "generated by default" question
>>>
>>> I wish it was a bug!
>>> it seems this is by design, but i hope there is some other way to
>>> overcome this behavior.
>>>
>>> here is a quote from the docs at
>>>    http://db.apache.org/derby/docs/dev/ref/ref-single.html
>>> that show it is by design:
>>>
>>> <quote>
>>> RESTART WITH integer-constant specifies the next value to be generated
>>> for the identity column. RESTART WITH is useful for a table that has an
>>> identity column that was defined as GENERATED BY DEFAULT and that has a
>>> unique key defined on that identity column. Because GENERATED BY DEFAULT
>>> allows both manual inserts and system generated values, it is possible
>>> that manually inserted values can conflict with system generated values.
>>> To work around such conflicts, use the RESTART WITH syntax to specify
>>> the next value that will be generated for the identity column. Consider
>>> the following example, which involves a combination of automatically
>>> generated data and manually inserted data:
>>>
>>> CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT) CREATE
>>> UNIQUE INDEX tautoInd ON tauto(i) INSERT INTO tauto(k) values 1,2
>>>
>>> The system will automatically generate values for the identity column.
>>> But now you need to manually insert some data into the identity column:
>>>
>>> INSERT INTO tauto VALUES (3,3) INSERT INTO tauto VALUES (4,4) INSERT
>>> INTO tauto VALUES (5,5)
>>>
>>> The identity column has used values 1 through 5 at this point.  If you
>>> now want the system to generate a value, the system will generate a 3,
>>> which will result in a unique key exception because the value 3 has
>>> already been manually inserted.  To compensate for the manual inserts,
>>> issue an ALTER TABLE statement for the identity column with RESTART WITH
>>> 6:
>>>
>>> ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6
>>>
>>> ALTER TABLE does not affect any view that references the table being
>>> altered. This includes views that have an "*" in their SELECT list. You
>>> must drop and re-create those views if you wish them to return the new
>>> columns.
>>> </quote>
>>>
>>>
>>>
>>>
>>>
>>> Michael Segel wrote:
>>>> On Monday 29 May 2006 3:31 pm, hilz wrote:
>>>> After a quick glance,
>>>>
>>>> This looks like a bug.
>>>>
>>>> You should be able to insert your own values in the ID column, which 
>>>> you
>>> do...
>>>> then on rows that are auto generated, they should chose an incremental
>>> value.
>>>>
>>>> Using your example, it should have trapped the error and then tried to
>>> insert
>>>> using 2... until it found an integer value that was not in use.
>>>>
>>>> But hey, what do I know.
>>>> I'm sure someone is going to tell me that this functionality is 
>>>> behaving
>>> per
>>>> spec....
>>>>
>>>> -G
>>>>
>>>>> Hi all.
>>>>> If i have a table A defined as follows:
>>>>>
>>>>> create table A
>>>>> (
>>>>> ID     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
>>>>> NAME   VARCHAR(255)
>>>>> );
>>>>>
>>>>> then i do the following:
>>>>>
>>>>>      insert into A (ID, NAME) values (1,'hello 1');
>>>>>
>>>>> and then i do the following:
>>>>>
>>>>>      insert into A (NAME) values ('hello 2');
>>>>>
>>>>> I will get this error:
>>>>>
>>>>> The statement was aborted because it would have caused a duplicate key
>>>>> value in a unique or primary key constraint or unique index identified
>>>>> by 'SQL060529010004440' defined on 'A'.
>>>>>
>>>>> To avoid this, I will have to do :
>>>>>
>>>>>      alter table....RESTART WITH....
>>>>>
>>>>> Is there another way to make the autoincrement smart enough to know
>>> that
>>>>> the value already exists and just generate a new value for me?
>>>>> I find it odd to have to set the "restart with" to skip the values 
>>>>> that
>>>>> i set manually.
>>>>>
>>>>> thanks for any help.
>>>>
>>
>>
>>
> 
> Craig Russell
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> 408 276-5638 mailto:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
> 


Mime
View raw message