如何更改外键名称？

Question

我当前正在SQL Server数据库中创建新表。我重命名了旧的表，也重命名了外键约束。

以Adventureworks DB为例，SalesOrderDetail表具有FK_SalesOrderDetail_SalesOrderHeader_SalesOrderId (它链接到SalesOrderHeader表)。

更改这个外键的名称就足够了吗?或者我还需要更改SalesOrderHeader表中的外键引用吗？

Answer 1

只是名字--它不会以任何方式改变关系。

EXEC sp_rename 'Sales.FK_SalesOrderDetail_SalesOrderHeader_SalesOrderId', 
                'new_name', 
                'OBJECT';

注意: AdventureWorks中的外键在Sales模式中，因此object_name参数在上面的过程调用中是模式限定的。

Answer 2

SELECT Object_name(constraint_object_id),
       Object_name(parent_object_id),
       (SELECT name
        FROM   sys.columns
        WHERE  object_id = parent_object_id
               AND column_id = parent_column_id),
       Object_name(referenced_object_id),
       (SELECT name
        FROM   sys.columns
        WHERE  object_id = referenced_object_id
               AND column_id = referenced_column_id),
       'sp_rename ''' + Object_name(constraint_object_id) + ''', ''con_fk_' + (SELECT Lower(name)
                                                                               FROM   sys.columns
                                                                               WHERE  object_id = parent_object_id
                                                                                      AND column_id = parent_column_id) + ''',''OBJECT'''
FROM   sys.foreign_key_columns