Automate Default Constraints

The Problem

When I was working with one of my projects, I had a recurring problem. Each time I added new tables or columns to the Mart or Stage database, I had to check for default constraints for that table and then set defaults manually if they were not set earlier. Sometimes I used to forget to do this and as a result I usually got NULL values in the destination instead of proper default values.

The Solution

I have found a solution to my problem, which was to create one procedure that will take care of all these default constraint values in the destination database. What does my procedure do? Each time you create the database elements, you have to run my procedure and it will check for all the possibilities of default values in each column for every table. Once it finds any, it will drop the old constraints for that column and create a new one that will be appropriate for that column and as per the data type of that column.

The plus point for drop and recreating constraints is that it will always update your constraints value as per the new criteria. I used the following system objects for achieving this goal:

• sys.tables
• sys.syscolumns
• sys.Types
• sys.default_constraints

The procedure code is attached in the resource section below.

Code Description:

I have used a cursor DefaultCurSet for tracking each column in the given database. The cursor will fetch each column's information into the @TableName, @ColName, @DataType, @DefaultVlue, @ConstraintName variables. Then by using 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName I have dropped all constraints in that database. After all the constraints are successfully dropped, the procedure will show this message: 'All default constraints are successfully dropped'.

Similarly, for creating constraints, I used the same cursor DefaultCurSet as I have deallocated the cursor earlier. This time I have fetched each column's information into the @TableName, @ColName, @DataType, @DataTypeSize, @DefaultVlue, @ConstraintName variables.

The constraint name will be created like this: @ConstraintName = 'DF_' + @TableName + '_' + @ColName. Then with this code:

'ALTER TABLE ' + @TableName + 
 ' ADD CONSTRAINT ' + @ConstraintName

I create a new constraint. For example, in destination we have 'LastExtractedDate' and 'LastLoadDate' columns which must have getdate() as the default value. For this, we use this code:

IF @DataType = 'datetime' AND @ColName like '%Last%Date%'
 BEGIN
 SET @Query = 
 'ALTER TABLE ' + @TableName + 
 ' ADD CONSTRAINT ' + @ConstraintName + 
 ' DEFAULT getdate() FOR ' + @ColName
 EXECUTE sp_executesql @Query
 END

After the successful creation of all the constraints in all the columns, it will show the following message: 'All default constraints are successfully created'

The important thing here is that it will check for existence of any IDENTITY column in the table and exclude that column from the default constraint policy. I have achieved the same by adding the code below:

...........
sys.syscolumns SC 
 ON ST.[object_id] = SC.[id]
...........
WHERE SC.status <> 0x80
...........

Here 0x80 or 128 is used to identify whether that column has IDENTITY property set or not.

Conclusion

Now whenever you feel your database needs to be upgraded or modified, just run my procedure or include it in your package sequence; the procedure will take care of all the default constraints needed for your database.