Click here to monitor SSC
  • Av rating:
  • Total votes: 30
  • Total comments: 29
Sam Bendayan

Constraints and the Test-Driven Database

14 December 2011

Bad data always seems to appear when, and where, one least expects it. Sam explains the great value of a defensive approach based on constraints to any team that is developing an application in which the data has to be exactly right, and where bad data could cause consequential severe financial damage. It is perhaps better seen as creating a test-driven database

 Automated Unit Testing for database development has recently been promoted via the Agile Programming Methodologies. Although Test-Driven database development is an excellent discipline to adopt, it should be considered alongside the well-established practice of using Database Constraints. By using constraints, we already have the means to create test-driven databases. Database Constraints are, essentially, just tests. They are tests of Data Integrity. They also conform to Agile testing practices in the sense that they are usually designed first, before you write a single line of code, in a similar way to “Test-First Development”.

Database Constraints offer enormous value to any development project. Opting not to use them is equivalent to deciding not to test the output of your code.

Why are Constraints Important?

By definition, a “Database Constraint” is something which restricts the possible values which the database will accept. Constraints are declarative, which means that you declare them as rules that MUST be adhered to by the entire system you are developing. They are also bound to the database structure and fire automatically when needed, which makes them difficult to violate. Any violation of these constraints results in a “hard failure”, which returns errors to the application and forces the application-developers to deal with the issue immediately.

Database Constraints are a valuable by-product of the practice of identifying and clarifying beforehand those data rules that you need to follow before you develop the rest of your application. These rules depend on the nature of the business being supported by the application. They could be as trivial as checking the validity of an email address or zip code or as complex as checking for compliance with IRS Tax Rules. This process encourages the team to come to a consensus about what the business rules mean, which is surely one of the most demanding parts of any IT project.

If you can enshrine these rules in constraints, the database will alert you if you happen to violate any of these business rules in the heat of the development effort, and make you fix the issue before it gets out to the customer.

Large software teams in complex projects are faced with the daunting task of ensuring that everyone who is involved in the project has a clear comprehensive idea of all the rules that the system must adhere to throughout the development cycle. With constraints in place, this task becomes largely unnecessary because database constraints provide a safety net which gives the development team the confidence that they could not unwittingly violate any business rules.

So What Are Constraints?

There are several different types of database constraints:

  1. Primary Key & Unique Constraints – enforce uniqueness of rows in a table
  2. Foreign Key Constraints – enforce Referential Integrity (“lookup” values)
  3. DataTypes – enforce proper data type values (date, number, string, etc.)
  4. Nullability Constraints – determine whether a value is mandatory or not.
  5. Check Constraints – more flexible constraints that can be bound to a column or table and can be used to enforce a wider variety of constraints that cannot be enforced in any of the above constraints. However, the SQL that can be used in these is limited to smaller expressions.
  6. Triggers – the most flexible form of constraint. These can only be bound to a table but are free to use the full power of SQL, almost like a Stored Procedure.

What about Stored Procedures? Although they certainly aren’t constraints, these are often used to perform data validation tasks that are impossible to implement by a constraint. They are not bound to the database design, which makes them easy to use “after the fact” when you realize that the database design is faulty and you have to retrofit data validations into it. For the purposes of this article I will not consider these as constraints because they do not fire automatically.

So let's take a simple example. You are building a database for a retail seller and you have an Orders table which stores the orders placed by your customers. In that table you have an Order Date column. When thinking about the database design, you decide that you will not allow orders that have an order-date in the future, so you write a constraint that doesn't allow future dates to be put into this column. In SQL Server, this rule will take the form of a “check constraint” and will look something like this:

ALTER TABLE dbo.Orders ADD CONSTRAINT Chk_Orders_Date CHECK (OrderDate <= GETDATE())

With this one simple line of code in your database, you can now stop worrying about this rule. It will not be possible to enter future-dated orders. Any code trying to do so will 'error out' and you will be forced to fix it immediately. You can actually declare to your customers (or anyone else) that your application follows this rule and be completely confident in your statement!

