On this page 
The Not Null constraint specifies a column may not contain NULL values.
Details
INSERTorUPDATEstatements containing NULL values are rejected. This includesINSERTstatements 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.
| 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
See Also
- Constraints
DROP CONSTRAINT- Check constraint
- Default Value constraint
- Foreign Key constraint
- Primary Key constraint
- Unique constraint
SHOW CONSTRAINTS
Was this helpful?
