Data Relationships


Relationships Fundamentals



We have created applications that included various objects. In those applications, we were using the objects as independent as possible. A relational database is an application in which information flows from one object to another. To illustrate this, imagine you are creating a database for a car rental company (actually our Bethesda Car Rental application). When processing an order, the user would have to select a car and the customer who would rent it. To make this possible, one one hand you must create a special relationship between the list that holds the cars and the object used to process orders, on the other hand you must create a relationship between the list of customers and the order processor.

The DataSet and the other data-based classes of the System.Data namespace provide all the functionalities you need to create relationships among objects.

Using Unique Values

In order to create a relationship, you need two tables. One table would hold the original data. That table is referred to as the parent. That table would provide its data to another table. The table that receives data is referred to as a child table. The table that acts as the parent must have a way to uniquely identify each record.

In the previous lesson, we saw that, to create unique values for a column, you could set its Unique property to true. Once this property is set, the user must remember to specify the value of that column, otherwise, the record would not be created. In some cases, the user may not have the right value at the time of data entry or the user may simply be confused.

To assist you with specifying the unique value of a column, you can ask the compiler to take care of this aspect. You way this can happen to use an incremental value on the column. To support this, the DataColumn class is equipped with a Boolean property named AutoIncrement. The default value of this property is false. When this property is set to true, when the user decides to add a new record, the compiler would increment the current value of the column.

By default, if the DataColumn.AutoIncrement property is set to true, before any record is created on the table, the initial value of the column is set to 0 and that would be the first value of the column. If you want, you can start the records with another value. To support this, the DataColumn class is equipped with a property named AutoIncrementSeed, which is a long integral type.

By default, if the AutoIncrement property of the DataColumn class is set to true, when a record is being created, the value of the column would be incremented by 1. If you want it to be incremented by another value, you can use the AutoIncrementStep property.

Relationship Constraints


The Primary Key

To create a scenario where data flows from one list to another, we saw that the first list must hold the record that would be supplied to other lists. In this first list, each record must be distinguishable from the others. One way you can take care of this is to create what is referred to as a primary key.

To create a primary key, in the Tables Collection Editor, you can click the ellipsis button of the Constraints field. In the Constraints Collection Editor, you can click Add -> Unique Constraint, give it a name, click the check box of the column that will be the primary key, and click the Primary Key check box.

The Foreign Key

For a parent list to supply its information to another list, that child list must have a column that would serve as a relay. This means that, in the child list, you must create a column that would correspond to the primary key of the parent table. This column of the child list is called a foreign key.

To create a foreign key, in the Tables Collection Editor, you can click the ellipsis button of the Constraints field. In the Constraints Collection Editor, you can click Add -> Foreign Key Constraint. In Foreign Key Constraint:

  • Accept or change the Name
  • In the Parent Table combo box, select the name of the table that holds the primary key
  • If everything is configured fine, after selecting the table that holds the primary, the box under Key Columns and the box under Foreign Key Columns should have the names of the right columns already. Otherwise, you should click the box under Key to reveal its combo box, then click the arrow of that combo box to select the primary key column of the parent table
  • Click the box under Foreign Key Columns and, in its combo box, select the name of the foreign key in the current table

Here is an example:

In the same way, you can create the other foreign keys for your table. The list of foreign keys would appear in the Members list of the Constraints Collection Editor. Here is an example:


Data Relationships



A relational database is an application in which different table work together so that information in one table can be made available to other tables. To make this possible, you start by creating the tables as we have done above. Each table must have a primary key. As we saw above, to make data from a parent table available to data from a child table, the child table must have have a foreign key would "represent" the information from the parent table. Once the tables and their keys have been created, you can then link them.

Creating a Relationship

To visually create a relationship in a data set, first select  the DataSet object. In the Properties window, click Relations and click its ellipsis button. In the Relations Collection Editor, to create a relationship, click Add. In the Relation dialog box, you can complete the text boxes and combo boxes using the same options as when creating a foreign key.

If you had previously created a(the) foreign key(s) in your table, the relationship(s) would automatically be created and configured so you don't have to recreate it.

Data Binding

Once a relationship exists between two tables, you can use that relationship to have the information flow from one list to another. To support this, the visual controls of the .NET Framework are equipped with various properties, include DataSource and DisplayMember.

The DataSource property specifies the name of the data set variable that holds the database. The DisplayMember property specifies the name of the column from the table that has the value to be displayed.


Previous Copyright © 2007 FunctionX, Inc. Next