Cannot insert the value NULL

Question

Cannot insert the value NULL

jan michael woo cerna-336610

Right there with Babe

Points: 724
More actions
May 30, 2008 at 12:03 pm

#70499

Hi:
My application runs in SQL Server 2000 and it has a script that has a similar process below:
/*1st stage*/
alter table tbltst add b numeric(16, 2) not null default 0 /
/*2nd stage*/
alter table tbltst drop constraint DF__tbltst__b__61D2EC77
alter table tbltst alter column b numeric(18,4) not null
alter table tbltst add constraint DF_tbltst_b default 0 for b
and its gives me an error below when it ran the 2nd stage:
Error #: [1526]: Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'b', table 'vergo.dbo.tbltst'; column does not allow nulls. UPDATE fails.
Do you have any idea?
Jan

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Have you check if there were nulls in column B? I know you had a constraint on it, but if it was disabled or if the constraint was added AFTER the nulls were inserted - nulls could exist/pre-exist the constraint.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

You could always add the constraint with NOCHECK and then enable the constraint. That will prevent any FUTURE nulls from getting in.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Look at the code, guys... column was previously defined as NOT NULL.

Dunno if it'll work because no one provided the code for the rest of the table, but why are you dropping the NOT NULL constraint to begin with? Just alter the column... you're not changing the default so leave that out, as well...

/*1st stage*/

alter table tbltst add b numeric(16, 2) not null default 0

go

/*2nd stage*/

alter table tbltst alter column b numeric(18,4) not null

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jeff Moden (5/30/2008)
Look at the code, guys... column was previously defined as NOT NULL.
Dunno if it'll work because no one provided the code for the rest of the table, but why are you dropping the NOT NULL constraint to begin with? Just alter the column... you're not changing the default so leave that out, as well...
/*1st stage*/
alter table tbltst add b numeric(16, 2) not null default 0
go
/*2nd stage*/
alter table tbltst alter column b numeric(18,4) not null

You're right - I'm thinking check constraints. Doesn't look like NULL/NOT NULL gets to be ignored unless you define it as a check constraint itself.... So - what are we missing? Is there something happening between step 1 and 2 that's not being mentioned?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

I don't believe anything is missing... just alter the column like I did... don't mess with the constraint or the default.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Jeff Moden (5/30/2008)
I don't believe anything is missing... just alter the column like I did... don't mess with the constraint or the default.

I understand - I guess I've been focusing on why it's failing. The whole thing seems to be undoing and redoing the same contraints - so it doesn't really make any sense. But still - the original code seems to work for me - so I was curious why it's blowing up.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jan michael woo cerna-336610 Right there with Babe Points: 724 More actions · Answer 7

Thank you very much guys for your effort on this... I just get back over the weekend. The problem that I mention above occurs one of our users and I try to duplciate at my end but I can't. The user is running MSSQL 2000 SP4. Do you have any idea if these could trigger the error?