Coding referential integrity Say it ain't so

Question:
I have "inherited" an application for which the backend was written in Sybase. Accorging to the original developers, all checking is done with triggers and indexes, not foreign keys.
What are the advantages/disadvantages to this approach?
Answer:
Despina,
First of all, I would like to offer my moral support at this unlovely time in your life. Convert from Sybase Transact-SQL to Oracle PL/SQL – ugh. I hope that your team has explored options for automated migration. Oracle offers the Migration Workbench, which works with all supported versions of Oracle. Visit otn.oracle.com/migration for more details.
As to your specific question, I strongly urge you to switch to using foreign keys rather than rely on triggers and indexes. More generally, you should take advantage of every single possible declarative mechanism that Oracle offers to implement your application logic.
By "declarative," I mean that instead of writing code (procedural logic) to ensure data integrity, you simple describe or declare your requirements and let Oracle do the "heavy lifting": the enforcement of your requirements through code that resides in a lower level of the stack, and is invisible to you.
By taking the declarative approach, you can implement your business rules much more quickly, you will have less code to maintain, and you will be able to enhance your application much more easily. Finally and most important, there is no way to bypass the constraint defined by the foreign key. With a code-based constraint, if for any reason that code is disabled or simply missing, all heck breaks loose.
Sure, sometimes foreign keys are a bother. You can't just delete rows from a table willy-nilly. That's good in production, but a pain in the neck at times when you are playing around in your development database. No big deal. Just temporarily disable the constraint with a statement like the one below:
ALTER TABLE my_table DISABLE CONSTRAINT constraint_name
/
Perform the necessary operations and then enable the constraint. Or drop your database objects and rebuild them with the new test data.
Bottom line: write the least amount of code you can to implement your application requirements. You can achieve this admirable objective by reusing code, generating code…and relying on the many declarative mechanisms offered by Oracle to achieve a high level of data and code integrity.