Re: Referential Integrity and Indexes

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

peregenem@xxxxxxxxxxxx wrote in news:1122456543.559916.44550
@g14g2000cwa.googlegroups.com:

>> SELECT AField IS NULL FROM Something WHERE AKey = 1
>>
>> returns true regardless of what you try to do.
>
> For the latter I get an error, "Incorrect syntax near the
> keyword 'IS'".
>
>

Okay... I have re-researched this and I was wrong anyway. Here is an
actual example:

1> update bases set fullname=null where baseid=9
2> select baseid, isnull(fullname,'empty') as result
3> from bases
4> where baseid=9
5> go
(1 row affected)
baseid result
----------- --------------------------------------------------
9 empty

(1 row affected)
1> update bases set fullname='' where baseid=9
2> select baseid, isnull(fullname,'empty') as result
3> from bases
4> where baseid=9
5> go
(1 row affected)
baseid result
----------- --------------------------------------------------
9

(1 row affected)

.... so SQL Server seems quite happy to store zero length strings
distinctly from nulls. This is what one would expect although whenever I
tried it before I thought it failed. It is also true that the software
gets in the way -- in Access you have to try very hard to put a zero
length string in a text box.

Still, going back to the OPs original request for DDL equivalent to
"Allow Zero Length Strings" then, as seen above, it's on by default. In
order to refuse zero length strings he'd have to add a check constraint
like


1> alter table bases
2> add constraint NoNullNames
3> check (len(fullname)>0)
4> go
1> insert bases(fullname) values('')
2> go
Msg 547, Level 16, State 1, Server RUBENS, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement
conflicted with
COLUMN CHECK constraint 'NoNullNames'. The conflict occurred in database
'NewTestSQL', table 'Bases', column 'FullName'.
The statement has been terminated.


I think that's got it sorted out.


Tim F



.

Relevant Pages

  • Re: trouble with DBA
    ... I recently asked the DBA of the SQLServer database that I use to add a ... "SQL Server does not support a Zero Length String. ... I find zero length strings and nulls confusing in SQL Server. ...
    (comp.databases.ms-access)
  • Re: Explanation???
    ... Hi Lyle, thanks for thew answer. ... You can have thousands of records with zero ... > zero length strings and probably rightly so. ... I was lloking for coincidences: what you say explains is why zero lenght ...
    (comp.databases.ms-access)
  • Re: Explanation???
    ... zero length strings and probably rightly so. ... Maybe a trivial relationship, ie, we don't know what ... You don't have any aunts. ...
    (comp.databases.ms-access)
  • Re: result of strncmp(a,b,0);
    ... Could a or b be NULL when the count is zero? ... the strncmp spec says that the arguments point to ... arguments are strings, it says they're arrays, and for the return value ...
    (comp.std.c)