SQL Deploy / Compare : The object 'CONTRAINT_NAME' is dependent on column 'COLUMN_NAME'

girish128

Hi,

We're using Redgate Deploy for SQL Server deployment. 

The script generated with tool is somehow throwing following error:

Altering [dbo].[Table1]

The object 'DF__Table1_I__SysSt__0333DC13' is dependent on column 'ExcecutedOn'

  
The change that we have in differential script is that we're dropping a column 'ExcecutedOn' on 'Table1'. 

The table definition has column defined as follows:
    [ExcecutedOn]             DATETIME2 (7) CONSTRAINT [Table1_ValidFromConstraint] DEFAULT (sysutcdatetime()) NOT NULL,

So the thing is column has one system generated constraint and I can't control script generated through Redgate tool to remove constraint first and then execute Table definition change.

We're executing scripts in following sequence:
PreDeployment scripts (for data clean up and other stuff)
Redgate tool generated script (for schema related changes)
PostDeployment scripts (for data clean up and other stuff like master table records insert/update/delete)

Any suggestions to resolve this ? 

Thanks,
Girish

DanC

Hi @girish128

Could you please provide me with some additional context on this issue, such as why the constraint cannot be removed temporarily with your existing Pre-deployment script, then reinitialized by a Post-Deployment script

girish128

Hello @DanC,

Thanks for the reply. 
I'm not completely aware about why but in general, we've been asked to use SQL server project when it comes to change the schema and other db model related stuff.

Anything included in pre deployment and post deployment folder are mostly data scripts like modifying or adding new records to tables.

With our current setup, even if I include any schema changes in pre or post, the scripts won't work. That includes, alter, create table and other DDML stuff.

I'm not worried about enabling the constraint since I want to remove that column.

In similar scenarios of this in past, our DBA generally manually modify the redgate generated script to move forward but I feel there must be better way.

Thanks,
Girish

DanC

Hi @girish128

Thanks for the update!

I think it would be good to take a step back here, because scripts not working for any schema changes is a pretty fundamental issue that would likely undermine our troubleshooting.
 

In this case it would be good to get a copy of some of the scripts that are having issues, to avoid sharing potential private information, I'm going to reach out directly via a support ticket.