Foreign key issue if comparing against snapshot

ballrodballrod Posts: 3
edited May 16, 2008 12:39PM in SQL Compare Previous Versions
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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 */
    
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    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?
Sign In or Register to comment.