Default Value Constraint - CockroachDB

The DEFAULT value constraint specifies a value to write into the constrained column if one is not defined in an INSERT statement. The value may be either a hard-coded literal or an expression that is evaluated at the time the row is created.

Details

The data type of the DEFAULT value must be the same as the data type of the column.
The DEFAULT value constraint only applies if the column does not have a value specified in the INSERT statement. You can still insert a NULL into an optional (nullable) column by explicitly inserting NULL. For example, INSERT INTO foo VALUES (1, NULL);.

Syntax

default_value_column_level syntax diagram

You can only apply the DEFAULT value constraint to individual columns.

You can also add the DEFAULT value constraint to an existing table through ALTER COLUMN.

Parameter	Description
`table_name`	The name of the table you’re creating.
`column_name`	The name of the constrained column.
`column_type`	The constrained column’s data type.
`default_value`	The value you want to insert by default, which must evaluate to the same data type as the `column_type`.
`column_constraints`	Any other column-level constraints you want to apply to this column.
`column_def`	Definitions for any other columns in the table.
`table_constraints`	Any table-level constraints you want to apply.

Example

> CREATE TABLE inventories (
    product_id        INT,
    warehouse_id      INT,
    quantity_on_hand  INT DEFAULT 100,
    PRIMARY KEY (product_id, warehouse_id)
  );

> INSERT INTO inventories (product_id, warehouse_id) VALUES (1,20);

> INSERT INTO inventories (product_id, warehouse_id, quantity_on_hand) VALUES (2,30, NULL);

> SELECT * FROM inventories;

+------------+--------------+------------------+
| product_id | warehouse_id | quantity_on_hand |
+------------+--------------+------------------+
|          1 |           20 |              100 |
|          2 |           30 | NULL             |
+------------+--------------+------------------+

If the DEFAULT value constraint is not specified and an explicit value is not given, a value of NULL is assigned to the column.

See also