minor hippcup on the alter table statement

[johnny90]

Hi all

I was try to do the following things but i am getting a syntax error in the sql analyster of the sql server 2000

Case:

Alter the “rm” table.
Change the default value for the “dp_abbr” field to (‘Undefined’).
************************************
Code is:
ALTER TABLE rm ALTER COLUMN dp_abbr Default ['Undefined']
****************************
Error
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Default'.

*********

Need some help

 

[bborissov]

ALTER TABLE dbo.Rm ADD CONSTRAINT
	DF_RM_Dp_Abbr DEFAULT 'Undefined' FOR dp_abbr

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[johnny90]

Thanks for that code but i forgot to tell you that there is already a default value of (null) which need to be changed to ('undefined').

Therefore the code you gave didn't work. It came up with this error

Server: Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

 

[bborissov]

You first need to drop first constraint, then add new:

ALTER TABLE dbo.Rm DROP CONSTRAINT the name here
GO
ALTER TABLE dbo.Rm ADD CONSTRAINT
    DF_RM_Dp_Abbr DEFAULT 'Undefined' FOR dp_abbr

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.