Primary Key Index with Foriegn Key Constraints and v3.1.0

Brian Donahue

Hi John,

Thanks for your post. SQL Compare 3.x does not drop the foriegn key
first before dropping a table when a column in the table has a primary key
constraint and a foriegn key constraint applied to the same column. Even
scripting the table at the other end of the FK relationship along with the
first table won't help in this case. We've identified this problem about a
week ago and hope to have it fixed soon.

Thanks for your understanding.

Yours,

Brian Donahue
Red Gate Technical Support



"John P" <jplamon@grneam.com> wrote in message
news:1gIInR62DHA.1248@server53...
> We recently upgraded from SQL Compare 1.6 to v 3.1.0.151. The speed gains
> were very impressive!
>
> While running through some compares we found that a previously supportted
> situation is no longer [seemingly].
>
> The table is basically the same across two DB's except for the index on
the
> primary key. The primary key is also a foriegn key contraint for another
> table. The script that gets generated does not work. Our current work
around
> is to just implement the change manually. Is there something that we are
> missing to get this to work via script or is this no longer supportted?
>
> Details below:
>
> Much Thanks,
> John
>
>
> Compare Results (Just the part that was different)
> --DB1

---

> ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY
> CLUSTERED ([MyTableKey]) WITH FILLFACTOR=100
> GO
>
> --DB2

---

> ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY
> NONCLUSTERED ([MyTableKey])
> GO
>
> --Script to make DB2 like DB1 ---
>
> 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
> ALTER TABLE [dbo].[MyTable] DROP CONSTRAINT [PK_MyTable]
> GO
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
> GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
> TRANSACTION END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ARITHABORT ON
> GO
> ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY
> CLUSTERED ([MyTableKey]) WITH FILLFACTOR=100
> GO
@TRANCOUNT&gt;0 ROLLBACK TRANSACTION
> GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN
> TRANSACTION END
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
> GO
@TRANCOUNT&gt;0 BEGIN
> PRINT 'The database updated succeeded'
> COMMIT TRANSACTION
> END
> ELSE PRINT 'The database update failed'
> GO
> DROP TABLE #tmpErrors
> GO
>
>

---

>
> --Results --
> Server: Msg 3725, Level 16, State 1, Line 1
> The constraint 'PK_MyTable' is being referenced by table 'MyOtherTable',
> foreign key constraint 'FK_MyOtherTable_MyTable'.
> Server: Msg 3727, Level 16, State 1, Line 1
> Could not drop constraint. See previous errors.
>
> (1 row(s) affected)
>
> Server: Msg 1779, Level 16, State 1, Line 1
> Table 'MyTable' already has a primary key defined on it.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
>
> (1 row(s) affected)
>
> The database update failed
>
>
>

Brian Donahue

This issue was cleared up in version 3.15.