Modify a field to use a certain value instead of NULL

[Neozero]

I need to modify a field in one of my tables to always be 0 instead of NULL. Basically, when a record is created in our application, it populates a table called Credit. There is a field in the Credit table called PortType. It always populates this field with NULL and I want it to always put a 0 in that field. Can I do this by modifying the properties of the field or do I have to create a stored procedure or what? Right now, I have to run an update statment once a week to change all values from NULL to 0. Thanks in advance!


-J

 

[simian336]

modify the properties of the field set the default value to 0

Simi

 

[gmmastros]

There are various things you can do to "fix" this problem.

1. Since it is your application you could change it to put a 0 in the the column instead of NULL.

2. You could put a default on the column. To do this, open the table in SQL Server Management Studio, click on the column, and then in the column properties, set the default value to 0.

The previous 2 methods have problems with them. Specifically, if you try to find the places in code where you insert or update the column, you may miss a place or 2 and still have this lingering problem. You could somewhat alleviate the problem by looking for all instances where you insert a row in to the table and make sure you put 0 for the PortType and then you could change the column to not allow NULLS. If you missed any places in code, you will get an error and then you can go fix that spot in code.

The problem with the default is that you can still insert the value NULL in to the column. The default is only used if you don't specify the column during the insert.

The safest, most fool proof method would be to create a trigger for the table. You would want the trigger to fire for inserts and updates. Then, if the value is NULL, change it to 0.

Your trigger would look something like this:

Create Trigger Credit_PortTypeNoNULL On Credit
For Insert, Update
AS
Begin
  SET NOCOUNT ON

  If Exists(Select 1 From inserted Where PortType Is NULL)
    Update  Credit
    Set     Credit.PortType = 0
    From    Credit
            Inner Join inserted
              On Credit.CreditId = inserted.CreditId
    Where   Credit.PortType Is NULL

End

The trigger code above assumes that the primary key column for the Credit table is CreditID. If the primary key is different, you'll need to adjust the code accordingly.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

ALTER TABLE [TableName] ADD  CONSTRAINT [DF_TableName_FieldName]  DEFAULT ((0)) FOR [TableName];

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[Neozero]

When I do properties on the field, there is no default value listed. There is a property called default binding and default shema. I am using SQL 2008. Thanks.

 

[gmmastros]

Using a default is a fine idea, but please understand that it will still be possible to get NULLS in your data. If you use the trigger method like I suggest, you won't get NULLS.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Agreed, George. Default is just that a Default, I would also set the the field to Not Allow NULL. That would prevent the need for a trigger to be created.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[gmmastros]

ousoonerjoe,

If you changed the field to not allow nulls you could get errors in the application.

For Example:

Create 
Table  TestNoNulls
         (Id Int Identity(1,1), 
         EyeColor VarChar(20), 
         PortType int Default(0) NOT NULL)

This is an example of a table with a column that has a default value for the PortType column and does not allow NULLs.

Now, if you insert a row like this:

[tt][blue]Insert Into TestNoNulls(EyeColor) Values('Blue')[/blue][/tt]

You will get a row with PortType = 0. However, if you do this:

[tt][blue]
Insert Into TestNoNulls(EyeColor, PortType) Values('Green', NULL)
[/blue][/tt]

You will get an error:
[tt][red]
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'PortType', table 'LTD.dbo.TestNoNulls'; column does not allow nulls. INSERT fails.[/red]
The statement has been terminated.[/tt]

Normally this would be a good thing as you are building your application. As a developer, you would see the error and realize that the PortType column of the TestNoNulls table does not allow NULLS. So you simply change the application to insert a value instead of NULL and problem solved.

However, if there is already an application built, you run the risk of unexpected errors because you may not have changed all the necessary code.

Bottom line, without the trigger, you would want to completely test the application to make sure nothing fails before you put the application in to production. I agree that the default value in combination with the NOT ALLOW NULL constraint is the better solution if you've got the time to test the entire application.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Good point, George. From the view point of the application having unknown code to make the inserts, you don't know for sure if there is an explicit NULL insert/update taking place. The trigger would be an effective stop-gap measure to prevent the error as well as the NULL.

But from a ground up design, dates are the only fields we allow NULLs for in our system. The default values and not allow NULL takes care of everything..... and yeah... we do correct the statements that potentially insert/update a NULL to a table so the procedure is accurate.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------