Re: How much of data constraints on database is enough?

I agree w/ the all the above.

The database needs constraints to prevent inconsistencies being
introduced by external clients (i.e. not going through the main
application.) Its way to easy for db admins to corrupt the data.

Constraints only in the application are too easily circumvented,
either by ignorant application developers or db admins. Inconsistent
corrupt data is just too painful to cleanup, and its so easy to
prevent w/ a real DBMS that it seems foolish to turn it off. Its like
compiling C/C++ w/ warnings disabled. Why shoot yourself in the foot?

I like application-level constraints as user-friendly validation
feedback. For example, Rails (or WebObjects, etc.) provides consistent
user-friendly error messages for validation errors ready to display or
parse by non-human clients.

All this being said, I am not happy w/ having to maintain two parallel
sets of constraints. It violates the Don't Repeat Yourself principle.
I'd like some DDL to generate schema definitions with constraints as
well as be picked up by the Rails application code.

Drew

On Jan 16, 2008, at 6:24 AM, Josh Berkus wrote:

> Miho,
>
>> Being said that, you also have choice to do the same in your
>> application as well. Why do you want to have them in 2 places? Maybe
>> some constraints are appropriate to be done in application, some
>> should be on database. But where do you draw the line? Might as well
>> do them all in application!?
>
> The answer is that data integrity, like security, is a process, not
> an end
> result. To extend the parallel further, only checking data
> integrity in your
> input code is like implementing network security by having only a
> firewall
> and no internal security. That's a strategy called "perimeter
> defense only"
> and is justly attacked within the security community; all it takes
> to defeat
> it is one employee with a virused USB stick. Likewise, if you
> *only* do data
> integrity in your interface or ORM code, then you risk having some
> employee
> dash off a bit of careless SQL, or one bug pop up in your ORM code,
> and
> irrecoverably corrupt your whole customer database.
>
> Sure, it's more work to have data integrity checks in the input
> code, the
> middleware, table constraints, and triggers. But it's also more
> work to have
> firewalls, passwords, multilevel permissions and secure network
> protocols on
> your internal network too -- yet any smart company does all of these.
>
> --
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco