db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: "generated by default" question
Date Tue, 30 May 2006 16:02:27 GMT
Based on the thread
http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+GENERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069
Derby
behavior is SQL 2000 compatible.

Mamta

On 5/30/06, Craig L Russell <Craig.Russell@sun.com> 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