Skip to content

NOT NULL Non-empty constraints

NOT NULL constraints can be used to restrict a column from not containing NULL values.

Syntax Description

> column_name data_type NOT NULL;

You cannot insert a NULL value into a column containing the NOT NULL constraint, or update the old value to NULL.

Example

create table t1(a int not null,b int);
mysql> insert into t1 values(null,1);
ERROR 3819 (HY000): constraint violation: Column 'a' cannot be null
mysql> insert into t1 values(1,null);
Query OK, 1 row affected (0.01 sec)
mysql> update t1 set a=null where a=1;
ERROR 3819 (HY000): constraint violation: Column 'a' cannot be null

Example Explanation: In the above example, because column a has a non-empty constraint, the 1st insert statement will fail, the 2nd statement satisfies the non-empty constraint of column a, and column b has no non-empty constraint, so it can be inserted successfully. The update statement fails because it triggers the non-null constraint of column a.