db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig L Russell <Craig.Russ...@Sun.COM>
Subject Re: "generated by default" question
Date Tue, 30 May 2006 15:23:21 GMT
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