Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,518
    Thanks
    0
    Thanked 22 Times in 22 Posts

    SQL Server relationships (Access 2003 SP3)

    After developing a database in SQL and creating 4 tables, i now want to draw the relationship lines between them. I presume i need them to set referential integrity as in Access.

    I have struggled with this for quite a while last night.

    I bet it's easy, but darned if i can see how to.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    It's done by creating restraints on a foreign key. See for example Defining cascading referential integrity constraints in SQL Server.

  4. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,324
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    Open the SQL Server Management Studio, and click down to your database. Underneath that is a choice of "Database Diagrams". This works much like the Access diagrams, but has several advantages. Primary among these is that you can create multiple diagrams, so you don't have to define all your relationships in one big diagram (as you do with Access). Of course, with only 4 tables, this isn't much of an advantage.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,518
    Thanks
    0
    Thanked 22 Times in 22 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    Do you just drag the mouse between like fields in the different tables, i couldn't do this.

  6. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,518
    Thanks
    0
    Thanked 22 Times in 22 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    Thanks Hans, i will check this out.

  7. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,324
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    You have to click on the grey box next to a field and then drag it.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,518
    Thanks
    0
    Thanked 22 Times in 22 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    Thanks Mark, that is fine.

    However it comes up with Yes to Enforse for Replication. Do I need to set this to No?

  9. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,518
    Thanks
    0
    Thanked 22 Times in 22 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    Can you tell me Hans what OBJECT_ID is in that sample you posted for me?

    Is it a passed in parameter? Is that a Stored Procedure?

  10. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,324
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    That's correct, set this to No.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 15 Times in 15 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    I don't have the slightest idea, sorry.

  12. #11
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    Hi Pat,
    T-SQL (Transact SQL) definition: OBJECT_ID
    It is a unique id number for an object in a database. This blog has a nice explanation of it.
    Checking to see if the OBJECT_ID is > 0 is one way to query the existence of that table in the code Hans referenced.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  13. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,518
    Thanks
    0
    Thanked 22 Times in 22 Posts

    Re: SQL Server relationships (Access 2003 SP3)

    Thanks Gary, nice explanation in that blog. The other 2 references (T-SQL and OBJECT_ID) give valuable explanations as well.

    I will read and reread that blog.

Posting Permissions

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