Are Relationships Necessary

My hosting servers have "myLittleAdmin" installed. I am new to ms sql I have some experience with Access but I have never set up Relationships inside the database server. I have always just relied on my application logic to call the relations. What is the benefit to setting up relationships in sql, if any?The closer you can put business rules to the hardware itself, the fewer things can go worng ;) enforcing them. If you rely on applications to enforce your business rules, the first application that forgets to apply a rule, or worse yet mis-applies a rule will cause problems. If the database server enforces your rules, you won't store any data that violates the constraints, which means LOTS fewer hours spent trying to figure out "what created the mess that we're in" and more hours on the beach with a margarita in hand!

-PatP|||i assume that you are referring to foriegn key / primary key relationships.
if this is the case, the hands down winner is referential integrity.
this type of integrity from the trinity( domain entity referential) is in many ways the star of relational databases.
it prohibits the modification of a 'PARENT' key value if there are 'CHILD' values present in the referring column.
It also Restricts the entry of 'CHILD' values in a foriegn key column if an appropriate value does not already exist in the 'PARENT' Table.

check out UNDERSTANDING RELATIONAL DATABASES by fabian pascal.
if you can get through the vendor attacks, it's a good book built on sound concepts. (although pat may disagree. he and fabian dont see eye to neck)
also database design for the mere mortal will help you with the process of becoming normal|||scott, the examples of relational constraints you gave are merely the defaults

don't forget you can also define ones like "if you change the value of a primary key, the database automatically takes care of updating all related foreign keys"

;)

let me take a guess at what CreativeLeaf was asking

in access, there's a menu option, Relationships, which, um, creates relationships between access tables

what happens when you do this is that this relationship information will be used not only to enforce RI but also when you build queries

when you drag and drop tables into the workspace in Query Design View, access automatically generates the JOIN syntax for you!

in other words, you (the person building a query) don't have to know a thing about how the tables could or should be joined, because you (the table designer) have already defined that and it's built into the tables

neat, eh?

oh, and of course it does enforce RI too, including the ON UPDATE CASCADE that i alluded to earlier

(see my article Relational Integrity (http://r937.com/relationalintegrity.html) for other types of RI)

fast forward to the day you want to do the same in sql server

RI is declarative, so you set up your primary and foreign keys properly using CREATEs, and you get the same benefits (i.e. relational integrity, your tables will never be out of sync with each other)

but does this help you when building queries? nup

you still have to know the relationships if you want to write queries against the database

access is such a great database system

i love it!!

:cool:|||scott, the examples of relational constraints you gave are merely the defaults

don't forget you can also define ones like "if you change the value of a primary key, the database automatically takes care of updating all related foreign keys"
We all know access, I use it quite a bit to build the erd and work out issues with queries.

But why should i mention cascading updates or deletes?
his question was [What is the benefit to setting up relationships in SQL, if any? I figure the required protection of the enities is much more important than cascades. So, I wouldnt say the benefit of relations is to cascade their changes. however, in the spirit of fairplay, i am willing to meet you halfway on this, so....I do use cascade , and it doesnt leave spots on my glasses. :eek:

"Thats my story and i'm stickin' to it.." Franz Kafka