Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
azamsharp
Posting Yak Master
201 Posts |
 Posted - 2006-09-22 : 11:40:59
|
| Hi, I created the primary - foreign key relationship on my tables on the developement machine. I also implemented the cascading delete and it is working properly. I created the relationships using the SQL SERVER Diagrams and simply by dragging and dropping. Now, I need to transfer those relationships to the production. The production tables have data in them that is why I am catious about this transfer. Thanks, AzamMohammad Azam www.azamsharp.net |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-22 : 12:04:04
|
| In Enterprise Manager you can right click the database and select All Tasks/Generate SQL Script, then select the tables you want and generate the script for making the changes.I'd recommend that you take a full backup of your production data and restore it on a test server and test the scripts first, because applying constraints to existing tables may fail if there is already data in the tables that violates the constraints. If that is the case you need to fix that first. Also depending on the size of the tables running the ALTER TABLE statements on your tables may be very fast or take quite a long time, so testing will help you predict how much time you need.And then backup production again before you start! |
 |
|
|
azamsharp
Posting Yak Master
201 Posts |
Posted - 2006-09-22 : 12:07:09
|
| Thanks a lot! :)Mohammad Azam www.azamsharp.net |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-23 : 03:57:37
|
| Nice answer snSQL!FWIW we put aSELECT COUNT(*)FROM MyMainTable AS MTWHERE NOT EXISTS (SELECT * FROM MySubTable AS ST WHERE ST.MyPK = MT.MyLookupColumn)at the top of any of our scripts that change/add FKs so that we check for the possibility that there is missing data at the time we rollout. (We have a package and we rollout an upgrade over many months to different clients, so plenty of opportunity for violating data to be added between Test and Rollout even, let alone Dev and Rollout!)Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-23 : 06:35:32
|
| azamsharp: in future you may want to use the Design Tools in Enterprise but instead of saving the changes, as you did, use the "generate change script" tool instead, abandon the changes, and run the script instead.If you keep all such changes in script file(s), in chronological order, you can run those changes on Production to bring it to the same level as Dev.We do this with Dev [including if we need to revert to an earlier backup], QA, Pre-rollout dry-run on Production, and then on Production itself. We also include any "global data changes" in these Patch scripts, as well as some logging - so each script logs its name/version in a logging table so we can see which scripts were run in which order on which day!After the DDL change patch script we also run a separate script of all the SProc/Trigger changes we have made. And then we upSquirt any Meta Data, and at that point the Target database is up-to-date, version-wise.Kristen |
|
|
|
|
|
|
|
|
Transfering the Foriegn Key Relationship