"Do Not Allow Null" fields suddenly accept Nulls

A problem that has just reared up in the past week... all fields that are set
to "Not Null" are now allowing nulls. We can delete a record from the field
and the database saves it just fine. Normally it would prompt an error
message saying the field does not accept null data.
This just started happening and it is happening across all of the databases
on the server. I've checked several references on what may be causing this
but have come up with nothing.
Strange...and not very usefull for data integrity. Can someone shed some
light?
Hi Scarfie
What does "delete a record from the field" mean. Delete only applies to
whole rows, and whole rows can always be deleted no matter what Nullability
setting you have.
Do you mean you're changing the value of the field to something else?
How are you making that change? I suggest you NOT use Enterprise Manager for
this, as it is not intended to be a data management tool. You may be
changing the value to blank, which is not the same as a null.
Try in Query Analyzer:
update my_table -- whatever the name of your table is
set my_column = NULL -- use one of the columns you think doesn't allow
nulls
where <supply a meaningful condition for this table>
Let us know what happens. If you get an error, show us. If not, show us the
DDL for the table:
exec sp_help my_table
Also, what version are you using?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
>A problem that has just reared up in the past week... all fields that are
>set
> to "Not Null" are now allowing nulls. We can delete a record from the
> field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
> databases
> on the server. I've checked several references on what may be causing
> this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?
|||What do you mean by "delete a record from the field"? Did you mean "delete
the value from the column"? Can you explain the exact process you are
following to delete, as well as what tool you are using to do this? Is this
something the average user will be able to do easily?
If the data type is CHAR/VARCHAR, and you are deleting the string it
contains using the DEL key, please keep in mind that an empty string is NOT
NULL, it is an empty string. There is an important distinction there that
many people miss.
http://www.aspfaq.com/
(Reverse address to reply.)
"Scarfie" <Scarfie@.discussions.microsoft.com> wrote in message
news:31F93F06-BEEC-456C-8568-B07E8A257133@.microsoft.com...
> A problem that has just reared up in the past week... all fields that are
set
> to "Not Null" are now allowing nulls. We can delete a record from the
field
> and the database saves it just fine. Normally it would prompt an error
> message saying the field does not accept null data.
> This just started happening and it is happening across all of the
databases
> on the server. I've checked several references on what may be causing
this
> but have come up with nothing.
> Strange...and not very usefull for data integrity. Can someone shed some
> light?