2

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)

Simon Green
  • 1,131
  • 1
  • 10
  • 28

2 Answers2

3

There are 3 approaches I have seen:

  1. Use a pre-compare script which does what you want before running the sqlpackage.exe (or whatever you use to deploy), it works pretty well and is normally simple to add an extra "run this script" before the compare (https://the.agilesql.club/Blog/Ed-Elliott/Pre-Compare-and-Pre-Deploy-Scripts-In-SSDT).

  2. Do it in a phased deployment, so in the first release you add a default constraint and correct the existing data and then in a later release enforce it with a not null constraint

  3. You could write your own update with a deployment contributor.

Personally I use 1 and 2, option 3 seems a bit like overkill (p.s. I haven't forgotten about your table re-write issue)

ed

Ed Elliott
  • 6,666
  • 17
  • 32
  • Hi, yes so far I have gone with option 1. I can't use the right-click-> publish option in VS anymore, I have a .bat file and I run the command prompt against it. It's a real shame you can't do this in a more VS integrated way, I feel this is a serious omission in functionalty.... but for now it looks like this is the right (the only) answer – Simon Green Sep 28 '15 at 20:05
  • Hi - I do something similar, I use a powershell script to drive sqlpackage.exe and one of the quick deployers to help with red, green, test, dev otherwise a full build and deploy is too slow for me. The powershell script is the same one I use in CI/Prod to deploy or generate scripts sp they get very well tested :) – Ed Elliott Sep 28 '15 at 20:59
  • See Backs' answer below - it turns out the post deployment script runs BEFORE the FK Check constraints are imposed. So I can just set up the records in my new table and the values in my FK column here, before the FK constraint is checked... – Simon Green Sep 29 '15 at 06:26
  • cool, yes it looks like constraints get set to NOCHECK and then back to CHECK after the post deploy script, that is really useful :) – Ed Elliott Sep 29 '15 at 08:15
3

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.

Backs
  • 24,430
  • 5
  • 58
  • 85
  • I don't think that will work. The actual publish process will still try to 1/ create a new table 2/ create a new column on the existing table and 3/ create an FK from the new column to the PK on the new table. This will fail because, even with smart defaults, the FK will not be satisfied because the new table in 1/ is empty – Simon Green Sep 28 '15 at 20:03
  • @SimonGreen it works, database project checks constraints (and foreign keys) at the and of deployment after your scripts – Backs Sep 29 '15 at 01:16
  • Good grief, so they do!!! Yes, this changes everything. I must have just assumed that a post deployment script ran after everything in the main publish script... but yes, I can just insert the records I need in the new table and set up the values I need in my new FK column and this all happens before the FK constraints are imposed.... simple! Thank so much – Simon Green Sep 29 '15 at 06:25