want to know correct syntax for default values

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)

want to know correct syntax for default values

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-10-29 : 02:38:03

Hi,

Anyone can point out me Whats wromg with this statement

ALTER TABLE [dbo].[TableName] ALTER COLUMN COL1 TINYINT NOT NULL DEFAULT 0

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

Thanks

Aravind

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-29 : 05:15:13

Do:

ALTER TABLE [dbo].[TableName] ALTER COLUMN COL1 TINYINT NOT NULL
ALTER TABLE [dbo].[TableName] add constraint D_test default 0 for
COL1

DEFAULT 0

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:48:13

I don't think you can do it that way jackv - because it is a NOT NULL column the DEFAULT must be expressed in the same ALTER TABLE statement as the Column itself, otherwise SQL has no value to put in the new column for all existing rows.


ALTER TABLE dbo.ADDRESS ADD
	MyNewColumn tinyint NOT NULL CONSTRAINT MyConstraintName DEFAULT 0

Kristen

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-10-29 : 06:09:12

Thanks a lot ... at last I got the remedy which jackv shown here ...
I got error while what Kristen scripted aboce... dont know the reason

any how Thank you so much for your response

Have a nice time !!

Bye

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 06:43:51

"I got error while what Kristen scripted aboce... dont know the reason"

If you posted the error message we could tell you ....

Probably you already have the column in your table, thus you cannot ADD one with the same name.

And probably you already have values in that column in all rows, so you are not getting any error, or it is already NOT NULL etc.

To change an existing column to NOT NULL and add a DEFAULT you either have to

Drop FKs
Make a temp table
Copy all data across
Drop the original table
Rename the temp table back to the original table's name
Recreate Indexes, PK, FKs

or:

Set up the DEFAULT
Make sure that there are no existing rows with a NULL value
Change the column to be NOT NULL


ALTER TABLE MyTableName ADD CONSTRAINT
	MyConstraintName DEFAULT 0 FOR COL1

UPDATE MyTableName 
SET COL1 = 0
WHERE COL1 IS NULL

ALTER TABLE MyTableName ALTER COLUMN
	COL1 tinyint NOT NULL

Kristen

aravindt77
Posting Yak Master

120 Posts

Posted - 2007-10-29 : 08:23:57

Thanks Kristen.... Thank you for your kind information

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-30 : 05:46:00

It is possible to do it the way , i've outlined assuming there are no NULLS , which wasn't clear even if any data existed in the table

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources