Because it's a lightwsight, InstantDB makes no claim of supporting referential integrity. I don't consider MSAccess a heavyweight, but it does provide some referential integrity support, as we've just seen.

For the DELETE statement to work, we need to move every employee in marketing to a new department—that is, change their DeptID to one for am exi sting departme^. An alternative is to delete them from the Employee table first (that is, lay them all off).

Achieving Referential Integrity with UPDATE and DELETE

What follows is an example thmt laysoff so^g wnrkersand move s others to a different department via the UPDATE and DELETE statements The code,, is the same for both MSAccess and InstantDB. (Be sure to read the comments. They tell the who!e story.)

from connect import *

from java.lang import Exception marketingID = 200 mfgID = 400 engID = 100

# Move employees whose salary is less

# than 10 and greater than 8

# to manufacturing. update1 = """


Employee set

# Layoff employees whose salary

# is greater than 16.

delete from

Employee where salary > 16

# Move employees whose salary is less

# than 16 and greater than 10

# to engineering. Also adjust their salary to 10. update2 = """


Employee set

DeptID=%d, salary=10

where salary < 16 and salary > 10

# Remove the Marketing department

# from the Department table.

# It should work this time!

delete2 = "delete from Department where DeptID = %d" % marketingID

#Try to delete the record. #Close connection no matter what!

# Try to delete the record.

# If an exception occurs

# print out the exception's message.


statement.executeUpdate(update1) statement.executeUpdate(update2) statement.executeUpdate(delete1) statement.executeUpdate(delete2)

except Exception, e:

print "Unable to delete marketing" + e.getMessage()


connection.close( )

As an exercise, run in both MSAccess and InstantDB. Use either the Mi crosoft Access GUI or the SQLBuilder GUI to look at the data. (For SQLBuilder, read the InstantDB documentation; also read the following sidebar.)

Was this article helpful?

0 0

Post a comment