> -----Original Message----- > From: news [mailto:[EMAIL PROTECTED] On Behalf Of hilz > Sent: Thursday, June 08, 2006 11:28 PM > To: derby-user@db.apache.org > Subject: Re: "generated by default" question > > Well... > A good solution would probably be if someone would look at the HSQLDB > code and see how they do it because it works like a charm over there! > [mjs] It would be best if you wrote your own code.
As a contributor to Apache, you agree to indemnify Apache against any lawsuit that may occur due to your contribution. Please remember that Open Source is not a license to steal. (I don't think that you were suggesting this, however, it is too easy and tempting to cut and paste aka "borrow" code.) Please note that the lawsuit between Caldera and IBM is based on the "borrowing" of code. Whether the suit has any merit, it will still cost you to defend yourself. Having said that, Daniel already made a suggestion which is in use today by other databases.... The simplest solution would be to find the MAX value, rest the sequence to MAX() +1 and re-insert the row. There are other possible solutions. It's disappointing that certain people don't accept this as a bug and that neither Sun nor IBM, which sell support for Derby, are not stepping forward to fix this issue. Just my .02 cents. -Mike > > > Craig L Russell 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, > > [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] > > P.S. A good JDO? O, Gasp! > >
