Foreign key issue if comparing against snapshot

ballrod

It appears that if you rename a primary key, foreign key constraints that are depending on the primary key aren't being included properly in the change script if you are comparing against a snapshot. If you are comparing against another db it is fine (we had to create a db and set it up from the snapshot).

1. Started with:
Table A - Primary key named PK_NOT_RIGHT
Table B - Has a column with a foreign key setup to PK_NOT_RIGHT

2. Create snapshot "Initial"

3. Now, rename the primary key on Table A to PK_TABLE_A

4. Create a change script (update.sql) between the db and snapshot "Initial". You will notice that it isn't dropping the foreign key on Table B.

At this point, if you create a db based on snapshot "Initial" and try running change script update.sql, it will error out when dropping the primary key PK_NOT_RIGHT due to the foreign key dependency on the primary key.

However, if you compare your updated db with the db you created from your "Initial" snapshot, it will correctly recognize the dependency and drop the foreign key constraint prior to dropping PK_NOT_RIGHT.

Let me know if you have any issues reproducing this.

Thanks.

Brian Donahue

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 */

ballrod

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?

Brian Donahue

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

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?