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.