Problem adding a column to an existing table

cjoycecjoyce Posts: 3
edited July 19, 2005 12:35PM in SQL Compare Previous Versions
I want a simple script that adds a new column of type bit to my SQL Server database table. I have version of SQL Compare. After running the script generated by SQL Compare, I notice in Enterprise Manager that the column has been added successfully. However, if I go back and recompare my databases, SQL Compare still reports a difference. This is what the table column looks like:

[checked_out] [bit] NOT NULL CONSTRAINT [DF__tmp_rg_xx__check__2CF2ADDF] DEFAULT (0),

This is what it is supposed to look like and what I compared it to:

[checked_out] [bit] NOT NULL,

Where is the constraint coming from and why is the value so cryptic? I looked at the script that was generated and it has no references to "DF__tmp_rg_xx__check__2CF2ADDF". Also, why is the script for adding a new column so complicated. Couldn't it be done with a simple ALTER TABLE ADD COLUMN command?



  • Hi

    Because this is a NOT NULL it needs to have a default when it is created so a temporary one has been included. It appears that this has been left behind by mistake.

    Please try upgrading to the latest version 3.2 (bundle 3.4) and repeat the compare / sync again.

    Daniel Handley
    Red Gate Software Ltd
  • Hi Dan,

    I am particularly interested in "CONSTRAINT [DF__tmp_rg_xx__check__2CF2ADDF]" - I added the DEFAULT(0) myself because it will be required to update tables with existing data. With that being said, I created a simple script on my own (with SQL Compare) to add a column and executed it. It turns out that SQL Compare still reports a constraint being added to the column so maybe this isn't related to your product. Would SQL Server being doing this? Oddly enough, if I use Enterprise Manager's "Generate SQL" command, it doesn't include this contraint in the column's definition. Any thoughts as to where you are reading this constraint?

  • Hi Chris

    From your original post I assumed that the constraint was added to enforce the data integrity because of the NOT NULL condition.

    If you create a script from Enterprise Manger it may not include all the dependencies as it uses the sysdepends tables that are not always correct. SQL Compare has it's own dependency engine to assist in creating the script.

    Daniel Handley
    Red Gate Software Ltd
This discussion has been closed.