Is it possible to change SQL user-defined data type?

Question

I have a bunch of tables using user-defined data type for PK column. Is it possible to change this type Using SQL Server 2005?

Answer 1

I would suggest that it is always possible to refactor poor or outmoded database designs, it simply depends on how much work you are willing to go to in order to do so.

If you are looking to replace the user-defined data with a surrogate key then you should be able to simply alter the existing table to contain a non-nullable identity column and this should cause all of the existing records to be assigned a new key automatically.

Once the new field is populated with unique id's, if you need to move out and replace foreign key references to this table, then I would simply alter those tables to contain the new field and use something like the following:

UPDATE child_table 
SET new_fk_val = 
    SELECT new_pk_val 
    FROM parent_table
    WHERE parent_table.old_pk_val = child_table.old_fk_val

Once that step is complete, then you could drop the old foreign key constraint, drop the old foreign key column, drop the old primary key column, establish the new primary key constraint, and then establish the new foreign key constraint.

Of course, if the old version of the parent and child tables relationship was such that you have invalid records in the child table you may have to do something like the following:

DELETE FROM child_table 
WHERE old_fk_val NOT IN 
    ( SELECT old_pk_val FROM parent_table)