Foreign key issue if comparing against snapshot

• 
  Hi Roderick,
  
  I'm sorry to say I couldn't duplicate the problem, at least not on version 6.2.0.271. Here is the script that I had used to create the sample database:

  USE [ILikeToRenameThings]
  GO
  /****** Object:  Table [dbo].[tableA]    Script Date: 05/07/2008 16:17:07 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[tableA](
  	[ID] [int] NOT NULL,
  	[Data] [varchar](50) NULL,
   CONSTRAINT [PK_NOT_RIGHT] PRIMARY KEY CLUSTERED 
  (
  	[ID] 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
  SET ANSI_PADDING OFF
  GO
  /****** Object:  Table [dbo].[tableB]    Script Date: 05/07/2008 16:17:07 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  SET ANSI_PADDING ON
  GO
  CREATE TABLE [dbo].[tableB](
  	[ID] [int] NOT NULL,
  	[Data] [varchar](50) NULL
  ) ON [PRIMARY]
  GO
  SET ANSI_PADDING OFF
  GO
  /****** Object:  ForeignKey [FK_NOT_RIGHT]    Script Date: 05/07/2008 16:17:07 ******/
  ALTER TABLE [dbo].[tableB]  WITH CHECK ADD  CONSTRAINT [FK_NOT_RIGHT] FOREIGN KEY([ID])
  REFERENCES [dbo].[tableA] ([ID])
  GO
  ALTER TABLE [dbo].[tableB] CHECK CONSTRAINT [FK_NOT_RIGHT]
  GO
  /* Take snapshot at this point */
  EXEC SP_RENAME 'PK_NOT_RIGHT', 'PK_TABLE_A'
  /* Now compare .snp on the left to ILikeToRenameThings on the right */
  

  Brian Donahue May 07, 2008 11:24

  - 0 +
• 
  That's the same version we are using. We also duplicated it on two different machines. So, when you tried it, it was correctly dropping the FK on tableB before renaming the PK?

  ballrod May 12, 2008 14:00

  - 0 +
• 
  Here is the SQL Compare migration script:

  /*
  Script created by SQL Compare version 6.2.1 from Red Gate Software Ltd at 13/05/2008 09:17:02
  Run this script on BRIAN\FORD.ILikeToRenameThings to make it the same as BRIAN\FORD.ILikeToRenameThings
  Please back up your database before running this script
  */
  SET NUMERIC_ROUNDABORT OFF
  GO
  SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
  GO
  IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
  GO
  CREATE TABLE #tmpErrors (Error int)
  GO
  SET XACT_ABORT ON
  GO
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  GO
  BEGIN TRANSACTION
  GO
  PRINT N'Dropping foreign keys from [dbo].[tableB]'
  GO
  ALTER TABLE [dbo].[tableB] DROP
  CONSTRAINT [FK_NOT_RIGHT]
  GO
  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  PRINT N'Dropping constraints from [dbo].[tableA]'
  GO
  ALTER TABLE [dbo].[tableA] DROP CONSTRAINT [PK_TABLE_A]
  GO
  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  PRINT N'Creating primary key [PK_NOT_RIGHT] on [dbo].[tableA]'
  GO
  ALTER TABLE [dbo].[tableA] ADD CONSTRAINT [PK_NOT_RIGHT] PRIMARY KEY CLUSTERED  ([ID])
  GO
  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  PRINT N'Adding foreign keys to [dbo].[tableB]'
  GO
  ALTER TABLE [dbo].[tableB] ADD
  CONSTRAINT [FK_NOT_RIGHT] FOREIGN KEY ([ID]) REFERENCES [dbo].[tableA] ([ID])
  GO
  IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
  GO
  IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
  GO
  IF @@TRANCOUNT>0 BEGIN
  PRINT 'The database update succeeded'
  COMMIT TRANSACTION
  END
  ELSE PRINT 'The database update failed'
  GO
  DROP TABLE #tmpErrors
  GO
  

  Brian Donahue May 13, 2008 04:18

  - 0 +
• 
  In my example, the keys get dropped in the right order and the update script succeeds. Is there another factor that you think may be the root cause of the problem you're seeing?

  Brian Donahue May 16, 2008 12:39

  - 0 +

Add comment

Please sign in to leave a comment.