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

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