Alter Table to add a new column

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 to add a new column

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2010-12-29 : 13:29:38

Hi,

I need to add a column to a sql server 2005 Express Database Table. The column should not have NULL values. I am using something like this.
How can i create this column with a NOT NULL. Should i use a constraint?

Error: Incorrect syntax near 'NULL'.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_Lkup' AND COLUMN_NAME = 'IsDeleted')
BEGIN
Alter Table my_Lkup ADD IsDeleted Char(1) NOT NULL

This also failed
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'my_Lkup' AND COLUMN_NAME = 'IsDeleted')
BEGIN
Alter Table my_Lkup ADD IsDeleted Char(1) Constraint C_IsDel NOT NULL

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-29 : 13:32:57

You are missing an END.

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

Subscribe to my blog