CODEWITHSUNDEEP
sql-serverdefault-value
markzzz
markzzz

Reputation: 47945

How can I set a custom-default value in SQL Server?

Im trying to set a default value for a field into a SQL Server Table. This field is int, and when I go to the GUI and I set Associated Default Value to 0 and I save the table, than the default value will be ((0)) (and when I insert a record it get the NULL value).

Why? How can I fix it?

Upvotes: 0

Views: 1528

Answers (3)

Diego
Diego

Reputation: 36126

If it inserts NULL is because you are probably specifying the column name on the query.

If you do:

insert into table (defaultColumn) values (NULL)

it will ignore the default constraint and insert NULL

There are several scenarios. Try posting your query so we can analyze it

Upvotes: 0

gbn
gbn

Reputation: 432200

You are sending NULL.

If you want it to use the default, then don't specify it or use the keyword DEFAULT

INSERT (col1, col3) -- col2 is skipped, gets zero default
VALUE (foo, bar)

INSERT (col1, col2, col3)
VALUE (foo, DEFAULT, bar)

INSERT -- not best practice
VALUE (foo, DEFAULT, bar)

Upvotes: 1

DavidGouge
DavidGouge

Reputation: 4623

I just created a table as follows and the default value on testid2 works as expected.

USE [db]
GO
/****** Object:  Table [dbo].[Table_1]    Script Date: 01/31/2012 10:27:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[testid] [int] NULL,
[testid2] [int] NOT NULL CONSTRAINT [DF_Table_1_testid2]  DEFAULT ((0))
) ON [PRIMARY]

If you right click on your table, Script Table as..., CREATE TO and then check your result against mine.

Upvotes: 0

Related Questions