Migrating DB: Keep relations constraints?

Question

At the risk of being called a fool, 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 unnecassary when an API above the DB is managing this?

I throw my question upon the community anyway in case I have missed something dumb or if this is just a nightmare waiting to happen ...

Many thanks,

Nathan

Answer 1

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: http://stackoverflow.com/questions/18717/are-foreign-keys-really-necessary-in-a-database-design

Answer 2

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

Answer 3

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.