Skip to Content
0

Error with generated by default as identity

Jul 11, 2017 at 03:02 PM

60

avatar image
Former Member

Hello

I created a table with an ID column that is filled automatically by 'generated by default as identity' (which uses a sequence).

If the table is filled with data and reaches the limit of the ID field it is not possible to insert new records.

The problem is that even after deleting records from that table it is not possible to insert new records because the sequence does not get reseted.

The sequence is generated by the system and is defined with a 'reset by' clause, which is executed automatically when the database gets restarted.

Is there a way to reset this sequence without restarting the database?

Best regards,

Houssem

PS: here a sample sql

create column table T (

ID smallint generated by default as identity,

WERT integer

);

do

begin

declare i integer;

for i in 1..32767 do

insert into T(WERT)

values (:i*100);

end for;

end;

delete from T where WERT>200;

insert into T (WERT)

values (300)

;

SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

0 Answers