NULL default constraint on nullable column

Question

I've inherited a database that's been in existence since SQL Server 6.5. It's now sitting on a SQL Server 2014 box, in SQL Server 2014 compatibility mode. As part of my initial analysis of the database, I found several tables with nullable columns that have default constraint value of NULL. An example table script is like so:

CREATE TABLE [dbo].[tester](
[tester_id int identity(1,1) not null primary key clustered,
[profile_type_key] [int] NOT NULL,
[reel_key] [int] NULL,
[product_key] [int] NULL,
[cd_percent_cov] [decimal](15, 4) NULL constraint DF_cd_percent_cov default (NULL),
[md_percent_cov] [decimal](15, 4) NULL constraint DF_md_percent_cov default (NULL))

SQL obviously allows this, but I'm having trouble wrapping my head around why these defaults exist. Can anyone share a possible rationale for such column definitions?

Thanks!

Answer 1

There is no need to do it, but explicitly adding it can help express to people that inherit this code later (i.e. you) that they expressly meant for there to be NULLs if no value supplied. In the same vein it is likely just a standard that they were using (possibly for the reasons aforementioned) when they created this script.

Answer 2

As you mentioned , default (NULL) constraint will determine what is the user defined value so in your case NULL is default for your field [cd_percent_cov] and [md_percent_cov].

So you can't do give any data once you fix constraint as DEFAULT(NULL) otherwise error will be as below

Msg 515, Level 16, State 2, Line 11 Cannot insert the value NULL into column 'profile_type_key', table 'tempdb.dbo.#teste______________________________________________________________________________________________________________000000004F49'; column does not allow nulls. INSERT fails. The statement has been terminated.