Msg 102, Level 15, State 1, Line 9

Question

Post reply

Msg 102, Level 15, State 1, Line 9

findleybryan

SSC Veteran

Points: 214
More actions
May 7, 2010 at 7:29 pm

#222243

Below is the simple code I'm trying to add. Don't know why I'm getting the error mentioned in the subject line.
Use master
Use FindleyKFFv4
--Query 1--
ALTER TABLE Job_title
Add MinSalary money not null
Add MaxSalary money not null

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

findleybryan SSC Veteran Points: 214 More actions · Answer 1

Here is the entire error message that I get.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near 'MaxSalary'.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi ,

Make like this

ALTER TABLE Job_title

Add MinSalary money not null

Go

ALTER TABLE Job_title

Add MaxSalary money not null

Thanks & Regards,
MC

findleybryan SSC Veteran Points: 214 More actions · Answer 3

Now I get this error

Msg 4901, Level 16, State 1, Line 4

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'MinSalary' cannot be added to non-empty table 'Job_title' because it does not satisfy these conditions.

Msg 4901, Level 16, State 1, Line 2

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'MaxSalary' cannot be added to non-empty table 'Job_title' because it does not satisfy these conditions.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 4

Sorry,

My assumption was the table was empty(no data). It looks like it has some data. In such cases while adding new columns by default it will take NULL values in the new columns for all the existing rows. So when you want to make it NOT NULL you have to specify some DEFAULT value for the new columns, so it will be like

ALTER TABLE Job_title

Add MinSalary money not null default <value you wan to give as default>

Go

ALTER TABLE Job_title

Add MaxSalary money not null default <value you wan to give as default>

eg:

ALTER TABLE Job_title

Add MinSalary money not null default 100

Go

ALTER TABLE Job_title

Add MaxSalary money not null default 1000

Thanks & Regards,
MC