Default a Column with Empty String

default a column with empty string

Yes - use a DEFAULT constraint:

DROP TABLE IF EXISTS `example`.`test`;
CREATE TABLE  `example`.`test` (
  `string_test` varchar(45) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

set default value to field if empty string is inserted through query

MySQL/MariaDB will put the value to a column you specify in the insert/update-statement. If you do not provide a value, the default (if specified) will be used.

If you want to use the default value even if the insert/update-statement does provide a value (NULL / empty string), you will have to have the logic somewhere. The options are that you put the logic in your application code (PHP) or if you want to do it in MySQL/MariaDB, you can use a trigger to check the new value and act accordingly.

CREATE TRIGGER ins_A BEFORE INSERT ON A
FOR EACH ROW
BEGIN
  IF NEW.Z is null or NEW.Z='' THEN
    SET NEW.Z = 'diverse';
  END IF;
END;

And do the same for UPDATE

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

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)            

alter column table and make default value to empty string

You need to add a contraint. Referring to http://blog.sqlauthority.com/2008/05/31/sql-server-create-default-constraint-over-table-column/

ALTER TABLE Employee 
        ADD CONSTRAINT DF_Employee _JobTitle
        DEFAULT '' FOR sJobTitle 

Avoid NULL columns using DEFAULT Empty String

You can read up on the subject here

CREATE TABLE dbo.Test (ID INTEGER, EmptyString VARCHAR(32) DEFAULT '')

INSERT INTO dbo.Test (ID) VALUES (1)
INSERT INTO dbo.Test (ID) VALUES (2)

SELECT * FROM dbo.Test

DROP TABLE dbo.Test

How to set default value to empty string for TEXT column?

You can specify a default value for the column when you create the table. (It doesn't appear as though you can add a default using an ALTER statement, so you'll have to recreate your table.)

CREATE TABLE your_table_name
(MainContactName TEXT NOT NULL DEFAULT '')

New rows that are inserted without a value specified for MainContactName will have an empty string for the MainContactName field. You could try to explicitly insert nulls into that field, but the queries would blow up due to the NOT NULL constraint.

---

Related Topics