I have a table that has rows in it. I am using a SQL Database Project to manage the DB, and I want to upgrade the schema by adding a new NOT NULL column to this table, that comes with a FK constraint on to a new table (also included in the new version of the project).
Is this possible? I know I can use the option to generate smart defaults for the new FK column, but that is of no help because I have no opportunity to put any rows into the new table that is being referenced by the FK.
What options do I have? Because the pre-deployment script executes AFTER the schema comparison is made, I can't create the new table here because the comparison runs first and determines that it needs to create the table - this blows up when it gets to this point in the script and finds that the table already exists (created during pre-deployment)
Our solution:
In pre-deployment script create new temp table and move needed data in it. In post-deployment script we read data from this temp table and update other tables. So, you does not interfere database deploy and has all data you need to restore references. And yes, I agree, it's ugly.