Are Automated unit Tests sufficient?

Why not just use automated unit tests? Well, you should be using automated unit tests, but they will not replace Database Constraints. Database Constraints have a special contribution to the effort to reduce defects in software.

  1. Constraints fire automatically, all the time whenever the application is used, so you can't get around them. Automated tests fire on demand only, so if you don't run the test suites they can't find any bugs.
  2. Constraints are shipped out to customers and run on Production environments, as part of the product, whereas Automated Tests are usually only run on Development and Test environments.
  3. Constraints focus solely on the data, whereas Automated Tests will more likely focus on an entire unit of code (hence the name Automated “Unit” Tests). Therefore, unit tests have a much wider area to cover and will not be able to focus on data as much as constraints will. This is why it’s a good idea to use both constraints and automated unit tests. They have different areas of focus.
  4. Constraints have “positive” logic; they define what “good data” is and everything else that falls outside of this is automatically considered to be “bad” data. Because of this it is not necessary to find all the different scenarios which might cause bad data. All you need to define is the one (or few) scenarios that constitute “good” data. Automated tests have opposite type of logic; they try to break the application by defining all the scenarios that may be considered “bad”. In my experience there are many, many more such scenarios, and many tests are initially left out of these test suites initially because it is very difficult to come up with all the “bad” scenarios that your customers are going to think of.
  5. Constraints don't need any additional hardware or separate suites of code to function. Constraints typically have only a few lines of code as opposed to Automated Unit Test Suites which can be huge, and require you to install new technologies and be trained in their programming languages. Finally, you have to dedicate environments to run these test suites and wait until they are finished, which can be anywhere from minutes to hours. Database Constraints don't have any of these problems; they run within your existing code base so no additional hardware or technologies are necessary.

You can get around Database Constraints by dropping them, but in the production environment your customers would not drop DLLs or web pages, and likewise shouldn't drop constraints. If any type of test fails then either the test was wrong or there is a bug in the application. To drop a failing test is a way of “shooting the messenger” and ignoring the true cause of the error. A well-worded SLA will keep customers from dropping constraints if you declare up front that dropping database constraints will invalidate the support agreement.

Constraints don’t necessarily slow the database down. While it is true that constraints have to fire as part of the application, they are designed to do this and are very efficient at it. Constraints can actually speed up the database because the SQL Query Optimizer has access to these constraints and can use them when it decides how to optimize queries. Let’s take the example from above, where we were not going to allow any orders with future dates. If someone then runs a query looking for future dated orders, the database knows that it is not possible to have that data in the database and immediately returns an empty result set, without even trying to run the query.

The Impact of Database Constraints on the Development Process

‘Beating all the points to death’

As with any rigorous test regime, implementing Database Constraints has a profound effect on a software project’s lifecycle. Testing an application is impossible without a sound definition of how an application should function. To implement constraints, the software project has to start with a painstaking Database Design process, during which the customer sees no obvious progress. No screens or web pages are produced during this phase, and the more inexperienced team members can become impatient. Considerable pressure can be put on development teams during this phase to start ‘cutting code’.

All those design debates are necessary because the team will spot issues that customers will bring up later on in development if you don't fix them now. This is the perfect time to argue, since the design only exists as a drawing; if the design is inadequate, all you have to do is erase it from the board! No code needs to be refactored, and no customers are impacted. Thus it makes sense to fully argue and “beat all of the points to death” during this phase. In addition to this, it is extremely important to choose the right people for the design phase, supported by a project manager that understands software design. It is the quality of the people involved that will ensure you don’t end up running around in circles, which is a common pitfall of such design phases.

The constraints should be preserved

Application developers get frustrated with database constraints very quickly because the constraints constantly get in their way. This is understandable; developers want to get their job done as fast as possible, and since many of them are typically not involved in the design of the constraints they don’t understand why the constraints have to be there in the first place. Management needs to facilitate at this point, just as the construction foreman serves as the final arbiter between different parts of a construction project. A decision to drop the constraints of a database is difficult to reverse later on, and the longer you wait the harder it is to justify going back and redoing everything.

