So all the tables have been added to the database, most if not all with logical relationships to one another EX: Each Order has many Order Detail, Each Salesperson has many Order, etc (if your not following I would suggest you stop here and read up on Logical Database Design)…what more do we need to do? Validate Validate Validate! Here is an example:
Say I have two tables called Order and OrderDetail. Each Order can have many Order Detail. Each OrderDetail describes a single line item sold for a given Order. Now what happens if a program adds several OrderDetail rows and then the application freezes before adding the corresponding row to the Order table? Or what happens if an application deletes a row from Order without deleting all the rows from OrderDetail? In short we end up with orphaned child rows (foreign key table) that have no corresponding parent (primary key table).
In a perfect world all applications would modify tables in the proper order. But this is not a perfect world and there are cases where situations like the above can (and will!) occur. For a small project, where the programmer is also the DBA, it is possible to diminish the odds of violating any relationships with careful application design…but what happens if a database is accessed by several dozen applications all written by multiple programmers? Or what if someone accesses the tables directly (gasp!) and makes some changes? A simple mistake could end up violating the integrity of the database with no one ever knowing.
Repeat after me: “It is the job of the RDBMS, not the end application, to ensure referential integrity.”