Is there any reason to build constraints between tables (inside SQLserver) nowad

Question

Is there any reason to build constraints between tables (inside SQLserver) nowadays? If so, when? Most applications in my area are built on object principles and tables are joined on demand. Demand are based on the need from the application. I won't load a bunch of constrained tables for a simple look-up, which in turn (after action) require one another simple lookup.

ORM tools such EntityContext, Linq2Data, NHibernate are also handle the constraints by themselves, at least you know what tables that need each other. Doing constraints inside the server is just about making (forcing) same changes twice?

This is usually not a question up for decision, but this database is designed quite different. The design looks regular good, mostly mirrored the objects used by the applications. What disturbing me is all constraints configured inside SQLserver with "not cascade". Which means that you have to play "seek and find" when coding new database queries. Some cases requires up to 10 levels of an exact order to make a single delete.

This surprises me and I'm not sure how to handle it.

In my simple world, that setting makes the constraints losing most of the purpose. OK if the database were accessed from hosts without knowledge of the design.

How would you act in this scenario?
Why not just remove all constrains from db and keep them at application level?

Explanation / Answer

Two general reasons not to remove contraints from DB:

It may be accessed by more apps, now or in the future, which may or may not use ORM. Even if the developers of those apps faithfully duplicate all the constraints there (which may be significantly more difficult using lower level non-ORM solutions), it is always extra work. And if not, even one small omission is enough to break schema integrity... which is something you don't want to risk. In most companies, the data stored in their DB is the lifeblood of their business, so its integrity must be ensured by any means. And the tried and proven best means to achieve this is to implement as many constraints in the DB as possible.
The query optimizer relies a lot on the constraints known on the DB level. If you remove constraints, query performance may start deteriorating. You may not immediately notice it, but one day it is going to hit you, and by then it may be too late to fix it easily. The nature of things is that DB performance tends to break down at peak load time, when there is the least possibility to make careful, well thought out design improvements, backed by exact performance measurements and detailed analysis to pinpoint the root causes.
Your concrete case sounds like the DB schema may have been originally generated by an ORM tool (or designed by someone not very experienced with the relational world), so it is suboptimal from the relational point of view. It is probably better to analyse and improve it towards a more "natural" relational design, while keeping it consistent with the ORM views. It may be useful to involve a DB expert in this analysis.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.