Setting a Default for a New Column

[juliajuliabell]

Is it possible to set a default value for a newly created NOT NULL column, without actually setting a default value in the database?

I guess in order to do this the script would have to create the column as NULL, update it with the default value and then alter it to NOT NULL. Or something along those lines.

[David Atkinson]

[juliajuliabell]

For me, just an option to specify a single value when the column is being created would be sufficient. There are scheduled routines in the database that manage the value of the column I am adding, so the value of the column at creation doesn't really matter. It just needs to have a value so I can define the column as NOT NULL.

Julia.

[David Atkinson]

Thanks for that. This is a feature that is close to the top of our priority list so do look out for it in the next major version. In the meantime, the easiest thing to do is to change the columns to take a default, then change them back afterwards.

David

[jheidt]

Couldn't you just put the value you wanted as the default value for the column, run the synch, remove the default value, and then resynch?

[juliajuliabell]

Hi jheidt

Thanks for the reply. I could do that, but I was looking for a solution that was more automated. The more manual steps there are, the more chance there is of introducing an error into the process.

[RyanSchneider]

I would love this feature as well. I have a suite of tables generated by script that I need to upgrade, being able to do this in RedGate would be much preferred to editting the tables manually.

Specifically, there's two ways I would like to see this implemented:

- Add a default to the temp tables created when copying the data.
- Add a default value to the SELECT used to copy data to the temp table (e.g. INSERT INTO [tmp_rg_XXX] ([col1],[col2],[col3]) SELECT [col1], [col2], 0 FROM [OldTable] ...)

That is, I don't think a default should be added to the actual tables. If you want that, then I agree that you should just add the default to the new or old table before doing the compare.

[Random Lengths]

I could really use this feature as well.

I'm making changes to an existing software package. When I start a fix or feature upgrade process, I SQL Data Compare the data into my Dev database, make my changes, including new fields, with defaults, and some NOT NULL.

Because I then Sync back to the existing Prod db when it's ready, I start with SQL Compare, then do the Data Compare second. SQL Compare tries to create the new field as NOT NULL which it can't do with some data.

I would prefer that the default is pulled from the schema, in case it's a formula, but for me it doesn't really matter since I'm most likely going to do a Data Compare right afterwords.

[David Atkinson]

SQL Compare 9.5 can re-use custom migration scripts that have been committed to SQL Source Control 3. This means you can modify your scripts to update the existing values without having to set a default.

http://www.red-gate.com/MessageBoard/viewtopic.php?p=51312#51312

Let us know if this satisfies your needs.

David Atkinson
Red Gate Software