16
The column %.*s in table %.*s does not allow null values.%S_EED
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. |
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:
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.
All versions
