question

AleVecca-4285 avatar image
0 Votes"
AleVecca-4285 asked AleVecca-4285 commented

"NOT NULL" property in SQL Server Express 2019

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.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered AleVecca-4285 commented

Hi @AleVecca-4285,

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:
95074-01.jpg

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.




01.jpg (37.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Muchas gracias por tu respuesta.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

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.


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Good afternoon, thanks for replying. I've been looking for that property within the table designer and couldn't find it. I am attaching a screenshot of my table, could you please tell me which property you mention?
94979-sin-titulo.jpg


0 Votes 0 ·
sin-titulo.jpg (252.1 KiB)

Right-click in the upper half of the screen and select from the context menu.

But I would advice you to stay away from the Table Designer and use SQL to define tables. Not the least if you need to change existing tables, since this is an area where the Table Designer is rotten to the core.

0 Votes 0 ·

Sorry for the ignorance, but when creating this "check" property, does it apply to the whole table or only to the fields that have the "not null" set?
Thank you in advance.

95013-sin-titulo.jpg


0 Votes 0 ·
sin-titulo.jpg (237.5 KiB)
Show more comments
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered DanGuzman commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

I'll add that Oracle does not follow this standard ANSI SQL behavior and considers empty strings and NULL the same. This is bad and confuses developers that jump between database products.



0 Votes 0 ·