Extract Relationship from MS SQL Server

Posted on 2006-05-28
Last Modified: 2008-02-26
Hi Exprets,

I am making an application using MS SQL Server and I need to know that what kind of relationships are existing between table e.g. 1-1 or 1-M. I can get the constraint's, coulumn, table, types information from INFORMATION_SCHEMA at this point but I still need to find the relationship types between tables.
I am using C# and ADO.NET and the version of Database Server right now is MS SQL Server 2000 but Finally I will be utilizing MS SQL Server 2005.

Any idea, suggestion, snippet?

Question by:aahmed19
    LVL 44

    Expert Comment

    It is not possible to determine if a relationship is 1-1 or 1-M by simply examining the constraints on the tables.  You need to understand the data model, and that is something that is not obvious from the relationships alone.

    1-1 and 1-M can show precisely the same constraints.  


    LVL 3

    Expert Comment

    If you have Enterprise Manager, you may create Database Diagrams to show the relationship between tables. Check the content about Database Diagrams in MS SQL-Server Book Online.
    --"The endpoints of the line indicate whether the relationship is one-to-one or one-to-many.  If a relationship has a key at one endpoint and a figure-eight at the other, it is a one-to-many relationship. If a relationship has a key at each endpoint, it is a one-to-one relationship."
    LVL 50

    Accepted Solution

    if it has actually been implemented in the MODEL, and there aren't processing reasons for not implementing it (e.g. a requirement to
    keep historical information)

    then you could try looking for indexes which cover the constraint/FK and confirm whether the are UNIQUE or NOT...

    however this is something that can only be guessed at by querying the underlying system tables...
    JUST BECAUSE A MODEL doesn't explicitly use the underlying DBMS facilities to restrict relationship types, wouldn't
    mean that the business application logic doesn't itself enforce various business rules...

    in thesee cases you should always query the system designer/documentation , and potentially encapsulate your own business rules
    in a set of your own tables...

    LVL 16

    Expert Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now