4

The reason I ask is that we'd like to use a certain CHECK constraint which MySQL currently doesn't suport. Without this type of constraint in place, the whole reason for using foreign keys and referential integrity seems to diminish as the application code takes on more of the database's responsibilities.

If we were to create a 'dumb' data model and move all of the referential integrity checking to a layer in the application code, then potentially testing could be simpler as referential integrity errors would be trapped in the application rather than the db. It could also potentially speed up development of new modules, as they wouldn't necessarily have to be referentially complete (is that a term?) before testing.

So, are there any other benefits to sticking with a 'proper' data model in MySQL and keeping foreign keys and 'ON UPDATE CASCADE' statements, etc?

Or, should we ditch MySQL and move to something else?!

Thanks!

Bendos
  • 255
  • 2
  • 11
  • 1
    I think referential integrity slightly differs from validation constraints. – newtover Jan 16 '12 at 17:17
  • @newtover, of course they're different - we just don't see the benefit of using RI when we can't have validation, yet we could have both if they were all in the application... – Bendos Jan 16 '12 at 17:22
  • 2
    If you are committed to using a free (as in beer) software, have you considered [PostgreSQL](http://www.postgresql.org/docs/8.1/static/ddl-constraints.html)? – Nick Chammas Jan 16 '12 at 20:00

3 Answers3

7

Some developers advocate having no business logic at all in the database -- your dumb data-store. So that is definitely a valid strategy.

What concerns me about moving the constraints (and other business logic) out of the database is that it is more difficult to enforce constraints everywhere. Every single developer in every single application can violate that constraint. The DBA can't help, either.

So, I lean toward having these rules in the database itself.

DOK
  • 32,337
  • 7
  • 60
  • 92
  • 7
    Tom Kyte (Oracle Expert) has stated this rather nicely once: "*Your application won't be the only one and it won't be the last one to use that data*" (if that data is of any importance) –  Jan 16 '12 at 17:16
  • Thanks @DOK, I know you're right, but perhaps it's become common to think that the DBAs are different people from the developers... In my experience (in small projects, to be honest!) they are the same person and therefore just as capable of mucking up the database as they are the application :) – Bendos Jan 16 '12 at 17:24
2

You can emulate a check constraint in other ways

  • Trigger
  • FK to a single row table
  • Single value enum

It's inconvenient, but not the end of the world.

Not all constraints can or should be done in the application anyway. Uniqueness? Zero-sum checks (eg balance = zero before account is deactivated).

It's your data and your mess to clean up when it goes wrong...

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Hi @gbn, thanks for your comments. We've considered using triggers for some validation checks, but they always seemed like an afterthought compared with true CHECKs. I'm aware of the risk of orphaned records, update anomalies, etc if the db doesn't maintain its own integrity, but I'm not talking about removing it completely - just shifting it to a different layer. As I mentioned in the comment to DOK, quite often the DBA _is_ the developer, so it's his/her mess whichever mechanism they use! – Bendos Jan 16 '12 at 21:16
0

Yes, if you need a feature that is not supported by MySQL then you chould move to something else.

jeanreis
  • 898
  • 8
  • 20