0
votes

I'm looking to switch to DACPACs for our database changes, but I'm a bit at a loss about what to do when it comes to more complex database updates. To illustrate what I mean, let me use a simple example that has the same problem.

Say I have a Customer table that is currently live and I want to add a new CustomerType table with a foreign key from Customer to CustomerType. The new column in Customer should be required (not nullable), but should not have a default value.

I want to use some arbitrary formula to setup the initial type for the existing customers upon upgrading. How would I accomplish this using a DACPAC?

The DACPAC will only know there's a new column and will try to add it to the Customer table, which will of course fail because it is required. Setting a default value is undesirable, as is allowing null values.

Since the DACPAC should be usable to upgrade from every state to the latest, I don't see what kind of configuration or pre/post scripts I should setup to make this work.

Various searches have produced a disappointing lack of useful results :(

I hope there's someone here that can help out. Thanks in advance.

1

1 Answers

0
votes

The answer will vary a bit depending on how you're planning to deploy the dacpac(s). One common case is having the dacpac replace some collection of T-SQL update scripts that are executed in sequence to update a database schema from one version to the next. In this case you might choose to have one dacpac file for each schema-version of your database and to update a database you would plan to publish the dacpacs in sequence to update a database to the latest version.

In that case, it's possible to use a post-deploy script to fix up the schema as appropriate. For your example scenario, you can model the database in the database project with the new column specified as NULL and without the FK relationship with the new table. Then, in a post-deploy script you can author the T-SQL necessary to execute an UPDATE statement to fill the new table and the new column, an ALTER statement to change the column's type from NULL to NOT NULL, and finally to add the foreign key relationship.

Then moving forward you can remove the post-deploy script and model the new column and table with the proper column type and FK relationship.