The Default Value constraints specifies a value to populate a column with if none is provided. It supplies a value to a column if one is not provided on insert. The value may be a hard-coded literal or an expression that is evaluated at the time the row is inserted.

Details

  • The data type of the Default Value or expression should be the same as the data type of the column.
  • The Default Value constraint only applies on insert if the column is not specified in the INSERT statement. You can still insert a NULL into an optional (nullable) column by explicitly stating the column and the NULL value.

Syntax

CREATE TABLE table_name ( column_name column_type DEFAULT default_value column_constraints , column_def table_constraints ) )
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.
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)
  );
You cannot apply the Default constraint to multiple columns (i.e. at the table level).

Usage 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 no DEFAULT constraint is specified and an explicit value is not given, a value of NULL is assigned to the column. This may cause an error if the column has a NOT NULL constraint.

See Also



Yes No