Database
Relational Databases 101
By William R. Vaughn with Peter Blackburn, October 20, 2006
Understanding what relational databases are all about is the first step to effectively using them.
Understanding Parents and Children
Note that the database diagram shows the relationships among the three tables. In this case, there is a primary key/foreign key (PK/FK) relationship between the Orders and Customers table, as well as the Items and Customers table. A PK/FK relationship ties two tables together, in that when a row is added to the foreign key table, there is a corresponding row in the primary key table. This means you can't add an order with an invalid or missing customer ID (CustID). Because both of these tables (most tables) have a primary key, it can be bit confusing. For this reason (and other reasons), I call the "primary key" table the "parent" and the foreign key table the "child." In our design, the Customer table is the parent, and it has two children—the Orders and Items tables. I could also create a tiered parent/child hierarchy, as shown in Figure 3.4.
[Click image to view at full size]
Figure 3.4: A parent/child relationship tree.
Tip - These diagrams are annotated screenshots from a database diagram created by Visual Studio.
These relationships can be defined in the database to ensure that no order is created without a valid CustID and no item is created without a valid OrderID and a valid CustID. These defined (and server-enforced) relationships are called "constraints" and are used to maintain "referential" and data integrity. When these constraints are enabled, they mean that you won't be able to delete customers from the database who have orders or items. When I start making changes in the database with ADO.NET, I'll see how I have to handle these relationships with care. Note that once these relationships are defined in the database, no matter what applications access the database, these relationships are enforced. This means you can be (more) confident that when the pointy-haired manager starts to make changes to the data with Access, he (or she) won't be able to break the referential integrity—or at least, not easily.
Changing the Primary Key
One other point before I move on. Once a primary key is created, it should be considered inviolate. If
you think that a change to the primary key is necessary, think again. It's far safer and easier to delete the current hierarchy and rebuild it rather than simply trying to change a primary key. If the constraints are in place (and you can disable them in code), the server won't let you change the PK until all related dependencies are removed. That means you'll need to delete all of the parent's children (and all of the grandchildren) before changing or deleting the parent row. Since the parent might have a dozens of dependencies throughout the database, this is not an easy task.
