Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Constraints
Okay, I confess that the code shown in Listing 4 doesn't handle every odd situation that an inventive user can concoct. If you create a TestScores record with no corresponding Students record, the new records contain a StudentId value that doesn't exist in the Students table. That violates the TestScores table's foreign key constraint, so the database disallows the change, and again the program crashes.
Also, if you create a Students record with the same StudentId as an existing record, the database gets upset.
You could solve both of these problems with enough user interface programming. Your program could examine the values to make sure there was a Students record defining all of the StudentIds needed by the TestScores records. Similarly, your code could verify that the tables' primary key values are unique.
Happily, you may be able to dodge some of that code if you tell the DataSet more about the database's structure. You can add objects to the DataSet that tell it which columns define the tables' primary keys, and which columns are related by a foreign key constraint.
The ForeignKey program does just that. It gives the DataSet extra information so the DataSet can protect the program from these kinds of database errors. If the user enters a TestScores record with a StudentId that is not already in the Students table, the program immediately complains, and gives the user a chance to fix the problem. If the user tries to create a Students or TestScores record with a primary key value that is already in use, the program also complains. Finally, if the user deletes a Students record without deleting the corresponding TestScores records first, the program automatically deletes those records. Click here to download the ForeignKey program's source code.
Program ForeignKey uses the code shown in Listing 5 to build its DataSet. It begins by loading the Students and TestScores table data into a DataSet much as the previous versions did. It then obtains references to several objects it will need to define the database's structure. First, it uses the DataSet's Tables collection to get references to the two DataTable objects it has loaded. Next, it uses the DataTables' Columns collections to get references to DataColumn objects representing the StudentId and TestNumber columns in the two tables.
Now, the program creates an array holding a reference to the DataColumn object that represents the Students table's StudentId column. It then sets the Students DataTable's PrimaryKey property equal to this array. That tells the DataTable that the Students field is its primary key.
The program defines the TestScores table's primary key similarly. In this case, however, the primary key includes both the StudentId and TestNumber fields.
Finally, the program defines the foreign key that relates the two tables. It makes a new ForeignKeyConstraint object, passing its constructor references to the parent and child DataColumns that define the relationship. It then adds the new constraint to the TestScores DataTable's Constraint collection.
The routine finishes by binding the program's DataGrid control to the DataSet object.
Listing 5—This code tells the DataSet about the database's structure.
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
' Load the Students table.
m_daStudents.Fill(m_DataSet, "Students")
' Load the TestScores table.
m_daTestScores.Fill(m_DataSet, "TestScores")
' Get the DataTable objects.
Dim dt_students As DataTable = m_DataSet.Tables("Students")
Dim dt_testscores As DataTable = m_DataSet.Tables("TestScores")
' Get DataColumn objects we will need.
Dim col_students_studentid As DataColumn = _
dt_students.Columns("StudentId")
Dim col_testscores_studentid As DataColumn = _
dt_testscores.Columns("StudentId")
Dim col_testscores_testnumber As DataColumn = _
dt_testscores.Columns("TestNumber")
' Define the primary keys.
Dim students_keys() As DataColumn = _
{col_students_studentid}
dt_students.PrimaryKey = students_keys
Dim testscores_keys() As DataColumn = _
{col_testscores_studentid, col_testscores_testnumber}
dt_testscores.PrimaryKey = testscores_keys
' Make a foreign key constraint.
Dim fk_studentid As New ForeignKeyConstraint( _
col_students_studentid, col_testscores_studentid)
dt_testscores.Constraints.Add(fk_studentid)
' Bind the DataGrid to the DataSet.
DataGrid1.DataSource = m_DataSet
End Sub