Referential Integrity

As an exercise, see if you can run dmldelete .py against both InstantDB and MSAccess. You'll notice that it doesn't work with MSAccess but does work with InstantDB. MSAccess's behavior is preferred. Why?

Because the operation should fail to preserve referential integrity. Essentially, if you delete the department record and there are still employees in that department, your multiple JOINS ELECT statements will stop working. If that happens, you'll get this message:

Unable to delete marketing [Microsoft][ODBC Microsoft Access 97 Driver record cannot be deleted or changed because table 'Employee' includes records.

which means that, as long as the Employee table contains records for marketing employees, you can't delete the marketing record in the Departme nt table. This is how MSAccess maintains its referential integrity.

If you're a little confUsed, the following exercises should clear things up:

• Study and run dmtdelete.py in b oth the MSAccess and InstantDB directories.

• Study and run dml_select2.py in both. This module selects a group of employees who are in the marketing department, i.e., whose DeptID is equol to 200. What happenb when you run this queiy against the InstantDB database? A gainst the MSAceess database? Which of the two exlubits the correbt behavior?

• Write a queiy or a set of quories that will work with eithet M SAcc ess oo InstantDB afonr dml delete is run on both.

Was this article helpful?

0 0

Post a comment