quote:

---

The example was based around a parent - child relationship wasn't it ? I can't imagine a parent-child relationship where I would want to keep orphaned child records.

If staff don't understand the schema I'd be very nervous about letting them delete anything.

Steve Brett

---

In this case, the staff member is an experienced programmer. However, his experience is in RPG not SQL. In our environment, we have a large percentage of our work done without the benefit of SQL knowledge or good DBMS practices. The DB2 system is used more as an ISAM implementation. This is because 0S400 DB2 allows RPG to process files one record at a time even though it also allows SQL statements that process against those same files as tables and rows. The native langauage of the majority of our developers is RPG, so any converts to SQL and set based logic are welcomed and encouraged.

To your point about not wanting to leave orphans, in normal cases, our developers rely on their program logic to maintain RI. I know this is bad architecture, but the system in question is not within my area of influence. It is so old that even the sugestion of changing the underlying archtecture is laughable. Over 50 people maintain the system and no more than a handful see any value in RDBMS based RI. Having said that, the system works, we make a significant profit from it. When data is extracted from it, we attempt to apply good practices to it. Sometimes the data is pumped into the warehouse so it is denormalized and FK contraints are applied. Other operations pump it into the ODS in those cases, we do not enforce RI because some data will fail.

In any native SQL systems, we insist on good RI practices. Typically, we rely on FK contraints rather then cascading operations.