Alter column, add default constraint

sql-server sql-server-2008 tsql alter-table alter-column
ram picture ram · Jan 19, 2010 · Viewed 350.6k times · Source

I have a table and one of the columns is "Date" of type datetime. We decided to add a default constraint to that column

Alter table TableName
alter column dbo.TableName.Date default getutcdate()

but this gives me error:

Incorrect syntax near '.'

Does anyone see anything obviously wrong here, which I am missing (other than having a better name for the column)

Answer

SQLMenace picture SQLMenace · Jan 19, 2010

Try this

alter table TableName 
 add constraint df_ConstraintNAme 
 default getutcdate() for [Date]

example

create table bla (id int)

alter table bla add constraint dt_bla default 1 for id



insert bla default values

select * from bla

also make sure you name the default constraint..it will be a pain in the neck to drop it later because it will have one of those crazy system generated names...see also How To Name Default Constraints And How To Drop Default Constraint Without A Name In SQL Server

Related questions

Add a column if it doesn't exist to all tables?

I'm using SQL Server 2005/2008. I need to add a column to a table if it does not yet exist. This will apply to all tables in a given database. I hoped I was close, but I'm having issues with this …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More