sql-not-null-constraint

SQL - NOT NULL 约束

NOT NULL 约束是数据库中确保数据完整性的基本规则。它强制要求列不能包含 NULL 值。默认情况下，表中的列可以存储 NULL 值，但应用此约束会强制该列在每一行中都必须存在一个值，从而防止数据缺失或未定义。

在表创建时定义 NOT NULL

应用 NOT NULL 约束最常见的方式是在使用 CREATE TABLE 语句创建表时。您可以在列的数据类型之后立即指定 NOT NULL 关键字。

语法

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype, -- 此列可以为 NULL
    column3 datatype NOT NULL,
    ...
);

示例

让我们创建一个 PRODUCTS 表，其中产品的 ID、NAME 和 PRICE 是必不可少的，且不能为空。

CREATE TABLE PRODUCTS (
    ID INT PRIMARY KEY, -- 主键隐式为 NOT NULL
    NAME VARCHAR(100) NOT NULL,
    PRICE DECIMAL(10, 2) NOT NULL,
    SKU VARCHAR(50),
    DESCRIPTION TEXT
);

现在，如果我们尝试插入一行，而其中没有 NAME 值或 PRICE 的值为 NULL，数据库将拒绝该操作并返回错误。

-- 此语句将失败
INSERT INTO PRODUCTS (ID, NAME, PRICE) VALUES (101, NULL, 49.99);

-- 错误：[特定于数据库的错误消息，例如“列 'NAME' 不能为空”]

成功的插入必须为所有 NOT NULL 列提供值：

-- 此语句将成功
INSERT INTO PRODUCTS (ID, NAME, PRICE, SKU) VALUES (102, 'Wireless Mouse', 24.99, 'WM-102');

向现有表添加 NOT NULL 约束

您可以使用 ALTER TABLE 语句向现有表中的列添加 NOT NULL 约束。但是，如果该列当前包含任何 NULL 值，此操作将失败。您必须首先将所有 NULL 条目更新为有效值。

两步流程

让我们为 PRODUCTS 表的 SKU 列添加 NOT NULL 约束。

步骤 1：更新现有的 NULL 值。 对于任何 SKU 为 NULL 的现有行，我们需要确定一个合理的默认值或唯一值。

-- 假设我们有 SKU 为 NULL 的行。我们提供一个占位符。
UPDATE PRODUCTS
SET SKU = 'NOT-SET'
WHERE SKU IS NULL;

步骤 2：应用 NOT NULL 约束。 此操作的语法因不同的数据库系统而异。

语法 (PostgreSQL / SQL Server)

ALTER TABLE PRODUCTS
ALTER COLUMN SKU SET NOT NULL;

语法 (MySQL)

ALTER TABLE PRODUCTS
MODIFY COLUMN SKU VARCHAR(50) NOT NULL;

移除 NOT NULL 约束

类似地，您可以移除 NOT NULL 约束以允许列中存在 NULL 值。这同样通过 ALTER TABLE 语句完成。

语法 (PostgreSQL / SQL Server)

ALTER TABLE PRODUCTS
ALTER COLUMN SKU DROP NOT NULL;

语法 (MySQL)

在 MySQL 中，您需要重新定义该列，将 NOT NULL 改为 NULL。

ALTER TABLE PRODUCTS
MODIFY COLUMN SKU VARCHAR(50) NULL;

NOT NULL 与 DEFAULT

NOT NULL 和 DEFAULT 经常一起使用。NOT NULL 强制要求必须存在一个值，而 DEFAULT 则在 INSERT 语句中没有明确提供值时，提供一个备用值。

示例

让我们创建一个表，其中包含一个状态列，该列必须有值，并且如果未指定，则默认为“pending”。

CREATE TABLE ORDERS (
    ORDER_ID INT PRIMARY KEY,
    ORDER_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    STATUS VARCHAR(20) NOT NULL DEFAULT 'pending'
);

-- 此 INSERT 语句未指定 STATUS，因此它将获得默认值 'pending'。
INSERT INTO ORDERS (ORDER_ID) VALUES (1001);

最佳实践和注意事项

• 识别强制性数据： 对所有对于记录有意义和功能至关重要的列（例如，用户电子邮件、订单日期、交易金额）应用 NOT NULL。
• 主键： 所有主键列都隐式为 NOT NULL，因此您无需为它们明确指定。
• 避免过度使用： 对于可选数据字段（例如，middle_name、address_line_2），允许 NULL 是更合适且更灵活的做法，而非使用空字符串或特殊值。