Error 515

Severity

16

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 following example 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” with the name of the database in which your table resides and “titleauthor” with 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 2007 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 (zero) 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 the alter table command to modify the column. For example:

alter table table1 modify column1 varchar(5) null 

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 with the behavior you want to change.

Versions in which this error is raised

All versions