Solved

Team foundations (SSDT) Index generating constraint

Posted on 2013-01-04
15
355 Views
Last Modified: 2013-01-08
I am working with SSDT 2012 and Team Foundations 2012 source control. I have created an index on my dev server and I am attempting to update the Project in TFS.  One issue I ran into is that SS creates a constraint and FK which I did no realize until now. But I am trying to find out in I need to include the constraints in  the check-in or will SS be smart enough to build them when the Prod server is updated.  I'm kinda new to TFS with SQL  Server So any help/ advise would be appreciated.
0
Comment
Question by:rschmehl
  • 8
  • 7
15 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744154
I wouldn't worry about losing the PK or FK constraints in TFS.  Those sorts of constraints are pretty high priority in any database design.  To verify this, upload the project to TFS then pull it down to another computer (if one is available).  At that point you can check to make sure those constraints are in-tact.
0
 

Author Comment

by:rschmehl
ID: 38744225
So you are saying that if I remove them (Exclude them) in the TFS update / checkin that  when the code is generated in Prod they will be rebuild?    
I do not have any way yet to see if what you suggust is true, although I  think it is.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744237
Why exclude them?  I thought you just wanted to store the project in TFS.
0
 

Author Comment

by:rschmehl
ID: 38744394
OK, when I do a Compare ( Dev ss to Project ) and get the differences I go though the list and exclude every thing , then i go bak through and add the index I want.  after that I do an update, which has been showing me things which I did not know where they were coming from.  O, and when I check the index I also check the table to add it.    This may be wrong or I may be missing saomething.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744520
It seems that your question is more of..."do I need those constraints?"  If you want them, then include them explicitly.  If not, then exclude them.  My guess is that the design you've given SSDT requires these constraints even though you didn't expressly add them yourself.  May I ask which tables and fields are getting those constraints?
0
 

Author Comment

by:rschmehl
ID: 38744625
To answer your question, Yes,  When I added the indexes to my dev SS and tested I did not realize constrants were added by SS.   (Just did not think about it )  but yes in TFS at the update level I ADD the Table and Index and TFS  Includes the Constraint  in the create code.  As to your question on which tables it is the one(s) I want to add the new index too.

I'm fairly new to DBA world and especially TFS, as if you couldn't tell.  But yes , do i need the constraints, when i tested the load process in my Dev envorinment I had no issues with data.  Am I missing a key concept or something?
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38744711
Are you familiar with Primary Key and Foreign Key concepts?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rschmehl
ID: 38744780
yes
0
 

Author Comment

by:rschmehl
ID: 38744790
When I added the Indexes I did not notice the contraints being put on the DB.  So when I went to team to promote the indexes to stage ( which I have not dooe in a while) I noticed the constraints in the code. )
 So I know they are there to preserve referential integraty but my concern is messing up TFS.  do i add the constraint or do I hope TFS is smart enough to create themwhen it promotes the code?
0
 

Author Comment

by:rschmehl
ID: 38750924
Did i say something wrong?
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38751206
No, not at all.  I'm sorry.  Just keeping up with my day job and had a crazy busy weekend.  Back now and I hope I didn't delay you too much.

So, If we understand how the constraints will work and agree with the system for putting them in, then I'd formally add the constraints yourself.  That way, we won't even need to worry about what TFS will do (it will of course just preserve what you hand it).  You'll just know that those constraints will now be part of your overall design.
0
 

Author Comment

by:rschmehl
ID: 38751526
Thank you, me too,  But I think I'm seeing daylight on this issue   I only added indexes on my dev server and when i compared to the project to do my update I excluded everything and then just added the index and table  Since then i have noticed that someone before me added those constraints. So when iexcluded them it messed me up and now i need to rollback a few items.   but I saw all kinds of stuff in cyber land on this including auto generated constraints,grants .  Im off sick today but i will check tommorow.  I know this may sound confusing to you, as it does to me  So let me just ask one more thing to clarify :   if all i want to do(after testing) is add the indexes I should be able to do this without having to add constraints ????.  similar to adding missing indexes script ?   I truly appreciate your help.  you get all the marbles.  thanks
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 500 total points
ID: 38751671
Sure. You can do that.  Add the constraints and put it into TFS.  Then when you add indexing you'll be able to do so without effecting the constraints or the integrity of the system.
0
 

Author Closing Comment

by:rschmehl
ID: 38755623
Thank you  appreciate the advise.
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 38755641
Thanks for the points.  Feeling better yet?
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Viewers will learn how the fundamental information of how to create a table.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now