SQL Server Relationships/Foreign Key Definition Suggestions

I realize there is no absolutely correct answer to my question.  I am entering a new area in SQL Server and am looking for input from experienced users.

I’m fairly new to SQL Server.  I have created a DB in Developer 14.  I have written conversion programs to populate the tables with legacy data.  I have defined indexes on the tables as needed.  

Now I want to set up relationships between the tables.  I am doing that using the ‘Database Diagrams’ option in SSMS.

When I link two tables I get a popup to define the relationship further.  The options are:
Enforce Replication
Enforce Foreign Key Constraints
Insert and Update Specific:
           Delete Rule
          Update Rule
There is no documentation or help to explain the significance of each options.  Can someone point me to a resource?

I remember when defining indexes the warning was not to define more indexes than you need because there is a performance and storage overhead with maintaining those indexes.
What is the strategy with relationships?  Should every foreign key in every table have a relationship defined back to the primary table.

For example: I have a ‘tblProperty’ with an identify column of ‘PropertyRecID’.  
Many tables in my database contain the ‘PropertyRecID’ field, representing the record in the property file.  
All of these tables could have multiple records with the same propertyrecID.
Should I define ‘PropertyRecID’ as a foriegn key in every table that contains it?  And then create a relationship from that table to ‘tblProperty’?  If not on what basis do I make the decision to define the relationship, or not.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
SQL Server Books Online https://msdn.microsoft.com/library/ms130214(v=sql.120).aspx is probably the best resource. But it is technically correct and language is user manual like.

If you are not intending to use 'Replication' you can ignore replication related messages.

You can choose 'Enforce Foreign Key Constraint'.

Delete Rule - refers to what happens if the 'Parent' record gets deleted. It is asking 'Should I delete all referenced records from all referenced tables where the parent row value exists'

Same is the case with Update.

With respect to Primary Key Foreign Key relationship, define them WHEREVER possible, in my strictly personal opinion. There are drawbacks but then again every tool we use is essentially a two edge sword.

I suggest you learn the actual script to apply constraints which is way better approach than the graphical one, I think!
alter table [child table]
  add constraint [FK_childtable_childcolumn_parenttable_parentcolumn] foreign key ([child_column]) references [parent_table] ([parent_column])
Nitin SontakkeDeveloperCommented:
There is a scripted way to generate such a script, but I guess that would be too much for you at this stage. Don't mind sharing should you wish to have a look.
Scott PletcherSenior DBACommented:
>> Should every foreign key in every table have a relationship defined back to the primary table. <<

Yes.  I agree with Nitin here, they should.  Also, verify that the data types of the foreign key columns exactly match the original table.  For example, if "PropertyRecID" is a "bigint" in "tblProperty", it should be bitint (not int) in all the foreign tables.

Set "Enforce Replication" to NO.  The odds of you needing it are so low it's not worth the effort to worry about.

On the Delete and Update rules, you need to think about these for each FK and decide what you want to do.

The DELETE rule is potentially extremely dangerous.  
By default, if you tried to delete "PropertyRecID" = 3, and another table had a FK pointing to that key, SQL would not allow the delete and would return an error instead.  If you ALLOW/"CASCADE" on the delete, instead of getting an error, SQL would delete all matching rows in all foreign tables and then delete the main row in tblProperty.
Obviously that could be very bad if you accidentally tried to delete the wrong row.  Therefore, I recommend that you set the DELETE option to "NO ACTION" (fail) rather than CASCADE.

On UPDATE, by default, if you try to update "PropertyRecID" = 3 to =4, and another table had a FK pointing to that key (3), SQL would not allow the update and would return an error instead.  With ALLOW/"CASCADE" on, SQL instead simply updates all the keys to 4.  Key values normally shouldn't change a lot, but when you do need to change on, CASCADE can be extremely useful rather than having to delete all the child rows and re-insert them with the new key (ugh!).

Therefore, I recommend that you leave UPDATE off by default, but that you have code available that can drop and recreate the FKs with "CASCADE", make the key change(s) you need to make, then drop and recreate the FKs with "NO ACTION" again.  If that seems like a lot of trouble, believe me, often deleting and reinserting all the rows is much worse and much harder to do.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
I won't repeat what Nitin and Scott already said because they are extremely correct with their comments.
Just want to add that the indexes should be created after the creation of the PK and FK as those keys should be the first ones to be indexed. Then you can see better what indexes are missing based on the ones already created for PKs and FKs.
mlcktmguyAuthor Commented:
Thanks to all, excellent information.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.