Not Null Constraint

On this page Carat arrow pointing down
Warning:
As of April 30, 2020, CockroachDB v2.1 is no longer supported. For more details, refer to the Release Support Policy.

The NOT NULL constraint specifies a column may not contain NULL values.

Details

  • INSERT or UPDATE statements containing NULL values are rejected. This includes INSERT statements that do not include values for any columns that do not have a DEFAULT value constraint.

For example, if the table foo has columns a and b (and b does not have a DEFAULT VALUE), when you run the following command:

  > INSERT INTO foo (a) VALUES (1);

CockroachDB tries to write a NULL value into column b. If that column has the NOT NULL constraint, the INSERT statement is rejected.

  • You can only define the NOT NULL constraint when creating a table; you cannot add it to an existing table. However, you can migrate data from your current table to a new table with the constraint you want to use.

    Note:
    In the future we plan to support adding the NOT NULL constraint to existing tables.

  • For more information about NULL, see Null Handling.

Syntax

You can only apply the NOT NULL constraint to individual columns.

CREATE TABLE table_name ( column_name column_type NOT NULL 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.
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.

Usage example

icon/buttons/copy
> CREATE TABLE IF NOT EXISTS customers (
    customer_id INT         PRIMARY KEY,
    cust_name   STRING(30)  NULL,
    cust_email  STRING(100) NOT NULL
  );
icon/buttons/copy
> INSERT INTO customers (customer_id, cust_name, cust_email) VALUES (1, 'Smith', NULL);
pq: null value in column "cust_email" violates not-null constraint
icon/buttons/copy
> INSERT INTO customers (customer_id, cust_name) VALUES (1, 'Smith');
pq: null value in column "cust_email" violates not-null constraint

See also


Yes No
On this page

Yes No