How to change Foreign Key name?

Question

I am currently creating new tables in my SQL Server database. I am renaming the old tables, so also the foreign key constraints.

To use the Adventureworks DB as an example the SalesOrderDetail table has FK_SalesOrderDetail_SalesOrderHeader_SalesOrderId (which links to the SalesOrderHeader table).

Is is changing the name of this foreign key sufficient or do I also need to change the foreign key reference in the SalesOrderHeader Table?

Answer 1

Just the name - it won't change the relationship in any way.

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

NB: That foreign key in AdventureWorks is in the Sales schema so the object_name argument is schema qualified in the procedure call above.

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