We have a development database, a test database, and production. The only way for data to enter one of these three systems is via an SSIS package, which means that for the last month of development, production has been stable, with no additions made. We keep, as far as data is concerned, the three systems in sync. However; our data modeler made suggestions that I rolled into development on the existing structure of our tables. I rolled this into test, with the new data we will be using going forward. We'd like to roll these tables from Test directly into production. What I'm wondering is if the constraints that I've set up in Test will go with the table or will I have to set them up in Production?

  • How exactly are you promoting the table from test to production? – BradC Jul 27 '17 at 13:39
  • Well, honestly, I don't know. Someone else will be doing this for me, so I'm not sure. I just wanted to understand how it'll work. I probably should have explained that better. – DataGirl Jul 27 '17 at 14:36
  • 1
    Then the answer is "it depends on how they promote the tables from test to production". I would just let them know of your concerns that these constraints get promoted at the same time. – BradC Jul 27 '17 at 14:41

Yes, in SQL Server constraints are defined within each database within the table definition. So if you physically promote a database to a higher environment through backup and restore, the constraints will remain within the database. If you script out objects, the default behaviour will be for the constraints to be scripted out with the table definition.

Microsoft's TechNet has a decent explanation: https://technet.microsoft.com/en-us/library/ms189862(v=sql.105).aspx

| improve this answer | |
  • This is a terrific answer. Thank you very much. The link to TechNet is very helpful. – DataGirl Jul 27 '17 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.