NOT NULL constraint

The NOT NULL specifies a column may not contain 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 .

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.

• To add the NOT NULL constraint to an existing table column, use the statement.
• For more information about NULL, see .

Syntax

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

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 .
column\_constraints	Any other column-level you want to apply to this column.
column\_def	Definitions for any other columns in the table.
table\_constraints	Any table-level you want to apply.

Usage example

> CREATE TABLE IF NOT EXISTS customers (
    customer_id INT         PRIMARY KEY,
    cust_name   STRING(30)  NULL,
    cust_email  STRING(100) NOT NULL
  );

> INSERT INTO customers (customer_id, cust_name, cust_email) VALUES (1, 'Smith', NULL);

pq: null value in column "cust_email" violates not-null constraint

> INSERT INTO customers (customer_id, cust_name) VALUES (1, 'Smith');

pq: null value in column "cust_email" violates not-null constraint

See also