Error 233

Severity

16

Message text

The column %.*s in table %.*s does not allow null values.%S_EED

Explanation

During query processing, Adaptive Server performs an action called normalization, during which it resolves column and table names and generates a query tree. During normalization, when Adaptive Server prepares the target list for processing an insert or update, it checks to make sure nulls are allowed. Error 233 will be raised at compile time if, from within a stored procedure, you try to insert null values in a column which does not allow nulls.

Error 233 is raised with the following states:

State

Meaning

1

Nulls are not allowed, the column is in the target list, and the value being inserted is null.

2

No default exists, nulls are not allowed, and the column is not in the target list.

3

For inserts to temporary tables, Adaptive Server checks to be sure all “not null” columns are listed explicitly. If a “not null” column is not listed in the insert statement, the error is raised with state 3.

Action

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.

To determine whether a column has NULL or NOT NULL defined, enter commands like the following, replacing pubs2 with 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 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 zero (0) for the column means nulls are not allowed; a value of 1 means null values are allowed.

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

  1. Use bcp to copy the data out of the existing table.

  2. If you want the modified table to have the same name as the existing table, drop the old table.

  3. Re-create the original table, specifying NULL for the column you want to change.

  4. 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