Database Relationships and Constraints

for RuBoard

Before discussing how to create data relations inside a DataSet , it makes sense to explain how relationships function inside a relational database system such as Microsoft SQL Server. If you are already familiar with relational databases, you might want to skip ahead to the section entitled "The DataRelation Object."

Relationships

Suppose you have to store information about customer orders inside a database. You might choose to use a database schema like the one shown in the database diagram in Figure 4.1. There are five tables in this diagram, and they are all related to the main Orders table. Notice the lines connecting the tables together. Each of these lines represents a relationship in the database.

Figure 4.1. A database diagram of the built-in Microsoft SQL Server Northwind database

Each of the relationships in Figure 4.1 are "one to many." Notice how each relationship has an infinity symbol on one terminating end and a key on the other. The table connected via the infinity symbol may contain multiple entries from a column in the other table. For instance, the CustomerID field of the Orders table may only contain entries from the CustomerID field of the Customers table. It can contain the same CustomerID as many times as required (one per order per customer, in fact). If you try to enter a value in the CustomerID field of the Orders table that is not also present in the Customers table, you will receive an error.

This is done mainly to avoid duplicating data. Rather than storing redundant customer data in the Orders table, you only need the CustomerID, which you can then use to retrieve that customer's information later, when you need it. (For information on retrieving the customer information, see the section titled "The Join Operator.")

If you are using Microsoft SQL Server 2000, creating a relationship between two tables is easy:

1. Create a new database diagram in your database by right-clicking on Diagrams and selecting New Database Diagram as in Figure 4.2. You are presented with the Create Database Diagram Wizard. Click Next.

   Figure 4.2. The Create Database Diagram Wizard assists in setting up a new database diagram.

   

2. Add all the tables from the left to the tables in the new diagram, on the right side. Select Next and then Finish to complete the wizard. You will see a screen much like Figure 4.3.

   Figure 4.3. After the Create Database Diagram Wizard completes, you will see a screen like this one.

   

3. Right-click anywhere on the white background of the diagram window and select New Table. Name the new table EmployeeAwards.

4. Create the new table according to the values entered in Figure 4.4. When you are done, right-click on the grey box to the left of the AwardID column. Select Set Primary Key. A primary key must be present in both tables participating in a relationship.

   Figure 4.4. Creating a new database table inside the diagram utility.

   

5. Next, click and drag the box to the left of the EmployeeID column of the Employees table and drag it on top of the EmployeeID column of the EmployeeAwards table. You are presented with a screen like the one in Figure 4.5.

   Figure 4.5. After dragging and dropping the new relationship, you are presented with the Create Relationship screen.

   

6. Select OK and you are done. Notice that the graphical representation of the relationship is added to the diagram, as seen in Figure 4.6.

   Figure 4.6. The relationship between the two tables appears.

   

Now, if an employee wins an award, you can track it in the EmployeeAwards table.

The Join Operator

The only problem with using this type of data model is that whenever you need to retrieve records with columns from both tables, you must perform a join operation. The join operation enables you to select columns from two or more tables linked together by a relationship like the one you've just created.

There are a number of different kinds of join operations you can perform. Listing 4.1 displays the syntax to perform an inner join. An inner join will return records from two tables with matching records determined by the columns chosen for the join. For other types of joins, please refer to your database documentation.

Listing 4.1 The Syntax of the Join Operator

 SELECT  column_names  FROM  table_a  INNER JOIN table_b on      table_a.column = table_b.column WHERE      search_conditions 

The join operation essentially lets you add columns from another table to your current resultset based on the values in a common field. Syntactically, the query in Listing 4.2 and the query in Listing 4.3 are exactly the same.

For instance, suppose you want to retrieve all customer orders from the Orders table and also get their titles and phone numbers at the same time. The queries in Listings 4.2 and 4.3 will both retrieve the same data, one with a join, one without.

Listing 4.2 A Database Query Utilizing the Join Operator

 SELECT      Orders.*, Customers.ContactTitle, Customers.Phone FROM      Orders      INNER JOIN Customers on      Customers.CustomerID = Orders.CustomerID 

Listing 4.3 A Multitable Database Query Not Utilizing the Join Operator

 SELECT      Orders.*, Customers.ContactTitle, Customers.Phone FROM      Orders, Customers WHERE     Customers.CustomerID = Orders.CustomerID 

Constraints

Constraints are basically just rules about the contents of a database field. For instance, Figure 4.7 shows a constraint present in the Employees table of the Northwind database. This ensures that no one enters a future date in the birthdate field.

Figure 4.7. A constraint present in the Employees table of the Northwind database.

for RuBoard