16
Attempt to insert NULL value into column '%.*s', table '%.*s'; column does not allow nulls. Update fails.%S_EED
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 extended error data array (EED), which includes the database name, owner, table name, and column name. This information does not display, but you will see it in the message definition if you select error 515 from sysmessages.
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.
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.
All versions