Once the application is in production, and data errors do happen, the team may need to make sure that the constraint wasn’t too restrictive, needing to be adjusted to make it more flexible. However, if the constraint is correct and the application has a bug that wasn't caught in testing then that bug needs to be fixed. Whatever is decided, no data needs fixing because the bad data never got into the database in the first place!

However, if a constraint is missing and bad data does get in, it is considered a failure on the part of the requirements team and then the application bug and the data have to be fixed. However, even in such a case this data fix is much more unlikely to be harmful because there are so many other constraints in the database which will prevent the data fix from damaging any surrounding data.

What if you don’t use constraints?

So what happens if you decide not to use Database Constraints? At first, Management is happy with your progress as they see screens or web pages appear quickly in your demos and they even see them working. All is well, and when the product is considered “done” it is tested and shipped out to the customer. Then the troubles start.

Customers will find unexpected and creative ways to use your software and start putting bad data in. Customers might also import data into your application by going straight to the database, thereby bypassing all of your front end and middle tier data validations, and even the ones in stored procedures, because they don't fire automatically. This is why stored procedures aren’t able to implement real constraints. So the company starts to hire support professionals to fix these issues as they come up but, over time, the data issues increase; you add more customers, the product gets more complex, new developers are hired that don't have all the business rules inside their head, and usually there is not enough documentation to give them. So developers violate the business rules, as do support people when they have to fix production data in a high-pressure environment. The database, of course, says nothing about all these violations, since it doesn’t have the constraints. And what do companies do at this point? In my experience they accept this situation as the normal cost of doing business! Everyone complains about how bad the database is, but usually no one does anything about it because at this point it is too late.

In addition to the above, bugs have to be dealt with many times instead of just once, and the “fixes” to the data will likely end up damaging the surrounding data, causing more bugs. I have seen cases where entire departments of support people are hired to deal with these data issues. They end up developing entire libraries of data fix scripts to be run in production, and they run those fixes on a daily or hourly manner. Usually they have someone that manages these script libraries as well, resulting in a type of “parallel development effort”, just to fix the data bugs that the Development department allowed in the product because they didn’t use Database Constraints!

Conclusion

Good software design of database applications is very difficult to achieve without using Database Constraints. Development projects are very complex and one must have separation of concerns in order to make the project manageable. One of the best ways to have this is to research the business rules first and put them in the database in a way that they cannot be subverted. Later on in the project there are many things to worry about; the development of the UI and middle tiers, any frameworks that have to be developed, and, yes, there are even more data validation rules that take place only at the middle tier and UI. You don’t need to add the issue of database validation rules to this long list of problems that the development team has to deal with during the development phase.

I have observed first-hand the cost of deciding not to use database constraints in database design; it results in frustration and confusion all the way from management to the customers. Costs explode as the product scales and the customer base grows. While automated Unit Tests are another weapon in the fight for application quality, they are truly effective only when combined with proper Database Design with constraints. Although the process of getting the design right and resilient against incorrect data during the early phases of design and development is painful and its benefits are difficult to measure, everyone will be happier with the result in the long run.

Sam Bendayan

Author profile:

Sam is a database professional with 15 years’ experience in database development and architecture.He currently works at Ultimate Software as a database architect for UltiPro, an industry-leading Payroll/HR (HCM) application. He is a big fan of Data Modelling and likes to get involved in the entire spectrum of database development activity, from the requirements gathering phase all the way through development, testing and deployment. Outside of work he spends most of my time with his wife and 3 children and tries to head to the outdoors whenever he gets a chance. He is also a big fan of traveling.

Search for other articles by Sam Bendayan

Rate this article:   Avg rating: from a total of 30 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: But, Did You Do It?
Posted by: BuggyFunBunny (view profile)
Posted on: Tuesday, December 27, 2011 at 7:33 AM
Message: -- I have observed first-hand the cost of deciding not to use database constraints in database design

I completely agree. In my experience, however, the hoard of client-side coders bleated that the App Will Crash if the database is overloaded with "logic" and the only way to edit data is on the UI. The scenario in the quote is always blamed on incompetent DBAs, not whining C#/java/PHP UI coders!! So, have you ever been able to implement what you describe? (If so, are they hiring? Telecommute is fine.)

Subject: well written article...
Posted by: DeafProgrammer (view profile)
Posted on: Tuesday, December 27, 2011 at 5:25 PM
Message: Data lives in database, but UI doesn't. Database provides a connection to UI/application. I complete agree with you that stored procedures aren’t able to implement real constraints. However, they do lives in database and they can be dropped or changed without affecting the database structure or data at all. Application developers are really silly people of spending their years on developing the so-called "front-end UI and middle tier business service layers" for the database front-end clients. I have seen an article somewhere that says the life span of a front-end application is a minimum of 1 year. Damn, no wonder why that I called them (Project Managers/Technical Architects/Developers) the money machine people!!!

Subject: Where data integritiy is a serious business
Posted by: Phil Factor (view profile)
Posted on: Wednesday, December 28, 2011 at 2:46 AM
Message: @BuggyFunBunny
Sam works with Payroll systems. There is a world of difference between this kind of data and the sort of data you play around with using Hadoop and MongoDB. In a financial system, a single simple mistake could cost your client many hundreds of thousands of dollars. I can bet that Sam's management are keen on very strict data discipline!

Subject: Legacy systems?
Posted by: Edafe Onerhime (not signed in)
Posted on: Wednesday, December 28, 2011 at 3:11 AM
Message: I agree with many points, I like constraints in the database and for greenfield development, this is easier to enforce.

Where it starts to fall apart is applying these to legacy systems where rules have changed or dirty data already exists.

How would you guys go about resolving this?

Another point of contention tends to be persuading the powers that be that data quality is important, not pedantic. If you can't quantify it in terms of a direct hit on the organisation's bottom line, getting buy in is difficult.

Any tips on how to build a more persuasive arguments for cleaning up and enforcing good data practices for reluctant customers?

Subject: Constraints - Hadrian's Wall Revisited
Posted by: @SQLCowboyUp (not signed in)
Posted on: Wednesday, December 28, 2011 at 6:44 AM
Message: Properly designed data structures, armed with constraints, keeps data safe from the hoardes of lawless, developer-barbarians.

Thanks for a return to this timeless topic.

Subject: Constraints - even when designing databases for the sciences
Posted by: Rupertsland (not signed in)
Posted on: Wednesday, December 28, 2011 at 7:58 AM
Message: This was an excellent article.

I am a biologist, but my role is that of database architect and database administrator for a medium sized environmental consulting company in Canada. I'm using SQL Server. Being chosen to develop an environmental database for a major client, I knew early on that constraints were an absolute necessesity to stop the "bad data" from getting in. I also enforced referential integrity on look-up tables and have followed nearly all the rules outlined in the article.

In the sciences, unlike in the business/financial world, one encounters data that exceeds or falls below detection limits (i.e., you get the frustrating job of dealing with <n and >n values that creep in here and there). These results can not be used in calculations, and yet they need to be preserved somehow. Imposing a constraint to force the user to enter a number creates a problem. How do you enter a result that says <1.0 or >5.0? Or does one store all the results as text? There is also the very nasty problem with decimal places and storing trailing zeros to preserve significant digits. All databases chop of the trailing zeros, unless you store the results as text, which means you can not use the data in calculations, unless you convert the results back into a number and strip off any signs.

Any tips for handling this kind of data?

Subject: Favorite Constraints
Posted by: Michelle A. (view profile)
Posted on: Wednesday, December 28, 2011 at 8:18 AM
Message: There are a few standard check constraints that I always implement and my developers stopped fighting with me about them. I always use char(10) for phone numbers and a function that verifies that each character is numeric. I do the same with zip codes (different lengths, obviously). I was once told that a database of mine 'did too much' because of the constraints that I had in place. Fortunately, that developer no longer works for my company.

Great Article!

Subject: Stored Procedures
Posted by: JasonJenkins (not signed in)
Posted on: Wednesday, December 28, 2011 at 10:13 AM
Message: If you carefully use users and schemas to shield the underlying tables so that, outside of the development shop, tables can ONLY be updated via stored procedures, then stored procedures are perfectly capable of implementing real-time constraints (as long as the DBA him/herself applies the rules consistently when creating multiple stored procedures that overlap in terms of the data they update).

Subject: To Rupertsland
Posted by: JasonJenkins (not signed in)
Posted on: Wednesday, December 28, 2011 at 10:16 AM
Message: For storing fixed-precision numbers, check out the decimal/numeric data type.

For results that might be as imprecise as <1.0 and such, well, if you intend to use the values in that column *as numbers* (for example, to compute averages or sums), then you can't allow inputs like that. But if the requirement is to capture the information as available, then make it a character data type, and forget about using the data as numbers. The problem isn't in the SQL Server or database theory in general, it's in the underlying application domain.

Subject: constraints involving multiple tables
Posted by: Henno Vermeulen (not signed in)
Posted on: Wednesday, December 28, 2011 at 10:28 AM
Message: Very interesting article! Currently we validate the more complex constraints in our Java application before inserting/updating the database and not in the database. We actually use an ORM tool (OpenJPA) to completely generate the database schema from the domain model.

I do wonder how you would use SQL server to enforce certain cross-table constraints. For example we have some kind of "package deal" where multiple different products (wines) can be ordered for a fixed price and the user is free to determine per order which wines should be included in the package deal.

We have implement this by adding an order line for the package deal with the total deal price, followed by multiple order lines for the wines that have this order line as a "parent" and get price 0 instead of their usual price.

We must then enforce some rules which involve two or three of these entities/tables: Order, Product and ProductOrderLine:

- the price of a ProductOrderLine must be 0 when it refers to a parent ProductOrderLine which refers to a Product that is a package deal

- a ProductOrderLine (for a single wine) which refers to a parent ProductOrderLine which refers to a Product that is a package deal must refer to the same Order as the package deal

A drawback of putting these kind of constraints in the database is duplication of business logic in the application which is very hard to get rid of: we have a rich desktop client and for a good user experience we wish the application to set the price to 0 at the moment the user adds a product to the deal in the UI.

(We already manage to not duplicate this logic between client and server applications by using the exact same domain model classes)

Subject: Business Logic...
Posted by: DeafProgrammer (view profile)
Posted on: Wednesday, December 28, 2011 at 11:19 PM
Message: Henno,

First thing as usual is to analysis the business logic for the solution in regards to either set up a constraint in table structure or write codes in stored procedure or nor trigger. I often use business rule to write codes mostly in stored procedure where they are often changes during the life time of the database. Therefore dropping or changing the stored procedure that resides in the database does not affect the database or data at all.

Cheers,

Subject: RE: constraints involving multiple tables
Posted by: Geoff Miller (not signed in)
Posted on: Saturday, December 31, 2011 at 2:50 PM
Message: Henno,

Using indexed views (with a unique index) can be used as a constraint when the logic needs to span multiple/joining tables.

Subject: Numbers
Posted by: Pete Danes (not signed in)
Posted on: Sunday, January 01, 2012 at 3:00 PM
Message: One way to store trailing zeros to scale them. If you need absolute accuracy to 6 decimal places, mutiply all values by one million before storing as a numeric. Naturally, there is the possibility of blowing off the other end of the scale, by overflowing the maximum allowable value for a numeric data type, so this trick will not work with all possible data, but if you know your dataset well enough, this might be an option. Another possibility is a separate field storing the number of verified places. Fld1 = 17.34, Fld2 = 6 --> 17.340000, i.e. 17.34 accurate to six decimal places.

Subject: Numbers
Posted by: Pete Danes (not signed in)
Posted on: Monday, January 02, 2012 at 12:32 PM
Message: One way to store trailing zeros to scale them. If you need absolute accuracy to 6 decimal places, mutiply all values by one million before storing as a numeric. Naturally, there is the possibility of blowing off the other end of the scale, by overflowing the maximum allowable value for a numeric data type, so this trick will not work with all possible data, but if you know your dataset well enough, this might be an option. Another possibility is a separate field storing the number of verified places. Fld1 = 17.34, Fld2 = 6 --> 17.340000, i.e. 17.34 accurate to six decimal places.

Subject: Numbers
Posted by: Pete Danes (not signed in)
Posted on: Monday, January 02, 2012 at 12:32 PM
Message: One way to store trailing zeros to scale them. If you need absolute accuracy to 6 decimal places, mutiply all values by one million before storing as a numeric. Naturally, there is the possibility of blowing off the other end of the scale, by overflowing the maximum allowable value for a numeric data type, so this trick will not work with all possible data, but if you know your dataset well enough, this might be an option. Another possibility is a separate field storing the number of verified places. Fld1 = 17.34, Fld2 = 6 --> 17.340000, i.e. 17.34 accurate to six decimal places.

Subject: Edafe Onerhime
Posted by: Anonymous (not signed in)
Posted on: Monday, January 02, 2012 at 1:50 PM
Message: Edafe Onerhime:

I have done a number of legacy conversions, usually on live systems that are still in use and accumulating additional erroneous data while I am trying to fix things. Throwing a tantrum and demanding the systems be halted while I fix them is just not an option.

My situation is somewhat simplified by being in-house with the end users, and often personal friends with them as well, but I believe that the general tactics are applicable elsewhere.

What I do is write loads of action queries and scripts that investigate and correct various problems, and the same for post-conversion assessment of the effects. I don't do the conversion in place, but into another copy of the database. I have code to run the scripts in a pre-defined order, so that as I uncover problems, I can add scripts in arbitrary locations (THIS has to be fixed after THAT, but before THIS OTHER THING…) automated to run from a single 'GO' command. This horde of scripts examines, corrects and reports on what happens during the conversion. I then send each user the section of the report that explains what's wrong with his portion of the data and they can repair it at their leisure. My target database has all the necessary constraints and restrictions necessary to prevent bad data from getting in, and each conversion run starts with emptying the target database and does the complete conversion from scratch.

Users can continue to use the existing system while fixing their erroneous data. Some new errors inevitably creep in, but less than what gets fixed, so the total number of errors gradually declines. Some uncovered errors I can fix for them in place with a simple mass update query, and I do so whenever I can, so they see I am doing what I can to help out, instead of just imposing rules whose purpose they may not understand (although I'm happy to explain when they want to know). Some it turns out may not be errors at all, but are misunderstandings - those are easy. Some may be very difficult or tedious to fix in place, but I can deal with them via an action query during the conversion - I simply add the appropriate code to my list of conversion routines. Other errors often crop up during this process and I add constraints or conversion code as necessary to deal with them and keep the newly discovered errors out of the new database. This process is repeated as often as needed, for as long as needed (one such went on over two years), until everyone's data is either clean or fully convertible.

At the same time, I build the new application, testing it on as many guinea pigs as I can pester into trying it. The users are pretty good about helping with tests and suggestions, since they see firsthand how I am building it, soliciting their input, trying to accommodate the needs of their workflow as they want it to operate and just generally tailoring it as exactly as possible to their desires. At some point, my conversion routines report that all existing data is ready for the jump and the application is as tested as I can possibly make it. A meeting is called and everyone is notified that we're going live. If there are no last-minute objections, I take the existing database offline, run my conversions one final time and if there are still no new problems, I go live with the new setup. (Sometimes I do this after hours, to minimize impact on users.)

Naturally, there will always be some problems with the new application, but they tend to be minimal, because of all the testing and user input during the conversion process. I have a cell phone, I'm on site and tell the users to CALL ME, immediately, no matter what, if something isn't right. By this time, they generally have a pretty good grasp of how the database operates - everyone is involved, right from the start and I do as much to educate them as they can stand, sometimes maybe even a bit more. They're pretty up to speed on what is supposed to behave how, and they understand their own data (even if not the intricacies of database internals), so they are quick to spot errors and let me know immediately, before the errors can propagate to other places.

It's not simple, but this business of building a set of conversion scripts from the existing database to a new, empty one, configured with all proper safeguards has served me well many times. The scripts sometimes run into the hundreds, in several languages - whatever I consider the best tool for the job. It can be convoluted, but I'm a solo act and can do it how I want. Its benefits are that I can adjust and tune the conversion and repeat it as many times as necessary, and that the users can continue to work with existing system until the new one is completely ready. I shudder to think how many times I ran the conversion on the two-year project - thousands, certainly. That project is still in daily use and under my care, and I continue to use the same technique to maintain it. The first conversion was from Access to SQL Server, merging about twenty individual databases into one coherent whole. Today it's all in SQL Server, but new needs still crop up and I still use the same method - build a new database with the modified structure and a set of conversion routines. When the conversion routines report that all is well, I do an after-hours conversion and deploy the new version of the app (it has its own mechanism to detect and download the updated version, so users don't have to do anything except click 'Yes, I want the new version' next time they run it). The impact on users is almost nil, except for those directly involved in some data modification.

Hope this gives you some ideas - I know, all too well, how difficult legacy systems can be.

Subject: Constraints
Posted by: Pete Danes (not signed in)
Posted on: Monday, January 02, 2012 at 2:10 PM
Message: Constraints in the database are vital. No database should be without them and any developer who complains that they are interfering with his work should be flogged - if they are interfering with what he is trying to do, then what he is trying to do is put bad data into the database. Would you design a car without brakes because someone wanted the floor space where the brake pedal should live? I am self-employed, so I can pick and choose my projects, but even as an employee, I would refuse to build a database if I was prohibited from using constraints in it.

Subject: Peter, Peter, Peter
Posted by: BuggyFunBunny (view profile)
Posted on: Tuesday, January 03, 2012 at 4:42 PM
Message: have you no respect for those hard working coders who seek merely to remove you from the process? I did that, and now I'm also self employed. Worth it? Yes.

Subject: Constraints...
Posted by: DeafProgrammer (view profile)
Posted on: Wednesday, January 04, 2012 at 4:38 PM
Message: What happens if the coder got hit by the bus? In terms of three-tier architecture concept, all business rules are written by coders. The most important fundamental part of the programming work is the comments. Obviously, most of the coders including me sometimes couldn't be bothered commenting in code modules. Business Analyst tells coders of what the "business logic" do. Coder received this information from specification and verbal communication via the Business Analyst. This process is like walking on a thin ice.

In terms of database concept, all business rules can be either written in T-SQL code or used in different type of constraints. Constraints in the database are designed for logic or business rules. These "business rules" lives in a database permanently. SQL products provide constraint methods for data storage that has to be constantly validated on use and turn it into a clean data.

The format of written SQL language has been used for decades. It is a set-based thinking.

Subject: constraints/performance
Posted by: Andre (not signed in)
Posted on: Monday, January 09, 2012 at 12:12 PM
Message: Well, one thing to consider is that the constraints do tend to affect performance.... especially triggers. once you get past certain data size, there is a need for heavy optimization of constraints and delegating most of them to stored procedures, and securing the tables to go only trough stored procedures.

Subject: constraints/performance
Posted by: Pete Danes (not signed in)
Posted on: Tuesday, January 10, 2012 at 12:02 AM
Message: Performance is important, certainly, but data integrity much more so. Far better a slow database that gives correct results than a fast one with mistakes. If all you want is quick answers, and don't care whether they're correct, you don't need a database at all.

Subject: Some constraints best on db, some have to be on app
Posted by: Verbose (not signed in)
Posted on: Wednesday, January 11, 2012 at 2:54 PM
Message: I do the database and app development, so I have the advantage of seeing both sides. Certain conditions are hard to check on the app side, but easy for the database--an example is unique constraints. The app developer has to know how to parse the error returned by the failed insert/update statement and present a coherent message to the user instead of letting the exception go unhandled. It's not hard to do, but requires that the data layer be structured to allow it.

