How to reset serial without losing foreign constraints

[Wlodek]

Hi all

The documentation says that to re/set serial column next value one should use

alter table t modify (c serial(x));

What it doesn't say is that by doing this you loose all
foreign key constraints pointing to this column.

Anyone knows how to reset such column without loss of FK?

Thanks
Wlodek

[Doug Lawry]

Hi Wlodek.

To advance the next SERIAL value to be used, use a dummy INSERT such as the one below (or LOAD if it doesn't like that), appending names and values for any other NOT NULL columns:

begin work;
insert into t (c) values (x-1);
rollback work;

It's rather more difficult to reduce the next value!

Regards,
Doug Lawry

[Doug Lawry]

See Art's further comments below as informix-***@iiug.org doesn't seem to be replicating on comp.databases.informix.


From: Art Kagel
Sent: 02 February 2014 19:45
To: Doug Lawry
Cc: IIUG Informix List
Subject: Re: How to reset serial without losing foreign constraints

The only way is to insert a value of 2^31-1 then insert zero which will cause the serial column to wrap back to '1'. Once it is at '1' you can use the insert trick to increase it to whatever value you wanted.

Art