Where to maintain data integrity rules?

Question: In database design , is it better to maintain data integrity rules in the database or at the application level?

Answer: Fixing  data integrity rules is critical to maintaining good data. There are disadvantages\advantages to maintaining in the database or at the application level. I prefer to keep the data integrity rules close as possible to the database.

Every database design decision has a cost

 Keeping the data integrity rules out of the database only means redistributing the cost onto the application. The database will look quicker but the overall application response is not necessarily quicker.

How to minimise Bad Data

Maintaining data integrity rules outside of the database increases the possibility of bad data. For example, using the application will enforce rules, but if a user accesses data through another application such as SQL Server Management Studio  and edits data this could cause bad data.

DDL is king of data integrity

DDL should be the start and finish of all data integrity. It is the only way I know ,for sure, that data will follow the rules.  If an application attempts to override the rules , it cannot. A solid database designer can construct rules that maintain a data entity , as deigned.

Read More on data management rules

SQL Server - ISO-11179 Naming Conventions and SQL DDL

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server Performance Killers

Stored Procedures versus ad-hoc paramaterized queries.Which is faster?