[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Extract Relationship from MS SQL Server

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?

1 Solution
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.  


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."
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...


Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now