a problem with an auto incremental field

[Bertiethedog]

My predecessor on this job liked auto incremental fields which I always understood were supposed to be a unique field to help with updates.

Unfortunatly the rest of the system is using them as a references so the value in the auto incremental field appears in other tables

Now I have a problem
My customer wants the ability to archive some records (easy) but then the ability to bring them back which at the moment would change the value in the auto incremental field.

I was wondering if I could change the field into a standard integer field and then write some code for the insert trigger. I could then disable this by creating a variable when I was appending in archived records so as not to change the value.

Are there any obvious pitfalls?

Is it possible to disable the autoincrement on the fly

Further info there are 162000 records in the table

 

[Mike Lewis]

Hi Bertie,

Is it possible to disable the autoincrement on the fly

The easy answer is ... yes. Use ALTER TABLE to change the data type of the field in question. Change it from INTEGER AUTOINC to INTEGER. Then do the reverse to change it back.

However, when you change it back, you must also specify the NEXTVALUE. You must make this one higher than the highest value of the field that is now in the table.

Also, you will need exlusive use of the table to do this.

Now, having said all that, I would question whether this is the best way to go about this.

My understanding is that you want to fix the autoincs in this way because you are deleting records (when you archive them), and you might later re-instate them. You want the reinstated records to have the same keys as before they were deleted, because there are records in other tables that reference these fields. Is that right?

If so, the design is wrong. If you are deleting records which other tables reference, then you should also be deleting the referenced records. For example, if you are deleting a customer, you should also delete the customer's invoices. When you reinstate the customer, you are reinserting it into the table, and this should generate a new key. If you are also reinstating the invoices, you can set the appropriate value in the customer ID field at that time.

If you've got a good reason not to do it that way, then maybe you shouldn't be deleting a record when you archive it. Perhaps you should just set a flag to say it's archived, and change your user interface so that archived records are hidden. That way, you can ignore the whole issue of autoinc keys.

Just something to think about.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Bertiethedog]

Hi Mike

I fully agree with you, I understood that auto increments were to assist with updates and views

The project is too far advanced to redesign this part of it.

I think I might try your alter table idea

I wouldn't normally use this method, as the problem will come back when the table gets big again

If I find the next auto increment value alter the table to a plain integer and reimport. I will have to investigate what will happen when it is altered back to an autoincrement. Will it try to reincrement the whole table

 

[TamarGranor]

I fully agree with you, I understood that auto increments were to assist with updates and views

From your description, your co-worked is using auto-increment fields exactly as they're intended, to create primary keys for records that can then be referenced in other tables as foreign keys.

Mike's right that you shouldn't be removing data from one table that's linked in another. You need to rethink this requirement and figure out what you really should be doing.

Tamar

 

[Bertiethedog]

Hi Tamar

Surely this turns any data transfers into a nightmare?
It certainly has in this instance, I am not too sure about archive files either because I think the problem always come back later.

My customer thinks its the solution so I have to look at it as he is paying the bill

Rich

 

[Olaf Doschke]

You want to archive some portion of the data, delete it but be able to restore it? I hope you archive respecting the referential integrity, eg parent and child records belonging together.

The big problem is autoinc fields are read only and the only chance to set values is by insert or append.

Alter table is the only official way to change from autoinc to a normal integer. That's not possible as long as you don't have exclusive access on the table.

The information about the field type is in the table header along with the next autoinc value, so a hack would be to modify the table header with low level file functions.

Maybe you change from autoinc to a normal integer with a stored preocedure to create the nextid as the default value of that field. That would be the ideal solution, as you then have write access, better yet make it non integer keys liek GUIDs.

Bye, Olaf.

 

[Olaf Doschke]

>The big problem is autoinc fields are read only and the only chance to set values is by insert or append.

I meant: inserts or append without explicitly setting that field, so the only chance to set the value is the automatic mechanism.

Bye, Olaf.

 

[TamarGranor]

Why would it turn data transfers into a nightmare? I don't see what the issue is.

Maybe that's because the task you're describing sounds like something that shouldn't be done as described. Removing records from a parent table without changing the child table is just a bad idea.

Tamar

 

[Bertiethedog]

Thanks everyone for there replies, I have been contracting for 15 years but this is the biggest and most complex system so far.

I am constantly reading in tables or importing parts of tables. The autoincrements are a PIA I have to disable the receiving table import then work out the max value and renable.

When you have 6 or 7 tables to do and 20 people on stop it causes a little bit of stress.

I certainly wouldn't use them as primary keys excellent for updating views etc but I would prefer to write a few lines of code to create my own key field.

Maybe I am a dinosour but I thought good code had to be easily maintained.

Rich

 

[Mike Lewis]

Richard,

I think you are blaming Autoinc keys for a problem whose roots lie elsewhere.

The fact is that, as soon as you create a relationship between records in two tables, you can't delete records from the parent table without considering the effect on the child table. In your case, the situation is complicated by the fact that you might also need to re-insert the deleted records at a later date.

The real problem - and the solution - lies in the logic behind the process. You would have the same difficulties regardless of whether the keys are Autoinc or are generated in some other way.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[CajunCenturion]

==> You would have the same difficulties regardless of whether the keys are Autoinc or are generated in some other way.
I respectfully disagree with that. The problem is not in how the key values are generated, but when the key values are generated, and that new key values are being generated as part of the recall process. A GUID wouldn't have such a problem because it's not re-assigned when a record is re-inserted into a table.

It's a major design flaw to use autoincs as keys in this environment, or in any distributed database environment where records are being moved around. In fact, generally speaking, autoincs are not good choices for keys when other candidate keys exist.

Be that as it may, you have to deal with the situation that you have, and I think you're on the right track to disable the autoinc process while data migration is taking place. Once that's in place and you have time to deal with the design flaw, I would try to determine what stable candidate keys exist and as you can, change the code to use these other keys for the relationships.


--------------
Good Luck
To get the most from your Tek-Tips experience, please read
FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Olaf Doschke]

You don't have a problem with autoinc, if you don't delete the exported records, as long as they remain, you can simply recall and or update the existing records.

The problem with reimporting data into records with autoinc fields is simply the read only state of those fields, which means your ids will change.

The solution might be to live with that key change and make a cascading update of foreign keys. Those by theri nature as foreign keys must be simle integer fields.

Bye, Olaf.

 

[Bertiethedog]

Thanks everyone your comments have been usefull


Rich