NULL default constraint on nullable column [duplicate]

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!

DougBug · Accepted Answer · 2018-11-09 19:42:41Z

2

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.

answered Nov 9, 2018 at 19:42

DougBug

462 bronze badges

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

Comments

Amarnadh · Accepted Answer · 2018-11-09 20:13:26Z

0

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.

answered Nov 9, 2018 at 20:13

Amarnadh

114 bronze badges

Collectives™ on Stack Overflow

NULL default constraint on nullable column [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related