What is the purpose of a DataRelation in a DataSet?

Question

A C# program I am working on will need to export data to one or more tables of data. Some columns of these tables will be related to one another, such that the ID column of of one table may be reference by the cell of another table. This seems like the common case for setting up a DataRelation. But I'm trying to understand how these relationships can actually be used later.

I have a little experience with relational databases but not much. I understand SQL queries and stuff like LEFT join, right join etc. And I've done some work in Access creating queries both with the GUI and by hand. But I've never created any relationships in the database itself. I tried this a minute ago in Access. After creating a relationship i tried adding my two test tables to a query, and the join was automatically created.

However, when I actually looked at the actual SQL it did in fact include a JOIN command that was derived from the relationships. But that also means the query was not dependent at all on the relationship at all. How would a relationship be used in a database that doesn't have a drag and drop type query editor like Access?

Is the only reason I would want to create a relationship in a database, or a DataRelation in a DataSet, is so that I can imply what relationships should exist but not necessarily force them?

Answer 1

Actually the opposite. Setting up a relation between tables helps enforce them. If you setup a foreign key relationship and try to insert data into a table and don't include the foreign key the database will stop you.

Many DB's have cascading updates and deletes also when you setup relations. They will automatically update and/or delete the child relations when you are updating/deleting the parent.

The DataRelation works similarly in that it helps enforce the relationships at the code level....

Here's a link to some of the basics of database design: http://www.sqlteam.com/article/database-design-and-modeling-fundamentals. #4, 5, 6, and 7 talk about relationships.

And here's another for more info on: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Here's a tutorial on using the DataRelation to help with the Parent/Child relationship in code when using a DataSet:

http://www.dotnetjohn.com/articles.aspx?articleid=63

And here's the MSDN for the DataRelation: http://msdn.microsoft.com/en-us/library/system.data.datarelation%28VS.80%29.aspx

Answer 2

DataRelations are used to :

• Enforce referential integrity (you can't reference a row that doesn't exist in the parent table)

• Navigate through relations, for instance obtain the child rows or parent row of a relation :

  DataRow[] orderRows = customerRow.GetChildRows("Customers_Orders");

• Create calculated columns with expressions that reference the parent or child rows

  OrderTotalColumn.Expression = "Sum(Child(Orders_OrderDetails).Quantity * Child(Orders_OrderDetails).UnitPrice)";

DataRelations are also used also in the Windows Forms binding system

Answer 3

Sometimes Access in the query designer will automatically join the fields of two tables together if they are the same name (ID) even if you have not created the relationship.

Answer 4

It seems to me that you are confusing referential integrity at the database engine level with a nice feature of the Access UI.

As others have explained, a relationship is about the data, not about the pretty entity relationship diagram you get in the relationship designer, nor about ease of use in the Access query grid. A relationship restricts the values in a field in one table to values drawn from a column of a different table.

The lines you get in the query grid when you add two tables with RI defined between them is a result of Access being nice enough to draw the default relationship for you.

Access by default also guesses about relationships using "AutoJoin," a setting that can be controled in TOOLS | OPTIONS | TABLES/QUERIES. Here's the What's This explanation:

Select to automatically create an inner join between two tables you add to the design grid. Clear if you want to define relationships between the tables yourself. For the AutoJoin to occur, the tables must each have a field with the same name and data type and one of the join fields must be a primary key.

In that case, you can get automatic join lines even when no relationship is defined.

Also, note that if you alias fields in a saved QueryDef, you can lose both kinds of automatic joins. This seems to me to be something that has changed in recent versions of Access, but I don't have the time to actually check that.