Options
Problem adding a column to an existing table
cjoyce
Posts: 3
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!
[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!
This discussion has been closed.
Comments
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
Red Gate Software Ltd
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
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
Red Gate Software Ltd