"NOT NULL" property in SQL Server Express 2019

Question

Good evening everyone, I have been exploring this new tool (for myself) and ran into an issue that I cannot explain.

I have been using SQL 2008 and never had that problem.

The case is that within SQL 2019 I set the "Not null" property in certain fields of some tables. The problem is that either from my development environment (Visual Studio) or from a stored procedure or from the table's record editor, it allows me to save a record even though I passed an empty string to those "not null" fields.

I know that I could validate that data in the stored procedure, but I know that the function of the "not null" is precisely to control that aspect, so it is important to understand why it is allowing me to register null values ​​in a not null field.

If anyone has any ideas or the same experience, I would greatly appreciate sharing your knowledge. Thank you in advance. Greetings.

Answer 1

Hi @Ale Vecca ,

but when creating this "check" property, does it apply to the whole table or only to the fields that have the "not null" set?

You can type the SQL expressions for the check constraint in Expression field.
For example:

This will apply to the fields that have the "not null" set.
You also can right-click in the upper half of the screen-> select Generate Change Script to see the script about add Constraints.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Probably you are inserting empty strings (‘’), which are not null. Try adding a constraint to your column. For example, if the table is created by script:

create table MyTable 
(
    . . .
    MyColumn varchar(100) not null constraint c1 check ( MyColumn <> '' )
)

This will also prevent values that contain spaces only.

You can enter this condition in Table Designer window too (in Management Studio): select the column, then menu, Table Designer, Check Constraints.

Answer 3

NULL and an empty string are by the formal definition two quite different things.

For instance, take a column MiddleName in Persons table. If MiddleName is the empty string, we know that this person does not have a middle name. If the value is NULL, we don't know the middle name of this person might be, or if the person has a middle name at all.

As I said, this is the formal meaning of NULL. In practice, NULL has a semantic meaning which is defined by the context. NULL in column called PaidDate in an Invoice table probably means that the invoice has not yet been paid. NULL in a CustRespID column in a Customers table probably means that there is no employee has been assigned to be responsible for this customer. And for a MiddleName column, many prefer to use NULL to include that the person has no middle name.

And more generally for strings, in practice there is rarely any need to distinguish between the case "we don't know" and the "we know that there is no value", and NULL and empty strings tend be used quite interchangeably.

However, in formal definitions for a relational database, there still is a big difference between NULL and empty string. So while you may prefer to consider them as the same, you still need to keep the differences in mind when writing your code and defining your tables. And a constraint like the one Viorel suggested can serve that purpose. Note that since trailing blanks are not significant for = and <>, this constraint will also trap a value that consists of spaces only.