alter column from not null to null

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 column from not null to null

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-27 : 00:51:35

Dear folks,
please tell me the query for altering a column from not null to null

Vinod

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 00:54:17

first you have to make sure your data in the column is not NULL before enforcing the constraint.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-27 : 00:57:11

there is no data

Vinod

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 00:59:13

in your CREATE TABLE you'd have something like this:


CREATE TABLE dbo.SomeTable (
   col1 int ..,
   col2 varchar(20),
   col3 varchar(30) NOT NULL
)

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-27 : 01:01:22

ALREADY TABLE WAS CREATED AND DATA IS THERE ON SOME COLUMNS
SO I NEED ONLY ONE COLUMN TO ALTER

Vinod

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 01:09:25

THERE IS NO NEED TO POST IN CAPS AND YELL.

I are trying to help you out here. I dont work for you. Being polite and having common netiquette helps.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 01:10:29

In one post you say there is no data, in another you say data is there in some column? Which one is it? Or you dont know what you are doing?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-27 : 01:12:09

Sorry Dinakar, I'm not rude, the caps i didnt checked.I need the help of experts like you.
actually there was a table already created, but for my column there is nodata. i need this column to be changed from not null to null

Vinod

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-27 : 01:14:40

If the table already has data and this particular column does not, it means its NULL/empty. So you need to fill the column with some data before you enforce the constraint.


ALTER TABLE dbo.YourTable ALTER COLUMN yourColumn NOT NULL

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 01:15:11

If there is no DATA in YOUR column, do this:

1) DROP the column
2) ALTER the table and ADD same column again, this time with NULL preference

I am really scratching my head to understand wht you mean with NO DATA. If the column now is NOT NULL, the must be data in the column.
Or do you mean that empty spaces and/or zero value is NO DATA?

Peter Larsson
Helsingborg, Sweden

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-27 : 01:16:50

then how can i add the column at the same place? the added column will come in the last?
please guide me

Vinod

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 01:22:12

Yes, it will in the Enterprise Manager (EM) or SQL Server Management System (SSMS).
But... The order of column has ABSOLUTELY NO MEANING! It is NOT important!

Why? Because you define the order of columns when you SELECT the data anyway.

Peter Larsson
Helsingborg, Sweden

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-27 : 01:30:02

thank you friends,
i got the query

alter table table_name alter column column_name varchar(50) NULL

please tell me if my query is wrong

Vinod

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-27 : 01:31:37

Did you get an error when you tried it?

Peter Larsson
Helsingborg, Sweden

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-27 : 01:35:44

no the query run successfully.............
is there any wrong?

Vinod