How to insert null value to NOT NULL column using the DEFAULT Constraint

Question

These is my example table definition:

CREATE TABLE [MyTable] 
(
    [ColumnName] [bit] NOT NULL
)

ALTER TABLE [MyTable] ADD DEFAULT ((0)) FOR [ColumnName]

I want to be able to pass a Null value to my stored procedure's @ColumnValue, something like:

@ColumnValue = null;

INSERT INTO [MyTable] ([ColumnName])
VALUES (@ColumnValue)

But I'm getting this error:

"Cannot insert the value NULL into column... INSERT fails with"

Why the DEFAULT constraints not working?

The default value will be inserted if the requested insert doesn't specify a value for the field. You're specifying NULL for that field. If possible, modify your insert to exclude the field entirely, or modify the application doing the insert to be aware of and supply the default value. — J.D. Pace
– J.D. Pace, Commented Sep 24, 2017 at 6:01

Shahar Shokrani · Accepted Answer · 2017-09-24 07:50:38Z

3

Solved:

as @J.D. Pace said: The default value will be inserted only if the value is not specified on the Insert statement.

so as @dotNET suggested, i have specified the default value in the INSERT query statement using the ISNULL:

ISNULL - The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL:

@ColumnValue = null;

INSERT INTO [MyTable] (
    [ColumnName]
)
VALUES (
    ISNULL(@ColumnValue, 0)
)

answered Sep 24, 2017 at 7:50

Shahar Shokrani

8,93010 gold badges60 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dotNET · Accepted Answer · 2017-09-24 06:02:57Z

1

A table with only one column of bit type with a default value seems to be a bad design. This stuff can almost certainly be stored in a different and better way. On the other hand, if there are other columns in the table that you didn't include in the post, just skip this particular column in your INSERT query and it will work fine. Lastly, you can specify the default value in your INSERT query too.

answered Sep 24, 2017 at 6:02

dotNET

35.7k29 gold badges176 silver badges277 bronze badges

2 Comments

Shahar Shokrani Over a year ago

it is only example, I can't skip the column because it's value passed to my stored procedure's parameter

dotNET Over a year ago

Can't you use an IF/ELSE in your SP, using two different queries in case the passed parameter is NULL or non-NULL.

Collectives™ on Stack Overflow

How to insert null value to NOT NULL column using the DEFAULT Constraint

2 Answers 2

Solved:

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Solved:

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related