Not Null Constraint

On this page

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.

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

• Constraints
• DROP CONSTRAINT
• Check constraint
• Default Value constraint
• Foreign Key constraint
• Primary Key constraint
• Unique constraint
• SHOW CONSTRAINTS

Was this helpful?