This is a fairly simple process, but I bet more than a few people don’t know how to do it. I had to double check some syntax the other day, and I thought this is a perfect SQLNewBlogger post, based on something I had to (re)learn.
If I have a table, there are existing columns and I want to add a constraint to one, I need to alter it. To do that, I’ll use the ALTER TABLE x ADD CONSTRAINT syntax.
One of the common use cases is to add a default date to a date column. For example, I have a Blogs table and want to ensure the CreatedDate column is always populated. I’d do that with
ALTER TABLE dbo.Blogs ADD CONSTRAINT df_SysUTCDate DEFAULT SYSDATETIME() FOR createdate;
In this case, I use ADD CONSTRAINT and then name the constraint. Using specific names is always good since this means I can be sure that I have matches between development, QA, and production.
I then use the DEFAULT keyword and follow this with a function name. I then use the FOR and the column name. This means I’ve added a default constraint to the CreateDate column and if no value is included in an insert statement, sysdatetime() used.
This is a basic idea, but one that few developers think about or include in their design. Learn to use defaults and add them when you start building or adding columns.