How To Determine If A DataSet's Column Is A Foriegn Key To Another Table

Posted on 2003-03-09
Medium Priority
Last Modified: 2008-01-09
In a previous posting I mentioned how I'd need to determine a column's name
and datatype.  I failed to mention that I was using an ADO.NET dataset in a
C# windows application.  A reply to my posting prompted me to do more
research into datasets and I found most of what I need.

However, I am stuck here...how do I determine if a column in a dataset is a
foreign key to another table in the database?  Assume that I've set up my
database correctly and have named my foreign key relationships.

How do I test for this while looping through the columns in the dataset?  Or
is there another way?

Thanks, Axe
Question by:beaudetious
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 13

Expert Comment

ID: 8098870

Author Comment

ID: 8099336
I don't think you are reading my question properly.  I'm asking for specifics about how to determine if a column in a dataset has a foreign key relationship to another table in the database (and which table might that be).  You are (in your previous posting) answering me with some generalizations that I don't understand.  I do understand (and have figured out) how to use the properties of the columns in a dataset to learn the columnname and datatype.

Expert Comment

ID: 8105476
I don't understand completely.  Do you want information about the ADO.Net DataRelations, which would be a programming question for the C# or VB forum?  Or do you want information about the SQL Server Relationships?  

If you want to look at the relationships for SQL Server, then look up "sysforeignkeys" in SQL books online.  That should contain the information you need.

If you are talking about the ADO.Net DataRelations, would have you created the DataRelations yourself, or used some type of wizard to create all of the Data Components.  
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

Drom earned 150 total points
ID: 8112967
nitpicking: in .Net classes foreign key is a ForeignKeyConstraint, not DataRelation (and yes it looks like application, rather then pure db question)

Beaudetious, you can probably start with FillSchema() method of DataAdapter - it picks up database schema information including foreign keys to the table you going to Fill() later in DataSet. Or use DataAdapter property  MissingSchemaAction = MissingSchemaAction.AddWithKey before filling.

Then you can check foreign key in Contstraints collection of a DataTable from DataSet.Tables.

check more details in ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconaddingexistingconstraintstodataset.htm and ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdataforeignkeyconstraintclasstopic.htm if you have visual studio help handy.

hope this helps.

Author Comment

ID: 8113537
Thanks.  You came closest to the solution I found.  I now have a stored procedure that returns a resultset that includes all of the foreignkey relationships in my database.  I then loop through it looking for matching column names.

Expert Comment

ID: 8120542
any time :)

B> Thanks.  You came closest to the solution I found.
B> I now have a stored procedure that returns a resultset that includes all of the foreignkey relationships in my database.
B> I then loop through it looking for matching column names.

In this case RiverGuy is actually gave the answer you needed, not me. Please, reconsider answer choice.

Good luck,

Expert Comment

ID: 8123776
We don't take away points from experts once they are given in most cases.  Since I cannot re-assgn the answer, I am creating a points for RiverGuy and giving the 50 points there as well.  Please be more careful in awarding the points in the future.

RiverGuy, your points can be found at:

Community Support Moderator @Experts Exchange

Author Comment

ID: 8124646
Will do!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 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