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?
Thankx
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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...
Please see these links
Relation Types - One to Many
http://courses.mbl.edu/Med
Relation Types - Many to Many
http://courses.mbl.edu/Med
Which are main database relation types
http://www.faqts.com/knowl
Business Accounts
Answer for Membership
by: Arthur_WoodPosted on 2006-05-28 at 13:53:02ID: 16780694
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.
AW