On 5/30/06, Craig L Russell <Craig.Russell@sun.com> wrote:
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.
On May 30, 2006, at 7:38 AM, email@example.com wrote:
> 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
> 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.
>> -----Original Message-----
>> From: news [mailto:firstname.lastname@example.org
] On Behalf Of hilz
>> Sent: Monday, May 29, 2006 4:19 PM
>> To: email@example.com
>> 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
>> that show it is by design:
>> RESTART WITH integer-constant specifies the next value to be
>> 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
>> allows both manual inserts and system generated values, it is
>> that manually inserted values can conflict with system generated
>> To work around such conflicts, use the RESTART WITH syntax to specify
>> the next value that will be generated for the identity column.
>> 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)
>> UNIQUE INDEX tautoInd ON tauto(i) INSERT INTO tauto(k) values 1,2
>> The system will automatically generate values for the identity
>> But now you need to manually insert some data into the identity
>> 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
>> 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
>> issue an ALTER TABLE statement for the identity column with
>> RESTART WITH
>> 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
>> 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
>>> then on rows that are auto generated, they should chose an
>>> Using your example, it should have trapped the error and then
>>> tried to
>>> 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
>>>> 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
>>>> 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
>>>> 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.
Architect, Sun Java Enterprise System
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!