ALTERing column defaults - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ALTERing column defaults

Author

Topic

jeffnc
Starting Member

14 Posts

Posted - 2008-10-23 : 16:40:50

Not sure how to do this. SQL Server 2005. I want to be able to either drop DEFAULT from a column definition, or change the default.

CREATE TABLE DateTable
aTime DATETIME NOT NULL DEFAULT ('12:00:00 AM')

I can't find any syntax involving changing or dropping the DEFAULT that it will accept.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-23 : 16:47:59

You can use the ALTER TABLE statement to ADD/DROP constraints.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DeveloperIQ
Yak Posting Veteran

71 Posts

Posted - 2008-10-23 : 16:50:27

Not sure if I understand your question clearly. This what BOL has to say:

"DEFAULT
Specifies the value provided for the column when a value is not explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp, or those with the IDENTITY property. If a default value is specified for a user-defined type column, the type should support an implicit conversion from constant_expression to the user-defined type. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string; a scalar function, either a system, user-defined, or CLR function); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT."

jeffnc
Starting Member

14 Posts

Posted - 2008-10-23 : 16:58:40

I don't understand. I want to be able to either drop the default and redefine it with ALTER statements, or simply change the default value with an ALTER statement.

I want to be able to say

ALTER TABLE DateTable ALTER COLUMN aTime DROP DEFAULT

or

ALTER TABLE DateTable ALTER COLUMN aTime DATETIME NOT NULL DEFAULT ('11:00:00 AM')

or something like that. I don't see how a CONSTRAINT name can be of use here.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 17:04:14

CREATE TABLE DateTable (
aTime DATETIME NOT NULL DEFAULT ('12:00:00 AM')
)

alter table DateTable
drop constraint DF__DateTable__aTime__4830B400

Where the name of the constraint is to find in object explorer...

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources