ALTER TABLE

Question

ALTER TABLE

kapil_kk

SSC-Insane

Points: 21316
More actions
September 16, 2013 at 1:16 am

#283208

HI all,
here is the scenario that I faced:
1. I had a table which some data.
2. I need to add a column with not null, While I was doing in this manner.
Alter table table1
ADD column1 int not null
I got an error that Alter table statement not allowed for not null
3. then, I truncate the table and execute same statement as above and it executed successfully
4. after this, I was again inserting the data in the table in this manner
insert into table1
......
Select column names from table_name
While doing this, I again got the error that Insertion failed as table1 column column1 dont allow NOT NULL values.
When I checked in the table I found that there is no null values in statement
Select column names from table_name
5. then I drop and recreate the table again and this script run fine and data gets inserted.
But I dont understand the behaviour why does it happen?
Can anyone please explain me?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 8 posts - 1 through 7 (of 7 total)

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

BrainDonor SSCoach Points: 19256 More actions · Answer 1

kapil_kk (9/16/2013)
HI all,
here is the scenario that I faced:
1. I had a table which some data.
2. I need to add a column with not null, While I was doing in this manner.
Alter table table1
ADD column1 int not null
I got an error that Alter table statement not allowed for not null

So, you added a column to a table that already had rows stored within it? You told SQL that you didn't want any NULL values in this column. If SQL isn't told what value to store in a column it will generally use NULL, and you told it that NULL is not allowed, so it reported an error.

In this situation (where there is existing data in the table) you could first create the column allowing NULL, then update the column with a default value. Then ALTER the column to NOT NULL.

3. then, I truncate the table and execute same statement as above and it executed successfully

Because when you truncated the table you removed any existing rows, so it didn't have to try and update existing data.

4. after this, I was again inserting the data in the table in this manner
insert into table1
......
Select column names from table_name
While doing this, I again got the error that Insertion failed as table1 column column1 dont allow NOT NULL values.
When I checked in the table I found that there is no null values in statement
Select column names from table_name

Without seeing the actual code (or a working example of it) I'm assuming that your SELECT statement was being used to load data into every column EXCEPT the new column you've added. Again, you told SQL that you can't have NULL in that column but you haven't told it what it should store in there - so it complained because it can't use the usual NULL.

5. then I drop and recreate the table again and this script run fine and data gets inserted.

We would have to see a working demonstration of this. Without seeing an example I would say that either the column does allow NULL, it has a default value, data is being loaded into that column or the new column doesn't exist on this new table.

But I dont understand the behaviour why does it happen?
Can anyone please explain me?

Show us a working example of the table and the quieries that you use, so we don't have to guess.

Steve Hall
Linkedin
Blog Site

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Without seeing the actual code (or a working example of it) I'm assuming that your SELECT statement was being used to load data into every column EXCEPT the new column you've added. Again, you told SQL that you can't have NULL in that column but you haven't told it what it should store in there - so it complained because it can't use the usual NULL.

This time I used new column also in the SELECT statement but it gives an error that NULL values not allowed in that column instead I was not getting any NULL value in that column from that SELECT statment

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

If you were getting an error saying that nulls are not allowed, then there were nulls in the select for that column.

Post the exact code, we can't tell what's wrong from descriptions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

kapil_kk (9/16/2013)
This time I used new column also in the SELECT statement but it gives an error that NULL values not allowed in that column instead I was not getting any NULL value in that column from that SELECT statment

THere is a safe and best practice to avoid this kind of issues.

Always mention column's names in INSERT INTO(Column names.....) SELECT column names.... FROM TABLE.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

Bhuvnesh (9/16/2013)
kapil_kk (9/16/2013)
This time I used new column also in the SELECT statement but it gives an error that NULL values not allowed in that column instead I was not getting any NULL value in that column from that SELECT statment
THere is a safe and best practice to avoid this kind of issues.
Always mention column's names in INSERT INTO(Column names.....) SELECT column names.... FROM TABLE.

I always use this practice only mentioning columns names in INSERT INTO

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

GilaMonster (9/16/2013)
If you were getting an error saying that nulls are not allowed, then there were nulls in the select for that column.
Post the exact code, we can't tell what's wrong from descriptions.

I checked, there were no null in the select for that column..

I will post exact code and result ...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

kapil_kk (9/16/2013)
GilaMonster (9/16/2013)
If you were getting an error saying that nulls are not allowed, then there were nulls in the select for that column.
Post the exact code, we can't tell what's wrong from descriptions.
I checked, there were no null in the select for that column..

Sure you're looking at the right column? That error won't be thrown in error. If it says there are nulls, there are, somewhere.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass