Sql 简明教程

SQL - NOT NULL Constraint

在表中，列通常默认情况下可以接受 NULL 值。但是，如果您想确保特定列不包含 NULL 值，则需要在该列上添加 NOT NULL 约束/条件。

In a table, columns can typically accept NULL values by default. However, if you want to ensure that a particular column does not contain NULL values, you need to add the NOT NULL constraint/condition on that column.

The SQL NOT NULL Constraint

SQL 中的 NOT NULL 约束用于确保表中的列不包含 NULL（空）值，并且防止尝试使用 NULL 值插入或更新行。

The NOT NULL constraint in SQL is used to ensure that a column in a table doesn’t contain NULL (empty) values, and prevent any attempts to insert or update rows with NULL values.

通常，如果我们在向表中插入数据时未向特定列提供值，则默认情况下它会被视为 NULL 值。但是，如果我们在列上添加 NOT NULL 约束，它将强制在数据插入期间必须为该列提供一个值，并且尝试插入一个 NULL 值会导致约束冲突错误。

Usually, if we don’t provide value to a particular column while inserting data into a table, by default it is considered as a NULL value. But, if we add the NOT NULL constraint on a column, it will enforce that a value must be provided for that column during the data insertion, and attempting to insert a NULL value will result in a constraint violation error.

Syntax

以下是创建表时 NOT NULL 约束的基本语法：

Following is the basic syntax of NOT NULL constraint while creating a table −

CREATE TABLE table_name (
   column1 datatype NOT NULL,
   column2 datatype,
   column3 datatype NOT NULL,
   ...
);

Creating NOT NULL Constraint On a Table

要在表的列上添加 NOT NULL 约束，我们只需在列定义中在列的数据类型后添加关键字“NOT NULL”即可。

To add the NOT NULL constraint on a column of a table, we just need to add the keyword "NOT NULL" after the column’s data type in the column definition.

Example

首先，让我们使用以下查询创建一个名为 CUSTOMERS 的表：

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT             NOT NULL,
   NAME VARCHAR (20)  NOT NULL,
   AGE  INT           NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (20, 2),
   PRIMARY KEY (ID)
);

让我们使用以下 INSERT 查询向上面创建的表中插入一些值：

Let’s insert some values into the above created table using the following INSERT query −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '25', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'Hyderabad', 9000),
(7, 'Muffy', '24', 'Indore', 5500);

表将按如下所示创建 -

The table will be created as shown below −

ID

NAME

AGE

ADDRESS

SALARY

1

Ramesh

32

Ahmedabad

2000.00

2

Khilan

25

Delhi

1500.00

3

Kaushik

23

Kota

2000.00

4

Chaitali

25

Mumbai

6500.00

5

Hardik

27

Bhopal

8500.00

6

Komal

22

Hyderabad

4500.00

7

Muffy

24

Indore

10000.00

Verification

要显示 MySQL 数据库中表的结构，我们使用 DESCRIBE 命令。DESCRIBE 命令提供了表的列、数据类型和各种属性的摘要，如下所示：

To display the structure of a table in MySQL database, we use the DESCRIBE command. The DESCRIBE command provides a summary of the columns, data types, and various attributes of the table as shown below −

DESCRIBE CUSTOMERS;

正如我们在以下输出中看到的，该表显示了有关表中列名、类型和它们是否允许空值的信息。

As we can see in the output below, the table shows information about the column names of the table, their types, and whether they are nullable or not.

Field

Type

Null

Key

Default

Extra

ID

int

NO

PRI

NULL

NAME

varchar(20)

NO

NULL

AGE

int

NO

NULL

ADDRESS

char(25)

YES

NULL

SALARY

decimal(20,2)

YES

NULL

Removing a NOT NULL Constraint From the Table

在 SQL 中，要移除现有表中列的 NOT NULL 约束，我们需要使用 ALTER TABLE 语句。使用此语句，我们可以修改列的定义，即更改现有列的名称、数据类型或约束。

In SQL, to remove a NOT NULL constraint of a column in an existing table, we need to use the ALTER TABLE statement. Using this statement, we can modify the definition of a column i,e you can change the name, data type or constraint of an existing column.

移除列上的 NOT NULL 约束的一种方法是将其更改为 NULL。

One of a way to remove the NOT NULL constraint on a column is to changing it to NULL.

Syntax

以下是在 MySQL 数据库中移除表中非空约束的语法：

Following is the syntax to remove a not null constraint from the table in MySQL database −

ALTER TABLE table_name
MODIFY COLUMN column_name datatype NULL;

这里，

Were,

  1. table_name is the name of the table that contains the columns we want to modify.

  2. column_name is the name of the column that has the NOT NULL constraint you want to remove.

  3. datatype is the data type of the column.

Example

以下是在 MySQL 数据库中将 CUSTOMERS 表的 NAME 列上的约束修改为 NULL 的查询：

Following is the query to modify the constraint on the NAME column of the CUSTOMERS table to NULL in MySQL database −

ALTER TABLE CUSTOMERS MODIFY COLUMN NAME VARCHAR(20) NULL;

Output

执行以上查询后，输出显示如下 −

On executing the above query, the output is displayed as follows −

Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

现在，让我们使用以下查询显示名为“CUSTOMERS”的表的结构：

Now, let us display the structure of the table named "CUSTOMERS" using the following query −

DESCRIBE CUSTOMERS;

正如我们在下表中看到的，“NAME”列已修改为可空，这意味着此列允许使用 NULL 值。

As we can see in the table below, the column "NAME" is modified to nullable, which means NULL values are allowed in this column.

Field

Type

Null

Key

Default

Extra

ID

int

NO

PRI

NULL

NAME

varchar(20)

YES

NULL

AGE

int

NO

NULL

ADDRESS

char(25)

YES

NULL

SALARY

decimal(20,2)

YES

NULL

Adding a NOT NULL Constraint to the Existing Table

在上一部分中，我们通过使用 ALTER TABLE 语句更改列的定义来移除 NOT NULL 约束。同样，我们可以使用 ALTER TABLE 语句将 NOT NULL 约束添加到现有的表中的列。

In the previous section, we have removed the NOT NULL constraint on a column by changing its definition using the ALTER TABLE statement. Similarly, we can add a NOT NULL constraint to a column in an existing table using the ALTER TABLE statement.

Syntax

以下是向 MySQL 数据库中的现有列添加 NOT NULL 约束的 SQL 语法：

Following is the SQL syntax to add the NOT NULL constraint to the existing column in MySQL database −

ALTER TABLE table_name
MODIFY COLUMN column_name datatype NOT NULL;

Example

假设先前创建的表 CUSTOMERS ，让我们使用以下查询修改 ADDRESS 列，以确保它不允许使用空值：

Assume the previously created table CUSTOMERS and let us modify the ADDRESS column ensuring that it does not allow null values using the following query −

ALTER TABLE CUSTOMERS MODIFY COLUMN ADDRESS CHAR(25) NOT NULL;

Output

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

我们可以使用以下查询显示 CUSTOMERS 表的结构：

We can display the structure of the CUSTOMERS table using the following query −

DESCRIBE CUSTOMERS;

如我们在以下输出中看到，对“ADDRESS”列进行了修改，这意味着该列 NOT 允许使用 NULL 值。

As we can see in the output below, the column "ADDRESS" is modified, which means NULL values are NOT allowed in this column.

Field

Type

Null

Key

Default

Extra

ID

int

NO

PRI

NULL

NAME

varchar(20)

NO

NULL

AGE

int

NO

NULL

ADDRESS

char(25)

NO

NULL

SALARY

decimal(20,2)

YES

NULL