I don't think databases can enforce the common constraint that a master must have at least one detail. Initially, the master is inserted without a detail because detail depends on master via FK. Then details are added. So the state of master without details is temporarily a valid state. A good way for the app to handle this is to send the entire master/detail set to proc at once, using TVP for details. Proc uses xact to ensure that entire set of inserts succeed or fail together. The app can achieve the same thing with xact on the app side, but many developers don't know how to program this without throwing unhandled exceptions.

Subject: If Your Parents Had No Kids, Odds Are You Won't Either
Posted by: BuggyFunBunny (view profile)
Posted on: Wednesday, January 11, 2012 at 8:29 PM
Message: -- I don't think databases can enforce the common constraint that a master must have at least one detail.

I don't know that such is a "common" constraint. I've worked on a number of OLTP applications, and never had such a requirement. Yours?

Subject: deferred constraints rock
Posted by: AlexK (view profile)
Posted on: Thursday, January 12, 2012 at 10:00 AM
Message: @Verbose: regarding "I don't think databases can enforce the common constraint that a master must have at least one detail." - this is easy to accomplish with deferred constraints.

In SQL Server, we do not have deferred constraints, but we can mimic them.

@BuggyFunBunny: this is _very_ common. Usually Order without OrderItems just does not make sense.

Subject: On my planet it does
Posted by: BuggyFunBunny (view profile)
Posted on: Thursday, January 12, 2012 at 5:09 PM
Message: @BuggyFunBunny: this is _very_ common. Usually Order without OrderItems just does not make sense.

Of course it does. The Order is created and left alone while the items are added, deleted, changed, and at any time can be none. Happens all the time; at least in the wholesale and lower levels.

Subject: bonds, Masters, parents
Posted by: AlexK (view profile)
Posted on: Thursday, January 12, 2012 at 7:20 PM
Message: @BuggyFunBunny:

When we buy a bond, there is a schedule of one or more payments we expect to get.

Parents must have at least one child.

PhDs must have at least one degree.

From the business point of view these rules make sense.

In Oracle, we just enforce "parents must have children" rules in the database without thinking twice about it.

Just because it is not possible in SQL Server, it does not mean that we would not benefit from it if we could...

Subject: Hmm
Posted by: BuggyFunBunny (view profile)
Posted on: Friday, January 13, 2012 at 12:36 PM
Message: @AlexK

-- When we buy a bond, there is a schedule of one or more payments we expect to get.

This reveals the generic issue: should we force the user to enter data in the order and timeliness convenient to us, the developers, or to them? In general, users, of my acquaintance, would rather have the ability to create a "master record" then any "detail records" as the information develops. One could, and should, have a trigger to insert a default "child record"(s). With UDF support, templates derived from "master record" columns define the "child record" rows. Subsequent deletes would be blocked with before trigger.


-- Parents must have at least one child.

It is the case that children die before parents; I suppose the parent status then changes. Moreover, husband/wife/etc. will exist as childless/barren "master records", with a "field" to flag parent status. The insertion of "child records" (!) would change the "field" appropriately, on a trigger, natch. Again, a before trigger on "child" would prevent deletion; the child is eternal.


-- PhDs must have at least one degree

Have, as in earned? Many Ph.Ds are honorary and never got through high school (US term).

deferrable constraints are codified in SQL99 et seq. Oracle has the cleanest support, Postgres second. SQL Server, not so much.

Subject: Constraints
Posted by: Puckan001 (view profile)
Posted on: Saturday, January 14, 2012 at 4:16 AM
Message: n SQL Server, we do not have deferred constraints, but we can mimic them.

Subject: Anonymous
Posted by: Edafe_Onerhime (view profile)
Posted on: Monday, January 16, 2012 at 6:26 AM
Message: Thanks so much for your feedback, you've definitely given me some new ideas to pitch.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.