need syntax

[katbear]

help

What is the syntax for making a table column NOT NULL??

thanks

 

[SQLDenis]

NOT NULL

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

example


create table bla(id int NOT NULL,col2 int)


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[katbear]

i mean after the table & column already exist

 

[gmmastros]

You can also alter an existing table.

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

 

[katbear]

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

 

[SQLDenis]

alter table <TableName> alter column <ColumnName> <DataType> not null


example

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

 

[gmmastros]

I didn't write the documentation.

-George

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

 

[katbear]

Thanks for you help

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

 

[LNBruno]

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

 

[katbear]

TGIF

 

[ESquared]

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]