I need to change a tables unique ID column

Not sure the best way to do this or if there is even a single best answer but here is the problem:

I have a SQL Server 2000 databa开发者_StackOverflowse that consists of at least 13 tables. Some of the tables have a unique id (SysName) so that there can only be one unique SysName value (alpha001, alpha002, alpha003, etc).

With me so far?

Now, the client is wanting to be able to insert multiple sysnames values in the database (alpha001, alpha001, alpha001, alpha002, alpha002, alpha003, alpha003, alpha003, alpha003, alpha003, etc). On top of that, if you change the value for a sysname (alpha001 to beta001) in one table, the value in some of the other tables are also updated (I'm having a brain fart about what this is called at the moment).

I'd also like to add a new column (SysNameID) and set it to autoincrement. What is the best way to do this so that it will go through the database and just add the SysNameID values (1,2,3,4,5,6...) so that I do not have to rebuild the table?

I'm thinking I need to add a SysNameID column to all the affected tables and set the (brain fart term) between the tables to retain the correlation (for data integrity purposes).

If this is request is unclear, please leave a comment and I'll do the best I can to answer it. Some of you guys are very smart so you may have to dumb it down for me. :)

Basic process:

Make sure you have a current backup and do not attempt to do this on prod without a through test on develpment first. This is so extensive a change, you might want to restore prod to a new dev instance because it will be time-consuming and tricky and other development will be interfered with while you do this.

You add the identity column to the parent table called SysNameID (see @marc_s' answer for details)

You add an int column to each child table also called SysNameId. It is not an autoincrementing column and must allow nulls.

You Update this column by using the current sysname columns to find the id assciated with that sysname.

Once all the columns are populated you set the column to not allow nulls and create the foreign key to the parent table. The surrogate keys should not change, so you don;t really need to cascade update.

Finally you drop the sysname column from the child tables and adjust all the code that uses it to join to the parent table and look it up. Alternatively, you rename each child table and create a view that joins the child table to the parent and gets the sysname column from there. That should ensure existing code doesn't break.

There is no simple way to meet your requirement. You are changing the very fundamentals of how your database works. It has the potential to affect virtually every query against the child tables. It may affect reports (which are likely sorted by sysname which is now no longer unique). This is a major change and to do it properly could take months.

Suggested reading: http://www.amazon.com/Refactoring-Databases-Evolutionary-Database-Design/dp/0321293533/ref=sr_1_1?ie=UTF8&s=books&qid=1268158669&sr=8-1

I don't totally understand what your question/challenge is with the current sysname column ....

but for the second part: yes, you can easily add an autoincrement column to your table(s), and no, you won't have to do anything (like "rebuild the table" - whatever that was supposed to be.....) - SQL Server will fill the existing rows with autoincrement values for you.$

ALTER TABLE dbo.YourTableNameHere
   ADD SysNameID INT IDENTITY(1,1) NOT NULL

I usually recommend making that IDENTITY column either your primary (and clustered) key for the table, or if that's not possible, at least put a UNIQUE CONSTRAINT on the column to prevent duplicate values from being inserted:

ALTER TABLE dbo.YourTableNameHere
  ADD CONSTRAINT UC_SysNameID UNIQUE(SysNameID)