限定某个字段/某列的值不允许为空
image.png

关键字

NOT NULL

特点

  • 默认,所有的类型的值都可以是NULL,包括INT、FLOAT等数据类型
  • 非空约束只能出现在表对象的列上,只能某个列单独限定非空,不能组合非空
  • 一个表可以有很多列都分别限定了非空
  • 空字符串’’不等于NULL,0也不等于NULL

    建表时添加非空约束

    ```sql CREATE DATABASE dbtest13;

USE dbtest13;

not null (非空约束)

在CREATE TABLE时添加约束

CREATE TABLE test1( id INT NOT NULL, last_name VARCHAR(15) NOT NULL, email VARCHAR(25), salary DECIMAL(10,2) );

DESC test1;

INSERT INTO test1(id,last_name,email,salary) VALUES(1,’Tom’,’tom@126.com’,3400);

错误:Column ‘last_name’ cannot be null

INSERT INTO test1(id,last_name,email,salary) VALUES(2,NULL,’tom1@126.com’,3400);

错误:Column ‘id’ cannot be null

INSERT INTO test1(id,last_name,email,salary) VALUES(NULL,’Jerry’,’jerry@126.com’,3400);

Field ‘last_name’ doesn’t have a default value

INSERT INTO test1(id,email) VALUES(2,’abc@126.com’);

Column ‘last_name’ cannot be null

UPDATE test1 SET last_name = NULL WHERE id = 1;

  1. <a name="h4aEY"></a>
  2. # 在alter table时添加非空约束
  3. ```sql
  4. SELECT * FROM test1;
  5. DESC test1;
  6. # 若表中原本数据email中含有NULL值,则会报错!!!
  7. ALTER TABLE test1
  8. MODIFY email VARCHAR(25) NOT NULL;

在alter table时删除约束

  1. ALTER TABLE test1
  2. MODIFY email VARCHAR(25) NULL;