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.
