是否可以更改SQL用户定义的数据类型？

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?

我有一堆表使用PK列的用户定义数据类型。是否可以更改此类型使用SQL Server 2005?

1 个解决方案

#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:

一旦使用唯一ID填充新字段,如果您需要移出并替换此表的外键引用,那么我只需更改这些表以包含新字段并使用如下所示的内容:

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)

#1