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?
Scott PletcherConnect With a Mentor Senior 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.
Nitin SontakkeConnect With a Mentor DeveloperCommented:
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.
Vitor MontalvãoConnect With a Mentor MSSQL 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.