Recently I ran into a customer that was having issues deploying code from their development system to their production system. The issue was that they often found that the deployment script wanted to drop and recreate their keys. This wasn’t something they wanted, and kept feeling like they needed to edit their deployment script to remove these lines.
This post shows how to avoid having the SQL Compare script generate these changes and ignore the constraint issue.
The Scenario
Imagine you have tables in two databases, dev and prod. In this case, I have a SaleHeader and SaleDetail in both the Compare1 and Compare2 databases.
The code for these tables looks like this:
CREATE TABLE SaleHeader
( SaleID INT NOT NULL PRIMARY KEY
, SaleDate DATE
, SaleAmount NUMERIC(10,2)
)
GO
CREATE TABLE SaleDetail
( SaleDetailID INT NOT NULL PRIMARY KEY
, SaleID int
, LineItem smallint
, ProductID INT
, Quantity INT
, UnitPrice NUMERIC(10,2)
)
GO
Making Changes
When the customer makes a change, such as adding a column to the SaleHeader table, they run SQL Compare. This results in something like this:
I see both tables here as different, even though I only added a column to the SaleHeader table. You might think there is a FK or something else, but there isn’t. I only ran this code:
ALTER TABLE dbo.SaleHeader ADD SalesPersonID INT
Why do I see both tables? Let’s click on SaleHeader. I now see the details of the changes. In addition to my column, there is a difference with the constraint. That’s the Primary Key for this table.
If I look at the SaleDetail table, I see the only difference is the constraint.
What has happened is Microsoft generated a system-generated name for the Primary Key because I didn’t specify one. I can specify one, and should, but if I don’t, Microsoft handles this.
Since these are different objects, with different names, SQL Compare flags this. If I try to deploy my new column, this is the script generated. Note, I’ve only showed the change part, not all the setup.
PRINT N'Dropping constraints from [dbo].[SaleHeader]'
GO
ALTER TABLE [dbo].[SaleHeader] DROP CONSTRAINT [PK__SaleHead__1EE3C41F8D88FA16]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[SaleHeader]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[SaleHeader] ADD
[SalesPersonID] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK__SaleHead__1EE3C41FDBE16A10] on [dbo].[SaleHeader]'
GO
ALTER TABLE [dbo].[SaleHeader] ADD CONSTRAINT [PK__SaleHead__1EE3C41FDBE16A10] PRIMARY KEY CLUSTERED ([SaleID])
GO
This code shows that
the constraint is dropped, the column added, and then the constraint rebuilt. Again, with the same system-generated name. That means every script I generate will do this when this table is changed.
The customer was editing this script and removing the ALTER TABLE DROP CONSTRAINT and the ALTER TABLE ADD CONSTRAINT lines (and comments). A pain, and a place where a human can make a mistake.
Let’s fix this.
Change the Options
If I click “Edit Project”, I have a way to flip a switch and prevent this from happening.
After clicking the button, I see the Data Sources tab, where I picked my databases, but in the upper right, I can click the Options item.
This gives me a lot of checkboxes. If I scroll down, I’ll find an “Ignore” section. In here there is an Ignore constraint and index names. I need to check this.
The help on the right side tells me that I can ignore the system generated names. This doesn’t quite work smoothly for scripts folders, as you can read.
Once I click the checkbox and re-compare the databases, the view is similar, though the SaleDetail table doesn’t appear in the list of different objects.
If I generate the script, however, it looks cleaner. No constraint changes.
This is what the client wants.
Summary
Ultimately, you don’t want to have system generated named constraints. Too easy to make mistakes, or someone even manually checking items might think there is a problem. One thing that I recommend is that you slowly rename these constraints to a standard that fits your environment.
SQL Compare has lots of options, and this is just one to make this fit your environment. If you have items that you need customized between certain databases, look through the list of options for something that helps you. Or keep following these tips on the blog.
For this option, if you need to add this in the command line, then use the /icn option.
SQL Compare is amazing and if you need to check what might be changing and happening between your databases, give it a try today.