Fundamentals of Data Relationships

Introduction

Consider the following starting points of a data set:

Public Class Exercise
    Private colStudentID As DataColumn
    Private colUsername As DataColumn
    Private tblStudents As DataTable
    Private dsStudents As DataSet

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colStudentID = New DataColumn("ColumnID", _
                                      Type.GetType("System.Int32"))

        colUsername = New DataColumn("Username", _
                                     Type.GetType("System.String"))

        tblStudents = New DataTable("Student")
        tblStudents.Columns.Add(colStudentID)
        tblStudents.Columns.Add(colUsername)

        dsStudents = New DataSet("Students")
        dsStudents.Tables.Add(tblStudents)
    End Sub
End Class

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. Here is an example:

Public Class Exercise
    Private colStudentID As DataColumn

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load

        colStudentID = New DataColumn("ColumnID", _
                                      Type.GetType("System.Int32"))
        colStudentID.Unique = True
    End Sub
End Class

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. To make this happen, you can set 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. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    colStudentID = New DataColumn("ColumnID", _
                                  Type.GetType("System.Int32"))
    colStudentID.Unique = True
    colStudentID.AutoIncrement = True
End Class

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. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    colStudentID = New DataColumn("ColumnID", _
                                  Type.GetType("System.Int32"))
    colStudentID.Unique = True
    colStudentID.AutoIncrement = True
    colStudentID.AutoIncrementSeed = 1000
End Sub

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. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colStudentID = New DataColumn("ColumnID", _
                                      Type.GetType("System.Int32"))
        colStudentID.Unique = True
        colStudentID.AutoIncrement = True
        colStudentID.AutoIncrementSeed = 1
        colStudentID.AutoIncrementStep = 100
End Sub

In a relation, a constraint is a rule that would specify how some data is created or provided to a table. For example, a constraint can provide the means by which some values of a column are entered. Another type of constraint can create a rule that controls the types or ranges of values that can be accepted for a column; non-acceptable values would be rejected. There are various types of constraints and most are supported in the DataSet system.

To visually create a constraint, display the Table Collection Editor. In the Members list, click the name of the table. In the Properties list, click the Constraints list and click its ellipsis button:

To support constraints, the DataTable class is equipped with a property named Constraints, which is an object of type ConstraintCollection.

A unique constraint is a rule that states that the value of one column or the combination of values of some columns must be unique among the records of a table. The unique constraint can involve only one column as we saw for the Unique property of a column. For example, if you are creating a list of usernames for the students of a school, you would not want two students to have the same username. Here is an example:

When creating the table, you can set up a unique constraint so that duplicate values would be rejected. In this case, every time the user enters a new value, the compiler (actually an interpreter) would check if that value exists already. If so, the user would be informed and must take appropriate actions to correct it. Here are examples:

A unique constraint can also involve more than one column. For example, if you are creating a list of members of a club, it could be confusing to have two members with the exact same full name. You could allow same last names. As long as the first names are different, there would be less or no confusion. Using a unique constraint, you can set a rule so that each combination of a first and last name would be different.

To visually create a unique constraint, display the Table Collection Editor and create the desired columns for a table. In the Members list, click the desired table. In the Properties list, click the ellipsis button of the Constraints field. In the Constraints Collection Editor, click Add -> Unique Constraint, give it a name, click the check box of the column that will hold unique values. Here is an example:

If the unique constraint will involve more than one column, click the check box of each. After providing the necessary information, click OK.

To support unique constraints, the System.Data namespace provides the UniqueConstraint class, which is derived from the Constraint class. Therefore, to programmatically create a unique constraint, you can declare a variable to type UniqueConstraint and initialize it with one of its many constructors. If you want to specify only the column that will hold unique values, you can use the following constructor:

Public Sub New(column As DataColumn)

This method expects the variable name of a column as argument. After creating the constraint variable, you can add it to the Constraints collection of the table. To support this, the ConstraintCollection class is equipped with a method named Add that comes in many versions. If you had created a UniqueContraint object and you want to add it, you can use the following syntax of the method:

