Correct way to specify a default empty string for a column in HSQLDB?

Question

The ability and manner in which a table column may be specified with a default empty string appears to be RDBMS implementation dependent.

This answer (default a column with empty string) and this one (Avoid NULL columns using DEFAULT Empty String) indicate that a default empty string may be set with a CREATE TABLE column rule that uses an empty single-quoted literal with the DEFAULT constraint.

Here is a DDL excerpt that I have used in HSQLDB 2.2.9. I am executing SQL against the database with SQuirreL 3.4.0:

CREATE CACHED TABLE Clients ( 
    cli_id       INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    mRN          VARCHAR(24)   NOT NULL UNIQUE,
    lastName     VARCHAR(48)   NOT NULL,
    midName      VARCHAR(24),
    firstName    VARCHAR(24)   DEFAULT '' NOT NULL,
);

lastName may not be NULL

midName has no column rules

firstName is not nullable and an attempt is made to have it default to an empty string by specifying an empty string literal as suggested by the previous answers.

When I use SQuirreL to make this table editable and then insert a row into the table, the following values appear in the columns:

lastName:    (empty)
midName:     <null>
firstName:   ''

It seems that the semantics DEFAULT '' NOT NULL in HSQLDB cause the column to default to two single quotes and not to an empty string. When the column rule NOT NULL is used alone without a DEFAULT rule, it looks as though an empty string is reported by SQuirreL when a new row is inserted.

Can someone confirm that the correct way to have a VARCHAR column in HSQLDB default to a non-null, empty, zero-length string is to use these semantics:

CREATE CACHED TABLE Clients ( 
    cli_id       INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    mRN          VARCHAR(24)   NOT NULL UNIQUE,
    lastName     VARCHAR(48)   NOT NULL,
    midName      VARCHAR(24)   NOT NULL,
    firstName    VARCHAR(24)   NOT NULL,
);

Thanks :-)

That seems to be a squirrel bug then. Maybe Squirrel tries to be clever and retrieves the default expression and sends it to the database for columns that you do not edit. This works fine for me with my SQL client. Can you see what statement Squirrel sends to the database? If you don't edit it, the statement should not include the `firstName` column (or its value should be sent using the `DEFAULT` keyword) — , Apr 02 '13 at 06:47

score 3 · Accepted Answer · answered Apr 02 '13 at 06:10

The correct way is actually your original CREATE TABLE. If a column has no DEFAULT clause but has a NOT NULL constraint, then it has no default value and you must include a value for the column when you insert into the table.

It may be the SQuirreL client is using it's own convention for displaying or inserting the firstName column.

You can insert an incomplete row with an SQL statement, using the HSQLDB DatabaseManager:

INSERT INTO clients(mrn, lastname) VALUES 'mrn00', 'Aname'

And check the result with a SELECT:

SELECT * FROM clients

Which shows this when you switch to View->Results in Text

CLI_ID MRN   LASTNAME MIDNAME FIRSTNAME 
------ ----- -------- ------- --------- 
0      mrn00 Aname    (null)

Correct way to specify a default empty string for a column in HSQLDB?

1 Answers1