Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need syntax

Status
Not open for further replies.
i mean after the table & column already exist
 
You can also alter an existing table.

Code:
Alter Table [!]TableName[/!] Alter Column [!]ColumnName[/!] [!]DataType[/!] Not NULL

Ex:

Alter Table People Alter Column EyeColor VarChar(10) Not Null

This alter command will fail if there are records in the database that have a null value for the column you are trying to change. You'll need to update the table so there are no nulls before changing it.

-George

"the screen with the little boxes in the window." - Moron
 
argghhhhhhhhh

ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]

There is NO mention of needing to put the data type in here
 
alter table <TableName> alter column <ColumnName> <DataType> not null


example
Code:
create table bla666(id int,col2 int)
go

--change column to be not null
alter table bla666 alter column id int not null


--try inserting a null value
insert into bla666 values(null,1)


--error
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'id', table 'Blog.dbo.bla666'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions
 
I didn't write the documentation. [wink]

-George

"the screen with the little boxes in the window." - Moron
 
Thanks for you help

Anyways I can't make it not null, because duh, the column includes nulls

 
ROTFLMAO!!!

Sorry, katbear. I couldn't help myself. That sounds EXACTLY like the Friday I had last week!

Weekend's almost here, though. Hang in there!

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill
 


There is NO mention of needing to put the data type in here
That's not true. It's right in the snippet you posted. Immediately after "ALTER COLUMN column_name" is a curly brace. Curly braces mean, "all the elements within are required unless in square brackets."

new_data_type is not in square brackets, so it has to be part of the statement when you wish to use NOT NULL.

It is a little unfortunate that they called it new_data_type instead of data_type but it is still a required element as it is denoted.

ALTER TABLE table
{ [ ALTER COLUMN column_name
{ new_data_type [ ( precision [ , scale ] ) ]
[ COLLATE < collation_name > ]
[ NULL | NOT NULL ]
| {ADD | DROP } ROWGUIDCOL }
]
| ADD
{ [ < column_definition > ]
| column_name AS computed_column_expression
} [ ,...n ]
| [ WITH CHECK | WITH NOCHECK ] ADD
{ < table_constraint > } [ ,...n ]
| DROP
{ [ CONSTRAINT ] constraint_name
| COLUMN column } [ ,...n ]
| { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
}

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top