Public Sub Add(constraint As Constraint)

This version expects a Constraint-derived object as argument. Here is an example:

Public Class Exercise
    Private colStudentID As DataColumn
    Private colUsername As DataColumn
    Private tblStudents As DataTable
    Private cnsUniqueUsername As UniqueConstraint

    Private dsStudents As DataSet

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colStudentID = New DataColumn("ColumnID", _
                                      Type.GetType("System.Int32"))

        colUsername = New DataColumn("Username", _
                                     Type.GetType("System.String"))

        tblStudents = New DataTable("Student")
        tblStudents.Columns.Add(colStudentID)
        tblStudents.Columns.Add(colUsername)

        cnsUniqueUsername = New UniqueConstraint(colUsername)
        tblStudents.Constraints.Add(cnsUniqueUsername)

        dsStudents = New DataSet("Students")
        dsStudents.Tables.Add(tblStudents)
    End Sub
End Class

If you create a unique constraint using the UniqueConstraint(column As DataColumn) constructor, a default name would be assigned to it. If this is the first constraint, it would be named Constraint1 and the names would be incremental. If you want, you can provide your own name. To do this, you can use the following constructor of the UniqueConstraint class:

Public Sub New(name As String,	column As DataColumn)

The first argument is the name of the unique constraint. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colStudentID = New DataColumn("ColumnID", _
                                  Type.GetType("System.Int32"))
        colStudentID.Unique = True
        colStudentID.AutoIncrement = True
        colStudentID.AutoIncrementSeed = 1000
        colStudentID.AutoIncrementStep = 5

        colUsername = New DataColumn("Username", Type.GetType("System.String"))
        tblStudents = New DataTable("Student")
        tblStudents.Columns.Add(colStudentID)
        tblStudents.Columns.Add(colUsername)

        cnsUniqueUsername = New UniqueConstraint("UniqueUsername", colUsername)
        tblStudents.Constraints.Add(cnsUniqueUsername)

        dsStudents = New DataSet("Students")
        dsStudents.Tables.Add(tblStudents)
End Sub

If the unique constraint is a combination of columns, create them in an array before adding them.

To create a scenario where data flows from one list to another, we saw that the first list must hold the records that would be supplied to other lists. In the 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 visually create a primary key, in the Members list of the Tables Collection Editor, click the desired table. In the Properties list, click the arrow of the PrimaryKey combo box and click the left button of the column. Here is an example:

Like a unique constraint, a primary key can involve more than one column. In this case, you would click the gray button of each of the columns that would be involved in the combination. After doing this, you can click Close.

In reality, a primary key is a unique constraint. Therefore, if you create a primary key as just described above, the studio would configure it behind the scenes. Otherwise, you can explicitly create a primary key as a unique constraint. To do this, in the Members list of the Tables Collection Editor, click the desired table. In the Properties list, click Constraints and click its ellipsis button. In the Constraints Collection Editor, click Add -> Unique Constraint. In the Unique Constraint dialog box, accept the default name or change it. In the Columns list, click the check box of the column that will be the primary key, and click the Primary Key check box. Here is an example:

If the primary key involves more than one column, click the check box of each. After making the selections, click OK.

To assist you with programmatically creating a primary key, the UniqueConstraint class is equipped with the following constructor:

Public Sub New(column As DataColumn, isPrimaryKey As Boolean)

In this case, the second argument is passed as True or False. If passed as True, the column specified as the first argument would be treated as the primary key. If you use this constructor, a default name would be given to the constraint. If you want to specify a name, you can use the following constructor:

Public Sub New ( _
	name As String, _
	column As DataColumn, _
	isPrimaryKey As Boolean _
)

Here is an example:

Public Class Exercise
    Private colGenderID As DataColumn
    Private PKGenderID As UniqueConstraint
    Private colGender As DataColumn
    Private tblGenders As DataTable

    Private colStudentID As DataColumn
    Private colUsername As DataColumn
    Private tblStudents As DataTable
    Private cnsUniqueUsername As UniqueConstraint

    Private dsStudents As DataSet

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colGenderID = New DataColumn("GenderID", _
                                     Type.GetType("System.Int32"))
        colGenderID.AutoIncrement = True
        colGenderID.AutoIncrementSeed = 1
        colGenderID.AutoIncrementStep = 1

        colGender = New DataColumn("colGender", _
                                   Type.GetType("System.String"))

        tblGenders = New DataTable("Gender")
        tblGenders.Columns.Add(colGenderID)
        tblGenders.Columns.Add(colGender)

        colStudentID = New DataColumn("ColumnID", _
                                      Type.GetType("System.Int32"))
        colStudentID.Unique = True
        colStudentID.AutoIncrement = True
        colStudentID.AutoIncrementSeed = 1000
        colStudentID.AutoIncrementStep = 5

        colUsername = New DataColumn("Username", _
                                     Type.GetType("System.String"))

        tblStudents = New DataTable("Student")
        tblStudents.Columns.Add(colStudentID)
        tblStudents.Columns.Add(colUsername)

        cnsUniqueUsername = New UniqueConstraint("UniqueUsername", colUsername)
        tblStudents.Constraints.Add(cnsUniqueUsername)

        PKGenderID = New UniqueConstraint("PKGenderID", colGenderID, True)
        tblGenders.Constraints.Add(PKGenderID)

        dsStudents = New DataSet("Students")
        dsStudents.Tables.Add(tblStudents)
    End Sub
End Class

For a parent list to supply its information to another list, the 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 visually 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 key, 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.

To support foreign keys, the System.Data namespace provides a class named ForeignKeyConstraint. The ForeignKeyConstraint class is derived from the Constraint class. To programmatically create a foreign key, declare a variable of type ForeignKeyConstraint and initialize it with one of its six constructors. If you want to specify (only) the names of the primary key and the foreign key columns, you can use the following constructor:

public ForeignKeyConstraint(DataColumn parentColumn,
			    DataColumn childColumn)

Here is an example:

Public Class Exercise
    Private colGenderID As DataColumn
    Private PKGenderID As UniqueConstraint
    Private colGender As DataColumn
    Private colStudentGenderID As DataColumn
    Private tblGenders As DataTable

    Private colStudentID As DataColumn
    Private colUsername As DataColumn
    Private tblStudents As DataTable
    Private cnsUniqueUsername As UniqueConstraint

    Private FKGenderID As ForeignKeyConstraint

    Private dsStudents As DataSet

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colGenderID = New DataColumn("GenderID", _
                                     Type.GetType("System.Int32"))
        colGenderID.AutoIncrement = True
        colGenderID.AutoIncrementSeed = 1
        colGenderID.AutoIncrementStep = 1

        colGender = New DataColumn("colGender", _
                                   Type.GetType("System.String"))

        tblGenders = New DataTable("Gender")
        tblGenders.Columns.Add(colGenderID)
        tblGenders.Columns.Add(colGender)

        colStudentID = New DataColumn("ColumnID", _
                                      Type.GetType("System.Int32"))
        colStudentID.Unique = True
        colStudentID.AutoIncrement = True
        colStudentID.AutoIncrementSeed = 1000
        colStudentID.AutoIncrementStep = 5

        colUsername = New DataColumn("Username", _
                                     Type.GetType("System.String"))
        colStudentGenderID = New DataColumn("GenderID", _
                                            Type.GetType("System.Int32"))

        tblStudents = New DataTable("Student")
        tblStudents.Columns.Add(colStudentID)
        tblStudents.Columns.Add(colUsername)
        tblStudents.Columns.Add(colStudentGenderID)

        cnsUniqueUsername = New UniqueConstraint("UniqueUsername", _
                                                 colUsername)
        tblStudents.Constraints.Add(cnsUniqueUsername)

        PKGenderID = New UniqueConstraint("PKGenderID", _
                                          colGenderID, True)
        tblGenders.Constraints.Add(PKGenderID)

        FKGenderID = New ForeignKeyConstraint(colGenderID, _
                                              colStudentGenderID)
        tblStudents.Constraints.Add(FKGenderID)

        dsStudents = New DataSet("Students")
        dsStudents.Tables.Add(tblStudents)
    End Sub
End Class

A relational database is an application in which different tables 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 a foreign key that would "represent" the information from the parent table. Once the tables and their keys have been created, you can link them.

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 do not have to recreate it(them).

To support relations in a database, the DataSet class is equipped with a property named Relations. The DataSet.Relations property is an object of type DataRelationCollection. The DataRelationCollection class is a collection of objects where each member is of type DataRelation. To create a relationship, declare a variable of type DataRelation and initialize it using one of its six constructors. To specify the primary key and the foreign key, you can use the following constructor:

Public Sub New(relationName As String, _
	       parentColumn As DataColumn, _
	       childColumn As DataColumn)

The first argument is the name of the relationship. The second argument is the column name of the primary key. The last argument is the column name of the foreign key. After creating the relationship, you can add it to the DataSet.Relations property. To support this, the DataRelationCollection class is equipped with the Add() method that is provided in various versions. One of the versions uses the following syntax:

Public Sub Add(relation As DataRelation)

Here is an example

Public Class Exercise
    Private colGenderID As DataColumn
    Private PKGenderID As UniqueConstraint
    Private colGender As DataColumn
    Private colStudentGenderID As DataColumn
    Private tblGenders As DataTable

    Private colStudentID As DataColumn
    Private colUsername As DataColumn
    Private tblStudents As DataTable
    Private cnsUniqueUsername As UniqueConstraint

    Private FKGenderID As ForeignKeyConstraint
    Private relSchool As DataRelation
    Private dsStudents As DataSet

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        colGenderID = New DataColumn("GenderID", _
                                     Type.GetType("System.Int32"))
        colGenderID.AutoIncrement = True
        colGenderID.AutoIncrementSeed = 1
        colGenderID.AutoIncrementStep = 1

        colGender = New DataColumn("colGender", _
                                   Type.GetType("System.String"))

        tblGenders = New DataTable("Gender")
        tblGenders.Columns.Add(colGenderID)
        tblGenders.Columns.Add(colGender)

        colStudentID = New DataColumn("ColumnID", _
                                      Type.GetType("System.Int32"))
        colStudentID.Unique = True
        colStudentID.AutoIncrement = True
        colStudentID.AutoIncrementSeed = 1000
        colStudentID.AutoIncrementStep = 5

        colUsername = New DataColumn("Username", _
                                     Type.GetType("System.String"))
        colStudentGenderID = New DataColumn("GenderID", _
                                            Type.GetType("System.Int32"))

        tblStudents = New DataTable("Student")
        tblStudents.Columns.Add(colStudentID)
        tblStudents.Columns.Add(colUsername)
        tblStudents.Columns.Add(colStudentGenderID)

        cnsUniqueUsername = New UniqueConstraint("UniqueUsername", _
                                                 colUsername)
        tblStudents.Constraints.Add(cnsUniqueUsername)

        PKGenderID = New UniqueConstraint("PKGenderID", _
                                          colGenderID, True)
        tblGenders.Constraints.Add(PKGenderID)

        FKGenderID = New ForeignKeyConstraint(colGenderID, _
                                              colStudentGenderID)
        tblStudents.Constraints.Add(FKGenderID)

        dsStudents = New DataSet("Students")
        dsStudents.Tables.Add(tblStudents)
        dsStudents.Tables.Add(tblGenders)

        relSchool = New DataRelation("SchoolRelations", _
			colGenderID, colStudentGenderID)
        dsStudents.Relations.Add(relSchool)
    End Sub
End Class

There are many other ways you can create a relationship.

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, including 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.