SQL, How to change column in SQL table without breaking other dependencies?

Question

I'm sure this might be quite common query but couldn't find good answer as for now.

Here is my question:

I've got a table named Contacts with varchar column Title. Now in the middle of development I want to replace field Title with TitleID which is foreign key to ContactTitles table. At the moment table Contacts has over 60 dependencies (other tables, views functions).

How can I do that the safest and easiest way?

We use: MSSQL 2005, data has already been migrated, just want to change schema.

Edit:

Thanks to All for quick replay.

Like it was mentioned Contacts table has over 60 dependents, but when following query was run, only 5 of them use Title column. Migration script was run, so no data changes required.

/*gets all objects which use specified column */

SELECT Name FROM syscomments sc JOIN sysobjects so ON sc.id = so.id WHERE TEXT LIKE '%Title%' AND TEXT LIKE '%TitleID%'

Then I went through those 5 views and updated them manually.

Answer 1

Use refactoring methods. Start off by creating a new field called TitleID, then copy all the titles into the ContactTitles table. Then, one by one, update each of the dependencies to use the TitleID field. Just make sure you've still got a working system after each step.

If the data is going to be changing, you'll have to be careful and make sure that any changes to the Title column also change the ContactTitles table. You'll only have to keep them in sync while you're doing the refactoring.

Edit: There's even a book about it! Refactoring Databases.

Answer 2

As others pointed out it depends on your RDBMS.

There are two approaches:

• make a change to the table and fix all dependencies
• make a view that you can use instead of direct access to the table (this can guard you against future changes in the underlying core table(s), but you might loose some update functionality, depending on your DBMS)