DEFAULT Values

col_default:
        DEFAULT col_default_value
col_default_value:
        constant | NULL | AUTO
    |   CURRENT
    |   {CURDATE | CURRENT_DATE}
    |   {CURTIME | CURRENT_TIME}
    |   {CURTS | CURRENT_TIMESTAMP}}]

The DEFAULT attribute provides a default value for a column. The default value will be added to all new records if no other value is specified.

Validation of the default value is performed when the schema is compiled to make sure the default is a valid entry for the column. For example, the value -1 is invalid for a UINT8 column since the column is unsigned and cannot store a negative value.

 

DEFAULT NULL

Set the associated column to NULL if no other value is specified. This is the default condition for the DEFAULT constraint. This constraint is not allowed on a column that is declared to be NOT NULL.

DEFAULT AUTO

The AUTO default is currently implemented only for the UUID or GUID data types. If no other value is specified, the default value will be a generated UUID.

Example Schema

create table t1
(
   id uuid default auto,
   name char(10)
);

Example Usage

auto.sql(1): insert into t1 (name) values ('item 1');
*** 1 row(s) inserted
auto.sql(2): insert into t1 (name) values ('item 2');
*** 1 row(s) inserted
auto.sql(3): insert into t1 (name) values ('item 3');
*** 1 row(s) inserted
auto.sql(4): select * from t1;

ID                                      | NAME
----------------------------------------+-----------
d56171a4-16c4-4204-8cdc-39f21d8199fc    | item 1
466606dc-b5dc-4c7d-a1bc-2bc0eda7967c    | item 2
6e1d7293-d2ba-4e7d-8870-0a3c5dacf273    | item 3
*** 3 row(s) returned

In the above example, the 'id' column is declared with the 'default auto' attribute. The insert of a new row and only setting the value for 'name' automatically generated unique identifiers for the 'id' column.

DEFAULT CURRENT

This sets the associated DATE, TIME, or TIMESTAMP column to the current date, time or timestamp.

DEFAULT CURDATE or CURRENT_DATE

This sets the associated DATE column to the current date. DEFAULT CURRENT can also be used.

DEFAULT CURTIME or CURRENT_TIME

This sets the associated TIME column to the current time.DEFAULT CURRENT can also be used.

DEFAULT CURTS or CURRENT_TIMESTAMP

This sets the associated TIMESTAMP column to the current timestamp.DEFAULT CURRENT can also be used.

DEFAULT constant

The default value can also be defined as a constant value as long as it is compatible with the associated data type. The following example demonstrates some default settings. For more information on the appropriate constant for the associated data type, refer to the Constants section.

Example Schema

create table t1
(
   type char(5),
   col1 char(10) default 'Dummy',
   col2 integer default -26,
   col3 real default 3.14159,
   col4 uint8 default 10,
   col6 decimal(8,2) default 3.14,
   col7 binary(10) default 0x0001020304,
   col8 date default '1956-05-03',
   col9 boolean default true,
   col10 clob default 'something important...'
);

Example Usage

rdm-sql: insert into t1 (type) values ('A');
*** 1 row(s) inserted
rdm-sql: insert into t1 (type) values ('B');
*** 1 row(s) inserted
rdm-sql: insert into t1 (type) values ('C');
*** 1 row(s) inserted
rdm-sql: select * from t1;

TYPE | COL1      |        COL2|           COL3|   COL4|       COL6| COL7                  | COL8      |   COL9| COL10
-----+-----------+------------+---------------+-------+-----------+-----------------------+-----------+-------+------------------------
A    | Dummy     |         -26|        3.14159|     10|       3.14| 0x00010203040000000000| 1956-05-03|      1| something important...
B    | Dummy     |         -26|        3.14159|     10|       3.14| 0x00010203040000000000| 1956-05-03|      1| something important...
C    | Dummy     |         -26|        3.14159|     10|       3.14| 0x00010203040000000000| 1956-05-03|      1| something important...
*** 3 row(s) returned

RDM Core API (C/C++) Limitations With Default

The default value handling by these methods currently have the following limitations for rows inserted using the RDM Core API.

  • The default values other than NULL will be ignored for BLOB and CLOB columns.
  • The default values of CURRENT for DATE, TIME, or TIMESTAMP columns do insert the current date/timestamp. The default value will be set to the current date/timestamp when the schema is compiled.
  • The DEFAULT AUTO for the GUID/UUID depends on TIMESTAMP and generates an incomplete UUID. We recommend not depending on the DEFAULT AUTO for this column type on RDM Core API.