Deployment Fails: Column Drop Blocked by Its Own Default Constraint

Question

Please see error below:

Altering [sf].[Person]... (94,1): SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 1 The object 'df_Person_IsPartialBirthDate' is dependent on column 'IsPartialBirthDate'. (94,1): SQL72014: .Net SqlClient Data Provider: Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN IsPartialBirthDate failed because one or more objects access this column.

This error occurs on deployment.  My scenario is that I have dropped a column from the design of the "sf.Person" table.  That column has a default constraint.  The SSDT deployment fails on the error. 

This seems like a bug.  Shouldn't the tool determine the correct sequence to drop the default constraint first if the column is also being dropped? 

The design of this table is in a dacpac referenced to the main project. 

Thanks

Answer 1

Hi,

I tried to reproduce this issue but couldn't. I had the schema 'sf' in one project, and the table with default constraint in a separate project that references the first project. After dropping the column with the default constraint using table designer and deploying my changes, the default constraint was dropped before the column so the deployment succeeded. Could you tell us a little more about your database schema, including what the table references in the main project, and whether the two projects point to the same or different databases?

---

-Genevieve Orchard (SQL Server Data Platform team)

Answer 2

Thanks Genevieve for looking into this.  After running the same sequence you did above - which worked for me also - I did some more investigation and have found that the problem is a more subtle than the way I first described it.  I will set up a test case with clear steps and post that (might take a day or two).

Thanks

Answer 3

Hi Kazoo,

Any luck in finding clear repro steps for us to try?

Answer 4

Sorry for the delay and thanks again for looking into this.

I have found this problem can occur in multiple ways but the issue always come down to a change in an object in a dacpac file referenced in the main project.  It can be consistently reproduced.

In my original post the problem occurred on a default constraint established on a column that was later dropped in the referenced dacpac project.  In the steps below, the problem is demonstrated in a change to a FK relationship in a project referenced in a dacpac. 

You will need 2 projects - let's call them Project A and Project B.

1.  Create ProjectA.  It doesn't actually require any content but a HelloWorld sproc will give it something of its own to verify deployment with.

2. Create ProjectB.  Create 2 tables in a schema.  Use a schema other than DBO for consistency with my scenarios, however, it is possible the error occurs in DBO also.  In my example the schema name is "sf". 

3. Create sf.TableB1 which can have any structure.  Create sf.TableB2 can have any structure but needs a primary key.  In my example I used an identity column for the PK.

4. Add a FK so that sf.TableB1 is parented by the PK column of sf.TableB2.  Add an index on the FK column in sf.TableB1.

5.  Create a dacpac from Project B.

6. Open Project A and add a database reference to the Project B dacpac.  Use the "same database" option.

7. Publish Project A  (no errors should occur at this point)

8. Open Project B and drop table sf.TableB2.  Remove the FK column from sf.TableB1. Create a new dacpac.

9. Open Project A - update the DB reference to use the new dacpac file.

10. Publish project A.

While my table names are different than those above, an error message simliar in structure that provided below should occur. 

…
Altering [sf].[ConfigParam]...
(86,1): SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 1 The index 'ix_ConfigParam_ConfigParamGroupSID_ConfigParamSID' is dependent on column 'ConfigParamGroupSID'.
(86,1): SQL72014: .Net SqlClient Data Provider: Msg 5074, Level 16, State 1, Line 1 The object 'fk_ConfigParam_ConfigParamGroup_ConfigParamGroupSID' is dependent on column 'ConfigParamGroupSID'.
(86,1): SQL72014: .Net SqlClient Data Provider: Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN ConfigParamGroupSID failed because one or more objects access this column.

Even though ProjectB is valid when it is published through the reference in ProjectA its deployment is blocked. The dependent objects - in this case an index and a FK that no longer exist in ProjectB or its' dacpac prevent the deployment from succeeding.

We are wondering if this might be considered a bug.  It seems the problem could be avoided if the dependencies to the dropped object - in this case the FK column - were also dropped when the publish script is generated.  This kind of dependency analysis does occur within the main project – but not in the referenced dacpac  (or at least that is what appears to be happening to us.)

Thanks again for looking into this.

Answer 5

Hi Kazoo,

Sorry for the delay in getting back to you. Thank you SO much for the detailed and accurate repro steps. We were able to reproduce this behavior, and yes, you have found a bug. We have logged this bug in our system and the problem will be fixed in a future release. Again, many thanks for reporting this.

Genevieve

---

-Genevieve Orchard (SQL Server Data Platform team)

Answer 6

Hi,

I was looking at this bug.  If you turn on “drop objects in target” you will get the correct behavior.  You will need to turn this option on anyway to get that table dropped.  When I do this I have the following output:

Dropping [sf].[TableB1].[IXTableB1]...

Dropping FK__TableB1__c1__117F9D94...

Dropping [sf].[TableB2]...

Altering [sf].[TableB1]...

Update complete.

---

Jamie Laflen, Developer, Microsoft