16

Should items like Foreign Keys, Constraints, Default Values, and so on be handled by the database management system (in this case, MS SQL 2005) or the application? I have heard opinions from both sides and I'm honestly not sure which way to go.

There is a chance we will be spanning multiple servers/databases and I don't think Foreign Keys can be used across linked servers. In addition to that, there are some circular references in the database design which prevents me from using ON UPDATE CASCADE on everything.

The database is MS SQL 2005 (possibly 2008) and all interactions with it should go through the application.

Thomas Owens
  • 79,623
  • 18
  • 192
  • 283
Rachel
  • 23,979
  • 16
  • 91
  • 159

5 Answers5

10

If there's any chance that the DB will be modified outside your application, you want the constraints in the database. If the DB is and always will be nothing more than the back end of the application, you can leave them out, although I'd document them just in case and probably just keep them in if the performance hit wasn't too bad. (Peoplesoft software works this way - the constraints are in the software, and (I'm not making this up) it runs everything as SYS on Oracle.)

You want things like that to be monitored by the application, so it can react intelligently and not at best parrot back a database error message to the user.

And, yes, this is double coverage, but without it you're probably going to get preventable data corruption or a bad user interface.

David Thornley
  • 20,238
  • 2
  • 55
  • 82
5

Ideally, both. You shouldn't not have the DB handle it, but then again, if the app comes up with data that the DB will reject, that's a runtime error, so the app should have at least some code dedicated to preserving referential integrity. Also, setting up the right constraints in SQL in the DB is much simpler than setting up code for it client-side, so doing it on the DB greatly reduces the amount of work you need to do.

Mason Wheeler
  • 82,151
  • 24
  • 234
  • 309
1

If it's important, let the database handle it. That way, you don't have to worry about somebody accessing the database outside your application and changing or entering some inconsistent or duplicate data. Unless it's some high-level application-specific stuff (like "only users in department X having an access class of ZZZ should be allowed to belong to group 999"), but that's not usually referred to as "referential" integrity.

TMN
  • 11,313
  • 1
  • 21
  • 31
1

Both is definitely the way to go. You'll want validation logic in your code to prevent bad updates and inserts as well as to tell users what was wrong and how to fix it. And having the database there to back-stop things so if something came in and did not run through the validation it would not break things down the line is a good thing.

You should keep the database stuff higher-level. EG, enforce referential integrety and perhaps some not null. But don't worry about constraining lengths or formats as that is best rendered unto the application wholly.

Wyatt Barnett
  • 20,685
  • 50
  • 69
1

I would say put in the database. If you're using a persistent framework, It would pick up the keys automatically.

Lareau
  • 301
  • 2
  • 5