Alter column, add default constraint

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)

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][1]


[1]:

[To see links please register here]

-

you can wrap reserved words in square brackets to avoid these kinds of errors:

dbo.TableName.[Date]

Actually you have to Do Like below Example, which will help to Solve the Issue...

drop table ABC_table

create table ABC_table
(
names varchar(20),
age int
)

ALTER TABLE ABC_table
ADD CONSTRAINT MyConstraintName
DEFAULT 'This is not NULL' FOR names

insert into ABC(age) values(10)

select * from ABC

I use the stored procedure below to update the defaults on a column.

It automatically removes any prior defaults on the column, before adding the new default.

Examples of usage:

-- Update default to be a date.
exec [dbo].[AlterDefaultForColumn] '[dbo].[TableName]','Column','getdate()';
-- Update default to be a number.
exec [dbo].[AlterDefaultForColumn] '[dbo].[TableName]','Column,'6';
-- Update default to be a string. Note extra quotes, as this is not a function.
exec [dbo].[AlterDefaultForColumn] '[dbo].[TableName]','Column','''MyString''';

Stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Sample function calls:
--exec [dbo].[AlterDefaultForColumn] '[dbo].[TableName]','ColumnName','getdate()';
--exec [dbol].[AlterDefaultForColumn] '[dbo].[TableName]','Column,'6';
--exec [dbo].[AlterDefaultForColumn] '[dbo].[TableName]','Column','''MyString''';
create PROCEDURE [dbo].[ColumnDefaultUpdate]
(
-- Table name, including schema, e.g. '[dbo].[TableName]'
@TABLE_NAME VARCHAR(100),
-- Column name, e.g. 'ColumnName'.
@COLUMN_NAME VARCHAR(100),
-- New default, e.g. '''MyDefault''' or 'getdate()'
-- Note that if you want to set it to a string constant, the contents
-- must be surrounded by extra quotes, e.g. '''MyConstant''' not 'MyConstant'
@NEW_DEFAULT VARCHAR(100)
)
AS
BEGIN
-- Trim angle brackets so things work even if they are included.
set @COLUMN_NAME = REPLACE(@COLUMN_NAME, '[', '')
set @COLUMN_NAME = REPLACE(@COLUMN_NAME, ']', '')

print 'Table name: ' + @TABLE_NAME;
print 'Column name: ' + @COLUMN_NAME;
DECLARE @ObjectName NVARCHAR(100)
SELECT @ObjectName = OBJECT_NAME([default_object_id]) FROM SYS.COLUMNS
WHERE [object_id] = OBJECT_ID(@TABLE_NAME) AND [name] = @COLUMN_NAME;

IF @ObjectName <> ''
begin
print 'Removed default: ' + @ObjectName;
--print('ALTER TABLE ' + @TABLE_NAME + ' DROP CONSTRAINT ' + @ObjectName)
EXEC('ALTER TABLE ' + @TABLE_NAME + ' DROP CONSTRAINT ' + @ObjectName)
end

EXEC('ALTER TABLE ' + @TABLE_NAME + ' ADD DEFAULT (' + @NEW_DEFAULT + ') FOR ' + @COLUMN_NAME)
--print('ALTER TABLE ' + @TABLE_NAME + ' ADD DEFAULT (' + @NEW_DEFAULT + ') FOR ' + @COLUMN_NAME)
print 'Added default of: ' + @NEW_DEFAULT;
END

**Errors this stored procedure eliminates**

If you attempt to add a default to a column when one already exists, you will get the following error (something you will never see if using this stored proc):

-- Using the stored procedure eliminates this error:
Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

You're specifying the table name twice. The ALTER TABLE part names the table.
Try:
Alter table TableName
alter column [Date] default getutcdate()

alter table TableName
drop constraint DF_TableName_WhenEntered

alter table TableName
add constraint DF_TableName_WhenEntered
default getutcdate() for WhenEntered

I confirm like the comment from JohnH, never use column types in the your object names!
It's confusing. And use brackets if possible.

Try this:

ALTER TABLE [TableName]
ADD DEFAULT (getutcdate()) FOR [Date];