db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: "generated by default" question
Date Tue, 30 May 2006 14:38:52 GMT

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.
> >




Mime
View raw message