Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Unanswered: 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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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
    Last edited by Ruprect; 08-07-04 at 03:49.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 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!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Quote Originally Posted by r937
    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.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •