CREATING THE PHYSICAL MODEL WITH SQL SERVER - A Developer's Guide to Data Modeling for SQL Server - page 212

column in the event that a value is not explicitly specified. Unique con-
straints tell SQL Server that all the data in the column must be unique
and must contain no duplicates. Check constraints allow you to write a
small piece of T-SQL code to do custom data checking against one or more
columns. Let's look at each of these a little closer and see how they can be
useful in implementing business rules.
Default Constraints
Default constraints are very simple. You provide a value for a column, and
when a new row is added to a table without specifying a value for that col-
umn, the default value is used. This can be as simple as stating that the sta-
tus column in tbl_employee should always be 1 unless otherwise stated; or
it can be more complicated, such as a function call to generate a new order
number. The following code adds a default constraint to the status column
in tbl_employee.
ALTER TABLE dbo.tbl_employee
ADD CONSTRAINT DF_status
DEFAULT 1 FOR status
If you want to call a function to generate your default data, that is done
as shown next. This piece of code adds a default constraint to tbl_order to
call a user-defined function to create a new order ID.
ALTER TABLE dbo.tbl_order
ADD CONSTRAINT [DF_ordernumber]
DEFAULT dbo.udf_new_orderid() FOR ordernumber
Default constraints are useful as long as you document them for the
developers who will be writing the T-SQL code. Their functionality can be
replaced within stored procedure code, so many people ask why we should
bother with default constraints. The answer, which is the answer we give
for any question involving the use of data integrity, is that you can't always
rely on your stored procedures. What if you develop a SQL Server
Integration Services (SSIS) package that writes data in bulk to a table?
There will be no stored procedure there to protect you. Similarly, what if
a developer gets access to the tables and writes code to insert data directly?
This is a bad practice (we look at avoiding it in Chapter 11), but it can still