DEFAULT constraints allow you to specify a value that the database will use to populate fields that are left blank in the input source. They’re a replacement for the use of NULL values
that provide a great way to predefine common data elements.
Should you use DEFAULT constraints?
For example, image that you have a database table containing information about your customers, including a field providing the standard discount rate that each customer receives off the list price of your products. This table might be used to calculate prices dynamically in your e-commerce store. Account managers create a new row in the table each time they create a customer account. Sometimes they don’t specify a discount rate. You have several methods to address this problem:
- Allow NULL values in the discount field. This approach leaves you with a situation that the e-commerce application will need to handle when it encounters a NULL discount. As discussed in All About NULL values, the NULL value is different from a numeric value of 0. It reflects the fact that we are missing information. How should the application interpret a NULL discount? You’ll need to be sure that your developers know the business rules to implement in the application for this situation
- Require input for this field. If you do not allow NULL values, you force the account manager to enter a value when creating the new customer account. This solves the database problem, but might cause issues for account managers who are not sure what discount they should apply to a new customer.
- Create a default value. If you provide most of your customers with the same discount from list price, you could create a DEFAULT constraint containing that value. For example, if most customers receive a 10% discount, you could create that default value and then only modify the value for customers who receive a different discount. This is the easiest solution from a data entry perspective.
Creating a DEFAULT Constraint
To create a DEFAULT Constraint in Microsoft SQL Server 2008, follow these steps:
- Open SQL Server Management Studio
- Navigate to the Tables folder of the appropriate database
- Right-click on the table where you would like to create the constraint and choose Design
- Click on the column where you would like to create the DEFAULT constraint
- Enter the appropriate value in the Default Value or Binding cell of the properties page
That’s all there is to creating DEFAULT constraints in Microsoft SQL Server 2008. If you’re looking for more advice, stop by our forum