Setting DEFAULT to empty string using SQL

[Tomas Eklund]

Using: Access 2003, ASP and JET SQL

Hello!

I trying to create a table using SQL (which will be executed from an
ASP-page) which needs to have some VARCHAR columns that defaults to empty
strings. But I have serious issues with the syntax of the DEFAULT
statement as it doesn't allow for strings to be enclosed in quotes.
Example:

CREATE TABLE Users
(
Name VARCHAR(20) DEFAULT Unknown
)

From the MSDN article Intermediate Microsoft Jet SQL for Access 2000:
"Notice that the default value is not enclosed in single quotes. If it
were, the quotes would also be inserted into the record."

If I cannot use quotes, then how do I specify an empty string?

Best regards
Tomas Eklund

 

[Toco]

Have you tried using NULL? Otherwise just create the table with 'UNKNOWN
'contained in all of the records for the last column, then update the table
with either NULL or an empty string.

 

[Tomas Eklund]

Have you tried using NULL? Otherwise just create the table with

'UNKNOWN'contained in all of the records for the last column, then
update the table with either NULL or an empty string.

Yes, I decided I didn't want to use NULLs in this case (actually I'm
declaring the fields with NOT NULL). And having a default value that is
something other than an empty string will not make any sense in this case.
Then it's better to not have any default value at all, and make sure to
always specify a value (valid or empty) when inserting rows.

I just thought it would be utterly stupid if it wasn't possible to have
empty strings as default values (I know it's possible to create such
tables using the Access user interface). But then again, it is SQL and SQL
must be the most stupid and inconsistent language ever created.

Best regards
Tomas Eklund