Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 9 of 9 total
Thread Autoincs
Sat, Oct 18 2008 1:31 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

I'd like to request a slight change to autoincs, at least the generated by default ones. The current behaviour is that if you manually enter a value that becomes the start point for the next entry. I'd like it to continue with the existing series. EG

current behaviour

auto 1
auto 2
auto 3
manual 78
auto 79

I'd like

auto 1
auto 2
auto 3
manual 78
auto 4

ie similar to

auto 1
auto 2
auto 3
auto 4  edit 78
auto 5



Roy Lambert
Mon, Oct 20 2008 10:59 AMPermanent Link

"David Cornelius"
> I'd like to request a slight change to autoincs, at least the
> generated by default ones. The current behaviour is that if you
> manually enter a value that becomes the start point for the next
> entry. I'd like it to continue with the existing series. EG

Hmm...  I would *not* like this functionality because I might forget
the higher number is there and as more records are added, eventually
there would be a duplicate key (assuming the AutoInc field is a key
field).

Instead, you can use
 ALTER TABLE ... ALTER COLUMN ... RESTART WITH <seed value>
to reset the auto-generated ID, albeit it can be a bit of work to find
out what that value should be in this case.

--
David Cornelius
CorneliusConcepts.com
Mon, Oct 20 2008 11:10 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

David

>> I'd like to request a slight change to autoincs, at least the
>> generated by default ones. The current behaviour is that if you
>> manually enter a value that becomes the start point for the next
>> entry. I'd like it to continue with the existing series. EG
>
>Hmm... I would *not* like this functionality because I might forget
>the higher number is there and as more records are added, eventually
>there would be a duplicate key (assuming the AutoInc field is a key
>field).

Who cares if you forget Smiley- its up to the system to remember, and if there isn't a collision mechanism built in I'll start worrying now.


>Instead, you can use
> ALTER TABLE ... ALTER COLUMN ... RESTART WITH <seed value>
>to reset the auto-generated ID, albeit it can be a bit of work to find
>out what that value should be in this case.

For what I'm thinking about that just wouldn't be viable.

Roy Lambert
Mon, Oct 20 2008 11:34 AMPermanent Link

"David Cornelius"
Roy Lambert wrote:

> David
>
> >> I'd like to request a slight change to autoincs, at least the
> >> generated by default ones. The current behaviour is that if you
> >> manually enter a value that becomes the start point for the next
> >> entry. I'd like it to continue with the existing series. EG
> >
> > Hmm... I would not like this functionality because I might forget
> > the higher number is there and as more records are added, eventually
> > there would be a duplicate key (assuming the AutoInc field is a key
> > field).
>
> Who cares if you forget Smiley- its up to the system to remember, and
> if there isn't a collision mechanism built in I'll start worrying now.

I care!  I'm a very caring person!  Smile

Seriously, if I just let the collision mechanism catch it, then I have
to build in a lot of extra error-handling code to prevent the situation.

Usually autoincs are for internal IDs with a huge upper limit, so it
doesn't matter if a few are missed.

Here's an short description of how I handle purposeful gaps in
autoincs--I don't know if it directly applies to what you're doing, but
it might give you an idea...

In my replicated application, most tables are synchronized frequently
through the day.  Any of the users can add/delete/edit any of the
records.  The tables all have AutoInc IDs as the primary key, but they
don't have collisions because they all are setup in their own autoinc
range.  There are about 7 users (if you count my 3 test ones) each with
a 10-million autoinc range.  I have a procedure that selects the
MAX(ID) from a table where the ID is within their autoinc range, then
it uses that MAX(ID) to restart the seed value for the ID field.  That
way, if any record ever gets manually entered or imported, their
autoinc field always gets reset to the proper next value for them.

Hope that helps.  Or at least perhaps it was interesting?  Smile

--
David Cornelius
CorneliusConcepts.com
Mon, Oct 20 2008 1:52 PMPermanent Link

Lance Rasmussen

Jazzie Software

Avatar

Team Elevate Team Elevate

Sounds like you may need to go the Oracle workaround for autoinc, for which
Oracle doesn't do autoincs natively.   You have to create a sequence and a
trigger.

Change the type from autoinc to longint.


create sequence test_seq
start with 1
increment by 1
nomaxvalue;

create trigger test_trigger
before insert on test
for each row
begin
select test_seq.nextval into :new.id from dual;
end;

or instead of the trigger, just use the value from the test_seq

insert into test values(test_seq.nextval, xxx,yyy,zzz');


Those are Oracle examples and I've not tested to see how they would work
with EDB.


Lance Rasmussen

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:D302A4F9-0A99-453E-87EA-674C6FBFE92C@news.elevatesoft.com...
> I'd like to request a slight change to autoincs, at least the generated by
> default ones. The current behaviour is that if you manually enter a value
> that becomes the start point for the next entry. I'd like it to continue
> with the existing series. EG
>
> current behaviour
>
> auto 1
> auto 2
> auto 3
> manual 78
> auto 79
>
> I'd like
>
> auto 1
> auto 2
> auto 3
> manual 78
> auto 4
>
> ie similar to
>
> auto 1
> auto 2
> auto 3
> auto 4 edit 78
> auto 5
>
>
>
> Roy Lambert
Mon, Oct 20 2008 2:34 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< I'd like to request a slight change to autoincs, at least the generated
by default ones. The current behaviour is that if you manually enter a value
that becomes the start point for the next entry. I'd like it to continue
with the existing series. EG >>

I think you're going to have to roll your own sequencing with a table in
order to do this.  I don't think we can just change the existing behavior,
although I may be able to look into adding an option for this during the
column creation/alteration.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tue, Oct 21 2008 3:50 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim


OK, I thought that might be the answer.

Roy Lambert
Tue, Oct 21 2008 3:55 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Lance


Thanks for the example. What I'll probably end up doing is what I've done before and have a table which I use to manage incrementing the record number.

Roy Lambert
Tue, Oct 21 2008 3:55 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

David


>Hope that helps. Or at least perhaps it was interesting? Smile

How about mind stretching?

Roy Lambert

Image