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 Foreign Key Constraints
Insert and Update Specific:
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.