Where should data be validated?

This just came up on the mailing list for my local Linux Users Group following last night's meeting (which I didn't attend), the original mail and my response is below:

---

> In the pub, there was an interesting conversation going on regarding
> validation of data in databases.
>
> Excuse the omissions, as I said, it was overheard
>
> Someone brought up the point that in databaseX If say, you have a
> varchar field set to a limit of 10, and put 26 chars of data into it
> databaseX silently truncates it.
>
> So my question is, in your opinion, should it be up to the front end or
> the database to do this kind of data validation?
>

I'm a Database Administrator, mostly working with Oracle. The reverse of this problem (data validated in the client but not in the database) is something I come accross a lot. Most RDBMS/ODBMS/ORDBMS, certainly any that can claim to be enterprise class, will have functionality to implement data validation (key constraints, check constraints, strong datatyping, triggers &c). Unfortunately the majority of software vendors, in my experience, seem unwilling to use this functionality with their products. The most common excuse is 'database independence', which in reality translates to their app, instead of working well with one *DBMS, will work badly with three or four. They want all the data validation to happen in their application so if the business rules change you have to pay them to update the application rather than just getting your DBA to change the rules in the database, I've met a couple of vendors who have insisted that lists of values (where the user picks a value from a list) have to be hard coded in the application rather than generated from a lookup on a table so everytime you need a new value you have to pay them to write a patch.

To get back to the original question. I'd say that validation _must_ be done in the database layer, _may_ be done in the application layer and _could_ be done in the client layer (I differentiate between application layer and client layer as, in my world of work, N-Tier is very common). The database, however, when it gets invalid data should raise an error/exception which it then propergates to the application layer for handling. The database may carry out some action as a result of that error/exception (writing it out to a log file, the error plus the data that caused the error, is often a good idea) but it should pass it back to the application, what the application then does with it is up to the application, usually you'd want it passed back to the user, maybe translated to a more human readable message if necessary.

---

I remember attending a presentation at UKOUG Conference a few years ago about implementing business rules via constraints in the database, a very useful presentation.

Preventing record deletion

This entry is partly an aide memoire for me, partly to try to get something that has been keeping me awake for the past hour or so out of my brain so I can sleep and partly in the hope that someone can suggest a way forward.

A quick bit of background. Until April 06 most of our major systems were looked after by an external Faccilities Management company. In April 06 IT was kind of outsourced to a joint venture company, support of the systems transferred to that company and a couple of the DBAs transferred in as well under TUPE. A reccurring problem on one of the systems is that the users keep deleting records which, by law, they must not delete so we (actually one of the DBAs who transferred in who is responsible for that system) has to restore from backup to another machine and copy the deleted records over (she's tried using LogMiner but finds it too unwieldy). The core problem is that the application is faulty and lets the users delete the records when it shouldn't.

The application is closed source from an external vendor so we cannot change it to make it prevent the users from deleting the records. Due to the political environment we've got zero chance of getting the application changed to one that does stop them (the people with authority to replace the app don't have responsibility for the costs of continually putting right the problems it causes and vice versa).

The application logs into the database as the schema owner, individual user authentication is handled within the app, so we can't just revoke delete privilege from the users.

It occured to me just (this is what has been keeping me awake) that we might be able to fix it using triggers.

First I thought of a before delete...for each row trigger to archive to another table before it deletes them so at least restoring the rows is just a case of an insert statement. Then I thought thst times when we might legitimately need to delete a record are massively out numbered by the times we want to prevent a record being deleted so preventing deletion would be much better. Now I'm thinking we need and instead of delete trigger so if someone attempts a delete it won't let them. According to the documentation instead of triggers can only be applied to views so we might have to rename the table and replace it with an updateable view witht he original tablename, not sure how that would impact on the support/maintenence of the app, we might not able able to do it. Assuming we can work around that problem the next issue is what we do when the trigger fires. Do we do nothing, log that somone tried to delete a record or do we raise an exception?

Any thoughts and/or suggestions gratefully received.