db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: "generated by default" question
Date Mon, 29 May 2006 21:09:40 GMT
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.

-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]

Mime
View raw message