On this page 
Warning:
CockroachDB v20.2 is no longer supported as of May 10, 2022. For more details, refer to the Release Support Policy.
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
DEFAULTvalue constraint only applies if the column does not have a value specified in theINSERTstatement. You can still insert a NULL into an optional (nullable) column by explicitly inserting NULL. For example,INSERT INTO foo VALUES (1, NULL);.
Syntax
You can only apply the DEFAULT value constraint to individual columns.
Note:
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](data-types.html).
`default_value` | The value you want to insert by default, which must evaluate to the same [data type](data-types.html) as the `column_type`.
`column_constraints` | Any other column-level [constraints](constraints.html) you want to apply to this column.
`column_def` | Definitions for any other columns in the table.
`table_constraints` | Any table-level [constraints](constraints.html) 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
- Constraints
ALTER COLUMNCHECKconstraintREFERENCESconstraint (Foreign Key)NOT NULLconstraintPRIMARY KEYconstraintUNIQUEconstraintSHOW CONSTRAINTS
Was this helpful?
