Zero-length string in PK

[BK]

I have an Access 2002 adp file which is linked to SQL Server 2000. I
recently found that a primary key field was blank. This shocked me as I
didn't understand how SQL Server would allow a PK to be Null. Well what
I found out was that it was a zero-length string. From googling around
it seems like if you enter something in the text box and then erase it,
instead of putting in a null value it puts in a zero length string. I
verified this by using query analyzer. Instead of putting <null> in
that field it was just blank. Well basically this cannot happen. I do
not want to allow zero-length strings in this field. But I cannot find
such an option in the adp like I might be able to find in a regular mdb
database. How can I not allow these zero length strings? I would
really not like to use a hack like entering a bogus record with a zero
length string or even a validation rule on the form to not allow "" I
would prefer it to be controlled by SQL Server. Do I have to go about
it another way other than a nice little check box that says "Do Not
Allow Zero-Length Strings"?

TIA,
Bill

 

[SFAxess]

You can use a check constraint on the table such as:
Len(pkColumn)<>0
or use a Trigger(would give you error handling ability).

Just make sure your error handling is set up so the user
knows what is going on when they enter a z.l.s.
Also, it using an Identity column (Autonumber in Access
terms) as your PK, would avoid the possibility of this
altogether, though z.l.s. are not a good practice for any
column, key related or not.