Inserting default value instead of null

Question

Hi,

I have a table where i have the set the default value to 'a'

[Made in..] VARCHAR(255)  Constraint abc DEFAULT 'a'

tried also using null and not null also for the above column definition

I am expecting to insert 'a' when ever there is a null but instead its throwing an error

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Made in..', table 'FEATURES_BABY'; column does not allow nulls. INSERT fails.

Answer 1

I suspect the reason for the error is that the [Made in..] column is specified in the insert column list and and explict NULL value is specified.  If you want the default constraint value to be used, omit the column from the INSERT statement entirely or specify the keyword DEFAULT instead of NULL.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 2

are you defining the column as NOT NULL, and explicitly specifying NULL for that column in the insert statement?

The below code works fine for me. It might help you out.

CREATE TABLE test1234
(
    [Made in..] VARCHAR(255)  Constraint abc DEFAULT 'a',
    test int
);

INSERT INTO test1234 (test, [Made in..]) VALUES (1, NULL); -- inserts NULL
INSERT INTO test1234 (test) VALUES (2); -- inserts 'a'

Regards,
Alvaro

Answer 3

Put this in your statement

Isnull(the_var,'a')

---

Many Thanks & Best Regards, Hua Min

Answer 4

Adding a default constraint to any column just indicates that if NO value is specified for that column while INSERT, the default value will be used instead for that newly added row.

So, to get rid of the above error, you have got 2 options - 

1. Stop using the column name [Made in..] in your INSERT statement (your ideal choice)
2. Make the column [Made in..] NULLABLE.

Hope, this helps!

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia