ALTER TABLE problem - 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

General SQL Server Forums

New to SQL Server Programming

ALTER TABLE problem

Author

Topic

DarkConsultant
Starting Member

17 Posts

Posted - 2009-06-11 : 08:19:43

Hi All,

After reading everything I could Google about ALTER TABLE command I am still unable to get it to work.

I have a table that has a column with this spec -

[Prefix] [int] NULL

and I want to alter it to

[Prefix] [int] NOT NULL CONSTRAINT [DF_Contacts_Prefix] DEFAULT ((0))

I have tried lots of scripts like -

USE [DatabaseName]
ALTER TABLE TableName
MODIFY [Prefix] NOT NULL CONSTRAINT [DF_Contacts_Prefix] DEFAULT ((0));

and get the result 'Incorrect syntax near 'Prefix'.'

I am still a bit new to SQL and often do not know the correct words to search for. Would someone kindly steer me in the right direction please.

Thanks

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-11 : 08:44:16

try this

UPDATE TableName
set Prefix = 0
where Prefix is null

go

ALTER TABLE TableName
ADD CONSTRAINT DF_Contacts_Prefix
DEFAULT 0 FOR Prefix

go

ALTER TABLE TABLENAME
ALTER COLUMN PREFIX INT NOT NULL

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 08:44:19

alter table tablename alter column prefix int not null CONSTRAINT [DF_Contacts_Prefix] DEFAULT ((0))

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-11 : 08:56:48

quote:
Originally posted by bklr

alter table tablename alter column prefix int not null CONSTRAINT [DF_Contacts_Prefix] DEFAULT ((0))

Getting error as

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONSTRAINT'.

DarkConsultant
Starting Member

17 Posts

Posted - 2009-06-11 : 09:14:09

raky,

RESULT!

This works perfectly, thank you so much.

None of the answers found on Google looked anything like yours, and bless me I understood why it worked.

Thanks again

raky
Aged Yak Warrior

767 Posts

Posted - 2009-06-11 : 09:30:02

Welcome...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources