Why doesn't my DEFAULT constraint Work? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Why doesn't my DEFAULT constraint Work?

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2011-12-09 : 01:18:17

Can someone explain why these defaults don't work when i try to include null values. I've tried all types of variations on the default contraint. I'v e added it in an ALTER TABLE. I've used several variations on CONTRAINT NAME DEFAULT 1 FOR COLUMN_NAME.

THanks!

USE master
Create Database Test
Go
USE TEst
go

CREATE TABLE LineItems
(
LineItemID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Date] DATETIME NULL DEFAULT GETDATE(),
Quantity INT NULL DEFAULT 1,
LastChangedBy INT

)

insert into LineItems values (NULL, NULL, NULL)

select * from lineitems

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 01:28:23

if you're expplicitly passing NULL it wont put default values for putting default value

you need to either make the column as NOT NULL type and ignore it in insert or with current setting (NULL) you need to do below

insert into LineItems values (DEFAULT(), DEFAULT(), NULL)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JacobPressures
Posting Yak Master

112 Posts

Posted - 2011-12-09 : 11:53:04

Thanks very much that is helpful. I greatly appreciate that. I spent a long time yesterday trying to figure out why my code wasn't working. I thought it was wrong. I think i remember something about sending it explicit NULLs long time ago. but it has been a while. So now I know to just ignore it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-12-09 : 12:21:41

Depending on how you have your code structured, you can also specify a column list, ommitting those with default values. For exaple:

insert LineItems (LastChangedBy) values (NULL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources