-1

I am wondering what your thoughts are on maintaining relationship constraints within a MS SQL DB.

I am migrating a system into a .NET environment from ASP. This brings with it business objects and other tiered-coding techniques, which work to abstract the database from the user/API. The new application has a definite API above an Entity Framework DAL.

The application DB in the old database is large and the purpose of some of the tables will be changing to start containing binary data, in the form of files, etc. I'm keen to split these off into separate DBs to ease management at the client sites where disk space is at a premium.

Is there any value in retaining relationship constraints between tables?

Assumptions:

  • Code is tested
  • Where relations are important, execution is performed under a Transaction
  • Access to the DB is via the API only, other access by third parties is unsupported.

Reasons to keep constraints:

  • Enforces the data structure
  • JOINs are faster?
  • Query Plan assistance?

Reasons to remove constraints in new .NET version:

  • One can assume that the API/BIZ logic would manage relations such as Parent/Child.
  • Reduces opportunity to hive off sections of the DB into other catalogs (the system is built using a Plug-in architecture, most tables may operate in isolation)
  • Am I correct in believing that SQL has to do additional checks during INSERT on constraints, which may be unnecessary when an API above the DB is managing this?
E_net4
  • 27,810
  • 13
  • 101
  • 139
Program.X
  • 7,250
  • 12
  • 49
  • 83
  • By the way, there's no such thing as a dumb question, only dumb answers. So don't worry about being called a fool, that won't (or at least shouldn't!) happen. In all honesty; I've asked myself this constraint-question too. – pyrocumulus Apr 29 '09 at 10:41
  • Indeed, you are correct. One fears the wrath of the pedant! Thanks also for the link, that didn't come on a search - even the excellent AJAXy search when typing in my question. – Program.X Apr 29 '09 at 11:07

3 Answers3

2

Yes I would recommend keeping the relational constraints. Surely you have to handle them in your BLL, but the constraints also improve performance because they are used by the query planner to handle queries more efficiently.

See also this question: Are foreign keys really necessary in a database design?

Community
  • 1
  • 1
pyrocumulus
  • 9,072
  • 2
  • 43
  • 53
1

I don't support the assertion that constraints "are indexed automatically". Only Unique and Primary Keys do. Foreign keys don't, although in most cases I would recommend having an index on the "child" matching the FK's columns. Nevertheless I would still recommend FKeys as they

  1. will enforce your data integrity (I've bitten too many times by the BL having bugs and failing to do the job).
  2. Give the optimizer more information on the pattern of your data, which in turn might lead to better and faster execution plans.

But most of all, have you thought about keeping your data in a single database, but separating it in multiple filegroups? You could achieve your goal a more flexible disk space management without the hassles of Foreign Keys spanning multiple DBs

LeoPasta
  • 300
  • 1
  • 4
  • Thank you. I have revised my answer a bit. While the db engine might not automatically add an index on a FK, the effect is about the same because in both cases the planner uses the information for it's planning. – pyrocumulus Apr 29 '09 at 11:00
  • Good idea on the File Groups. I knew they existed but I am no DBA so overlooked them. Looking good for retaining relationships and using file groups so far. Thanks. – Program.X Apr 29 '09 at 11:09
1

Yes, you should have foreign key constraints on the tables unless you have a specific reason not to. Even if your application is tested, it may well not be the only thing that ever writes into the database. FK's enforce referential integrity on data from all sources.

Foreign keys also act as a sort of informal documentation tool for the relationships in the database. Where FK's are present in a database, I can reveerse engineer the schema with Visio (or any other tool that supports reverse engineering) and see the relationships. Dropping foreign keys on a database in the name of performance is a common technique used by ISV's to obfusticate their database schema and bring in more support revenue.

If you can trace a specific, performance related issue to a foreign key, you may need to drop the key. This is only ever likely to happen on a small number of keys on one or two specific, high volume tables. It is also only ever likely to be necessary on systems with really high transaction volumes. There is never an excuse to have no foreign keys on a database at all.

ConcernedOfTunbridgeWells
  • 64,444
  • 15
  • 143
  • 197