Adding a not null column - SQL Server Forums

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

Adding a not null column

Author

Topic

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2009-01-12 : 00:43:22

Dear experts
when i run the following query in mysql it runs successfully

create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10), Stu_Class varchar(10))
ALter table Stu_Table add Stu_marks int(3) not null

But when i run the same query in sql server it shows following error msg:
"ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'Stu_marks' cannot be added to table 'Stu_Table' because it does not allow nulls and does not specify a DEFAULT definition."

is there any way to add not null column in a table without specifying default definition??

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 00:46:18

is the stu_table contains any data r not

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-12 : 00:47:08

delete the data in ur table and try
create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10), Stu_Class varchar(10))
ALter table Stu_Table add Stu_marks int not null

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-12 : 00:47:11

if u have any data in the table, then u can't add a column with not null value.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-12 : 00:53:10

quote:
Originally posted by kartik.kaveeshwar

Dear experts
when i run the following query in mysql it runs successfully

create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10), Stu_Class varchar(10))
ALter table Stu_Table add Stu_marks int(3) not null

But when i run the same query in sql server it shows following error msg:
"ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'Stu_marks' cannot be added to table 'Stu_Table' because it does not allow nulls and does not specify a DEFAULT definition."

is there any way to add not null column in a table without specifying default definition??

Hi,

change ALter table Stu_Table add Stu_marks int(3) not null

as ALter table Stu_Table add Stu_marks int not null

Don't mention (3) while defining an int value because
you Cannot specify a column width on data type int

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2009-01-12 : 01:06:08

the table doesn't contain any data

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-12 : 01:06:59

quote:
Originally posted by kartik.kaveeshwar

the table doesn't contain any data

Have u tried my suggestion ??

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2009-01-12 : 01:08:39

yes raky while running the query in sql server i hv ommited column width

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-12 : 01:18:20

I think ur table contains data. Once check it

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-12 : 01:19:00

If u dont have any data , then use this
ALter table Stu_Table add Stu_marks int not null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 02:14:51

the error message clearly specifies that you should specify a default value while adding a NOT NULL column to already exiting table.try this

ALter table Stu_Table add Stu_marks int not null default -1

kartik.kaveeshwar
Starting Member

18 Posts

Posted - 2009-01-12 : 03:37:14

Thanks to you all for your suggestions
I ran the following queries to achieve desired result

create table Table1(Col1 varchar(20),Col2 datetime )

alter table Table1 add col3 int not null constraint MyConstraint default -1

alter table table1 drop constraint myconstraint

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 03:41:13

quote:
Originally posted by kartik.kaveeshwar

Thanks to you all for your suggestions
I ran the following queries to achieve desired result

create table Table1(Col1 varchar(20),Col2 datetime )

alter table Table1 add col3 int not null constraint MyConstraint default -1

alter table table1 drop constraint myconstraint

if there is no data in ur table then no need of creating default constraint u can use thsi statement
alter table Table1 add col3 int not null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources