Two single-quotes and null - aren't they the same?

[t***@gmail.com]

Hi all,

This is what happened last week -

I tried to intentionally insert into a table with null values like
this
INSERT INTO Table1(ID, username) VALUES(xxx, '' )
while "username is the PK", ID is just a number with identity, and
both ID and username do not allow nulls.

Immediately I received an error message which is expected, but weird
things happened when I did this -

1. I inserted something dummy first in Table1
INSERT INTO Table1(ID, username) VALUES(xxx, yyy)

2. I tried to update Table1 with '' (two single quotes right next to
each other without spaces inside)
UPDATE Table1 set username='' where ID=xxx

3. retrieve this record by ID
SELECT * FROM Table1 WHERE ID=xxx

Something that doesn't make sense to me is that I can retrieve this
record and found this row with a blank PK. It doesn't show the NULL on
the username, but it does not store two single quote in the column
either. What's more surprising to me is that my SQL Server 2000
allowed me to do this update. I already set up "not allow nulls"
attributes for this table. Besides, the username column is the PK for
Table1. How can we update a record to null PK in the table?

The only one possible answer is the two nearby single-quote thing ''
means something in the system and therefore does not equal to the
NULL, but I am not quite sure since I've never found this when I used
Oracle (I'm more familiar with PL/SQL)

Anyone has ideas about why it's allowed is highly appreciated.

-Tanya

[Krishnakumar S]

Post by t***@gmail.com
The only one possible answer is the two nearby single-quote thing ''
means something in the system and therefore does not equal to the
NULL

You are right here. NULL is entirely a different thing compared to empty
character (two nearby single-quote thing in your version) or zero. NULL means
unknown, but '' means blank. You can store empty character even if the column
does not allow NULLs. You cannot even equate NULL values. For more
information refer SQL Serve Books Online
--
Krishnakumar S

What lies behind you and what lies before you is nothing compared to what
lies within you

Post by t***@gmail.com
Hi all,
This is what happened last week -
I tried to intentionally insert into a table with null values like
this
INSERT INTO Table1(ID, username) VALUES(xxx, '' )
while "username is the PK", ID is just a number with identity, and
both ID and username do not allow nulls.
Immediately I received an error message which is expected, but weird
things happened when I did this -
1. I inserted something dummy first in Table1
INSERT INTO Table1(ID, username) VALUES(xxx, yyy)
2. I tried to update Table1 with '' (two single quotes right next to
each other without spaces inside)
UPDATE Table1 set username='' where ID=xxx
3. retrieve this record by ID
SELECT * FROM Table1 WHERE ID=xxx
Something that doesn't make sense to me is that I can retrieve this
record and found this row with a blank PK. It doesn't show the NULL on
the username, but it does not store two single quote in the column
either. What's more surprising to me is that my SQL Server 2000
allowed me to do this update. I already set up "not allow nulls"
attributes for this table. Besides, the username column is the PK for
Table1. How can we update a record to null PK in the table?
The only one possible answer is the two nearby single-quote thing ''
means something in the system and therefore does not equal to the
NULL, but I am not quite sure since I've never found this when I used
Oracle (I'm more familiar with PL/SQL)
Anyone has ideas about why it's allowed is highly appreciated.
-Tanya

[Tom Cooper]

'' is an empty string, that is, a string with 0 length. That is a value and
is most definitely NOT NULL to SQL Server. So it can be inserted or updated
into a column which has been declared NOT NULL and is a perfectly valid
value for a column in a primary key. I don't know why your original insert
failed, we would nedd to see the actual insert statement and DDL and/or the
actual error you recieved to tell you that, but it would not have been that
you were setting username to '' and that conflicted with the NOT NULL
attribute on username.

Tom

Post by t***@gmail.com
Hi all,
This is what happened last week -
I tried to intentionally insert into a table with null values like
this
INSERT INTO Table1(ID, username) VALUES(xxx, '' )
while "username is the PK", ID is just a number with identity, and
both ID and username do not allow nulls.
Immediately I received an error message which is expected, but weird
things happened when I did this -
1. I inserted something dummy first in Table1
INSERT INTO Table1(ID, username) VALUES(xxx, yyy)
2. I tried to update Table1 with '' (two single quotes right next to
each other without spaces inside)
UPDATE Table1 set username='' where ID=xxx
3. retrieve this record by ID
SELECT * FROM Table1 WHERE ID=xxx
Something that doesn't make sense to me is that I can retrieve this
record and found this row with a blank PK. It doesn't show the NULL on
the username, but it does not store two single quote in the column
either. What's more surprising to me is that my SQL Server 2000
allowed me to do this update. I already set up "not allow nulls"
attributes for this table. Besides, the username column is the PK for
Table1. How can we update a record to null PK in the table?
The only one possible answer is the two nearby single-quote thing ''
means something in the system and therefore does not equal to the
NULL, but I am not quite sure since I've never found this when I used
Oracle (I'm more familiar with PL/SQL)
Anyone has ideas about why it's allowed is highly appreciated.
-Tanya