Porting from Autonum to Integer (Primary Key)

How to recreate the Primary key as an Integer

Page 1 of 1

3 Replies - 800 Views - Last Post: 12 June 2008 - 03:52 AM Rate Topic: -----

#1 Nolan  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 52
  • Joined: 12-September 07

Porting from Autonum to Integer (Primary Key)

Post icon  Posted 09 June 2008 - 02:35 PM

Ok here's the deal:

Existing database with 5k + records.
A couple of issues:
First, the Primary key field for Tbl_Projects is named "File#"
File# is an AUTONUM field.

There is a requirement for all files to be contiguous, so in the event of an accidental deletion (this occurs when the office mgr. goes away for a week or so), a BACKUP copy is opened and all of the data needs to be re-entered in order to not SKIP a number.

Solution:
Create a NEW FIELD (integer)
Run an UPDATE QUERY to calc values from original Primary Key field to New field
Re-assign Primary Key
Rename First Primary key field (OLD_FILE#)
Rename NEW field (File_Num)

Track down ALL relationships and modify / edit

This will be a daunting task, as there are MANY relationships and I don't want to miss one.

Has anyone needed to do this before and is there an alternate to what I'm suggesting?
Thanks.
Nolan

Is This A Good Question/Topic? 0
  • +

Replies To: Porting from Autonum to Integer (Primary Key)

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5901
  • View blog
  • Posts: 12,804
  • Joined: 16-October 07

Re: Porting from Autonum to Integer (Primary Key)

Posted 09 June 2008 - 05:22 PM

"First, the Primary key field for Tbl_Projects is named File# [that is] an AUTONUM field." Fine. However, with the business rule of contiguous order, you've effectively invalidated it's usefulness as a primary key. You should never change a primary key, period.

I'm assuming that the File# field shows up in some public way and someone who doesn't understand databases saw it and the request was made? Rule #1, never show anyone the internal id. It's internal for a reason and should have no function other than to be a static unique identifier.

Solution #1: Make a field in Tbl_Projects called "PublicFile#". Show this field everywhere you currently show File#. Renumber it as you like.

Solution #2: If you really, seriously, want to change your primary key value and impact your entire database for no good reason...

1) create a temporary table (e.g. #renum) with with an AUTONUM field (NewId) and an integer field (OldId).

2) Populate #renum with the current ids, letting the autonum to it's job.
e.g.
insert into #renum(OldId)
	select File#
		from Tbl_Projects
		order by File#



3) "Track down ALL relationships and modify / edit" yep, you must do this. What's more, you will also need to suspend all the contraints relating to this, because a foreign key relationship wont let you change the parent value without a fight. Depending on your database, there should be some kind of data dictionary that you can run scripts against to find them all.

4) For each table you find, update with with values in your temporary table.
	update Tbl_ProjectItem a
		set File# = ( select NewId from #renum b where b.OldId=a.File# )



5) Reenable your constraints.

Note, an autonum field often doesn't like to be changed. There should be a way to override this. For SQL Server, for example, there's a "Set Identityinsert" option, or some such.

Good luck.

This post has been edited by baavgai: 09 June 2008 - 05:23 PM

Was This Post Helpful? 0
  • +
  • -

#3 Nolan  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 52
  • Joined: 12-September 07

Re: Porting from Autonum to Integer (Primary Key)

Posted 09 June 2008 - 06:34 PM

Sooooo.... what I'm hearing you say is..... ;-)

I should probably just lean on them a little bit harder to relax their 'very strong desire' to have contiguous files within the db. There really is no reason for them to be this way, and I have explained it, but I think there may be a bit of OCD going on...
The pk File# simply describes the actual Project, or folder (duo-tang) and is unfortunately not entered manually (obviously). I agree that this id should NEVER have been used for the purpose of storing actual FileNumbers, but it has, and now I'm stuck with it....
I was not looking forward to even the solution I had proposed and was hoping there would be an easier workaround.

Thanks for your comments Oh Bearded One.
Was This Post Helpful? 0
  • +
  • -

#4 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Porting from Autonum to Integer (Primary Key)

Posted 12 June 2008 - 03:52 AM

Yes that is the gist of it.
If the request is one that has some keywords in it, like "must", "tax office", "the law", then you can lean all you want but the implementation has to change.
In that case the work around baavgai proposed will do just fine (without a big chance of screwing up your database) : add an extra field that 'emulates' a consecutive index and work with that. You might have to change some of the software or twiddle a bit with some views etc, but that is a LOT better then compromising the data integrity of your database.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1