Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed?

Question

What happens to the foreign key relationships on a table if that table is renamed?

Answer 1

Renaming a table does not change the underlying object_id for that table. Foreign Key relationships are tracked using object_id's.

Take a look at the information in select * from sys.foreign_keys.

Assuming the following parent/child tables

DROP TABLE IF EXISTS [dbo].[QueueDatabase];
DROP TABLE if exists [dbo].[Queue];

GO
CREATE TABLE [dbo].[Queue](
    [QueueID] [int] IDENTITY(1,1) NOT NULL,
    [SchemaName] [sysname] NOT NULL,
    [ObjectName] [sysname] NOT NULL,
    [Parameters] [nvarchar](max) NOT NULL,
    [QueueStartTime] [datetime] NULL,
    [SessionID] [smallint] NULL,
    [RequestID] [int] NULL,
    [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED 
(
    [QueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[QueueDatabase](
    [QueueID] [int] NOT NULL,
    [DatabaseName] [sysname] NOT NULL,
    [DatabaseOrder] [int] NULL,
    [DatabaseStartTime] [datetime] NULL,
    [DatabaseEndTime] [datetime] NULL,
    [SessionID] [smallint] NULL,
    [RequestID] [int] NULL,
    [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_QueueDatabase] PRIMARY KEY CLUSTERED 
(
    [QueueID] ASC,
    [DatabaseName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[QueueDatabase]  WITH CHECK ADD  CONSTRAINT [FK_QueueDatabase_Queue] FOREIGN KEY([QueueID])
REFERENCES [dbo].[Queue] ([QueueID])
GO

ALTER TABLE [dbo].[QueueDatabase] CHECK CONSTRAINT [FK_QueueDatabase_Queue]
GO

---

Now, begin a transaction. Select object_id's and object_names before and after executing sp_rename. The object_names change, but the underlying object_id's remain the same.

begin transaction
--select object_id's and names before table rename
SELECT parent_object_id AS child_table_id
    ,object_name(parent_object_id) AS child_table
    ,referenced_object_id AS parent_table_id
    ,object_name(referenced_object_id) AS parent_table
FROM sys.foreign_keys

--rename the table
exec sp_rename 'dbo.queuedatabase','newqueuedatabase'

--select object_id's and names after table rename
SELECT parent_object_id AS child_table_id
    ,object_name(parent_object_id) AS child_table
    ,referenced_object_id AS parent_table_id
    ,object_name(referenced_object_id) AS parent_table
FROM sys.foreign_keys

--rollback

--before

| child_table_id | child_table   | parent_table_id | parent_table |
|----------------|---------------|-----------------|--------------|
| 1100687119     | QueueDatabase | 1068687005      | Queue        |

--after

| child_table_id | child_table      | parent_table_id | parent_table |
|----------------|------------------|-----------------|--------------|
| 1100687119     | newqueuedatabase | 1068687005      | Queue        |

---

Additionally, it should be noted that nothing magical happens to the name of the foreign key (assuming you used table names as part of the foreign key name)

Selecting from sys.foreign_keys reveals that the name remains the same even though it's now a little confusing since there is no table called QueueDatabase after the rename.

FK_QueueDatabase_Queue

Answer 2

Let's assume you have tableA with columns AID, AName and tableB with columns BID, BName, AID and tableB.AID references tableA.AID.

If I rename tableA to zTableA, the foreign key definition for tableB.AID is updated so that it references zTableA rather than TableA

This behaviour can be seen in management studio by scripting the foreign key before and after the table rename