What are the challenges you face when working across database platforms? Take the survey
Options

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 3.1.0.151 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?

Thanks!

Comments

  • Options
    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.

    Regards
    Dan
    Daniel Handley
    Red Gate Software Ltd
  • Options
    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?

    Thanks,
    Chris
  • Options
    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.

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