Tom Cooper
3/30/2007 10:46:00 PM
'' 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
<tanya.wang@gmail.com> wrote in message
news:1175293268.525525.60050@d57g2000hsg.googlegroups.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
>