reset IDENTITY field

[Rene Bosch]

Maybe a stupid question, but I'm not that much of an expert on
DBMS's ....

I have a table with an field that is an IDENTITY field. It is loaded
with records form a daily incoming file. The sequence number of the
identity field gives an indication of the order in which records come
in.
Sometimes I need to start loading this table from scratch. I do this
by TRUNCATE-ing it. But when I again insert entries the IDENTITY
field continues numbering where it left off before the TRUNCATE. For
my purpose I need to have it start with 1 again.

How can you do that?

[Hänsch, Wolfgang]

ALTER SEQUENCE sequenceName [RESTART WITH long] [INCREMENT BY long]

Changes the next value and the increment of a sequence. This command does not commit the current transaction; however the new value is used by other transactions immediately, and rolling back this command has no effect.

Example:

ALTER SEQUENCE SEQ_ID RESTART WITH 1000
Changes the next value and the increment of a sequence. This command does not commit the current transaction; however the new value is used by other transactions immediately, and rolling back this command has no effect.

Wolfgang

[Thomas Mueller]

Hi,

If you didn't explicitly used a sequence, then I think what your are
looking for is:

ALTER TABLE TEST ALTER COLUMN ID RESTART WITH 10000;

See also http://h2database.com/html/grammar.html#alter_table_alter

I wonder if other databases reset the identity if you truncate the table?

Regards,
Thomas

[Maaartin]

On Feb 11, 7:57 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> I wonder if other databases reset the identity if you truncate the table?

At least MySql does:
http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

For other storage engines, TRUNCATE TABLE differs from DELETE in the
following ways in MySQL 5.0:
...
The table handler does not remember the last used AUTO_INCREMENT
value, but starts counting from the beginning. This is true even for
MyISAM and InnoDB, which normally do not reuse sequence values.

[Thomas Mueller]

Hi,

I will add a feature request: "TRUNCATE should reset the identity
columns as in MySQL and MS SQL Server (and possibly other databases)."

Regards,
Thomas

[Maaartin]

On Mar 21, 8:28 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> I will add a feature request: "TRUNCATE should reset the identity
> columns as in MySQL and MS SQL Server (and possibly other databases)."

Hi,

there's (at least) one more thing working differently in MySql: With

DROP ALL OBJECTS;
CREATE TABLE t (a INT);
CREATE PRIMARY KEY ON t (a);

you get "COLUMN_MUST_NOT_BE_NULLABLE_1 = 90023" while MySql just
silently adds NOT NULL.

Disclaimer: I don't care about it at all, just stumbled upon it.

[Thomas Mueller]

Hi,

This also seems to work for PostgreSQL and HSQLDB. It fails for Derby
and H2. I will add a feature request (patches are welcome of course):

drop table t;
create table t(a int);
-- supposed to work
alter table t add constraint x primary key(a);

drop table t;
create table t(a int);
insert into t values(1);
-- supposed to work
alter table t add constraint x primary key(a);

drop table t;
create table t(a int);
insert into t values(null);
-- not supposed to work
alter table t add constraint x primary key(a);

Regards,
Thomas