0

very simple predicament I am in.

Basically I have four tables with primary/foreign keys and cascade constraints something like this.

CREATE TABLE [Board]
(
 [BoardId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [BoardId] )
,[Name] [nvarchar](100) NOT NULL
,[Description] [nvarchar](max) NULL
,[PortalId] [int] NOT NULL FOREIGN KEY ( [PortalId] ) REFERENCES Portals ( [PortalID] ) ON DELETE CASCADE ON UPDATE CASCADE
,[OrganizerId] [int] NOT NULL
,[GroupId] [int] NULL
,[ModuleId] [int] NOT NULL FOREIGN KEY ( [ModuleId] ) REFERENCES Modules ( [ModuleID] ) ON DELETE CASCADE ON UPDATE CASCADE
,[CreatedByUserId] [int] NOT NULL
,[CreatedOnDate] [datetime] NOT NULL
,[LastModifiedByUserId] [int] NULL
,[LastModifiedOnDate] [datetime] NULL
)
GO

It has come to a point where I need to rename the original tables. I have renamed tables in the past using sp_rename oldTableName, newTableName but not tables with primary, foreign, and cascade constraints.

Is it safe enough to rename tables with sp_rename or should I resort to creating tables from scratch, copying data across and then dropping old tables?

iiminov
  • 969
  • 1
  • 15
  • 34
  • 1
    As far as I know constraints are linked on the `objectid` and not the name. This means that you should not have a problem. What will cause a problem is that procedures, functions etc that reference that object. http://msdn.microsoft.com/en-us/library/ms188351.aspx – Jaques Dec 01 '14 at 13:23
  • I already thought about that and converted all my procedures to server side code so that shouldn't be an issue. – iiminov Dec 01 '14 at 14:05

1 Answers1

0

Just to close off the question.

The solution I originally proposed of using sp_rename oldTableName, newTableName worked fine for me.

But as @jaques pointed out you need to be weary of any stored procedures that may be attached to these tables. Because stored procedure work off table names.

iiminov
  • 969
  • 1
  • 15
  • 34