On this page
Warning:
CockroachDB v22.1 is no longer supported as of November 24, 2023. For more details, refer to the Release Support Policy.
The NOT NULL
constraint specifies a column may not contain NULL
values.
Details
INSERT
orUPDATE
statements containingNULL
values are rejected. This includesINSERT
statements that do not include values for any columns that do not have aDEFAULT
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.
To add the
NOT NULL
constraint to an existing table column, use theALTER COLUMN
statement.For more information about
NULL
, see NULL handling.
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 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
> 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