Troubleshooting and Error Messages Guide

Home

Troubleshooting and Error Messages Guide

Chapter 3 Error Message Writeups

Query Processor Errors

Error 515

Severity

Error message text

Attempt to insert NULL value into column '%.*s', table '%.*s'; column does not allow nulls. Update fails.%S_EED

Explanation

When you create a table, you can explicitly define whether each column should allow null values. If you do not specify NULL or NOT NULL for a column when you create the table, the default value will be NOT NULL. If you use sp_dboption to set allow nulls by default to TRUE for the database, any new table that is created will have columns with the default value NULL.

Error 515 occurs at run time when a column has a NOT NULL default and you try to insert a NULL value into that column (for example, if a user does not enter a value for that column). The error message includes:

The name of the affected column.
The name of the affected table.
The EED (extended error data array) includes the database name, owner, table name, and column name. This information is not displayed but you will see it in the message definition if you select Error 515 from sysmessages.

The following sample would cause a 515 error:

1> create table table1 (column1 varchar (5))
2> go

1> declare @c varchar(5)
2> insert into table1 values (@c)
3> go

Msg 515, Level 16, State 3:
Server 'SERVER_NAME', Line 2:
Attempt to insert NULL value into column 'column1', table 'test.dbo.table1';
column does not allow nulls. Update fails.

Action

To determine whether a column has NULL or NOT NULL defined, enter commands like the following, replacing "pubs2" by the database in which your table resides and "titleauthor" by the table in which your column resides:

1> use pubs2
2> go

1> sp_help titleauthor
2> go

Name          Owner   Type
------------------------------------------------------------
titleauthor   dbo     user table
Data_located_on_segment        When_created
------------------------------ -----------------------------
default                        Oct 27 1994 10:09AM
Column_name Type Length Prec Scale Nulls Default_name Rule_name Identity
------------------------------------------------------------------------
au_id       id       11 NULL NULL  0     NULL         NULL      0
title_id    tid       6 NULL NULL  0     NULL         NULL      0
au_ord      tinyint   1 NULL NULL  1     NULL         NULL      0
royaltyper  int       4 NULL NULL  1     NULL         NULL      0

The Nulls column indicates whether null values are allowed. A value of 0 for the column means nulls are not allowed and 1 means null values are allowed.

To change the default for a column from NOT NULL to NULL:

Use bcp to copy the data out of the existing table.
If you want the modified table to have the same name as the existing table, drop the old table.
Re-create the original table, specifying NULL for the column you want to change.
Use bcp to put back the data for the table.

If you want new tables in the database to have the default NULL, use the following commands for the database:

1> use master
2> go

1> sp_dboption database_name,
2> "allow nulls by default", true
3> go

1> use database_name
2> go
1> checkpoint
2> go

where database_name is the name of the database whose behavior you want to change.

Versions in which this error is raised

All versions


	Expand Search