Wednesday, June 15, 2016

Add DEFAULT constraint, or add new column with DEFAULT

On many occasions over the years, I've enabled a DATETIME attribute on a table, but allowed SQL to populate it for me, with a GETDATE() DEFAULT constraint.  This can be very helpful for monitoring application data flow, and for troubleshooting or debugging.  To know exactly when a record was written, is a very good thing, I can assure you.

You may already have a date field, but there is no constraint.  Just use this to add the constraint to your table:  

   ALTER TABLE [dbo].[YourTable]
   ADD CONSTRAINT [df_YourTable_DateField]
   DEFAULT (GETDATE()) FOR [DateField];

Or, this one can be used to both add the new field, and enable the constraint:

   ALTER TABLE [dbo].[YourTable]
   ADD InsertTime DATETIME NOT NULL
   CONSTRAINT [df_YourTable_InsertTime] DEFAULT {GETDATE());

I usually use 'InsertTime' or 'DateCreated' to name attributes like this, because I think it very intuitively suggests what the value is used for. 

No comments:

Post a Comment