sql-default-constraint

SQL - DEFAULT 约束 (Constraint)

DEFAULT 约束为未指定值的 INSERT INTO 语句中的列提供默认值。这是一个强大的工具，用于确保数据完整性、简化应用程序逻辑并使你的数据库 schema 更健壮。

为什么要使用 DEFAULT 约束？

数据完整性： 确保如果省略了值，则列不会为 NULL（前提是该列也为 NOT NULL）。
简化插入： 允许你在 INSERT 语句中省略具有默认值的列，使你的查询更简洁。
强制业务规则： 自动设置初始状态（例如，status VARCHAR(20) DEFAULT 'pending'）、时间戳或标志。

定义 DEFAULT 约束

语法（在表创建期间）

添加 DEFAULT 约束最常见的方式是在你首次创建表时。

CREATE TABLE table_name (
    column1 datatype CONSTRAINT constraint_name DEFAULT default_value,
    column2 datatype DEFAULT default_value,
    ...
);

提供给 DEFAULT 的值必须与列的数据类型 (data type) 匹配。例如，对于 INT 列使用数字，对于 VARCHAR 列使用带引号的字符串。

示例：现代用户资料表

让我们创建一个 Users（用户）表，我们希望自动记录用户帐户创建时间并分配默认的订阅级别。

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT, -- AUTO_INCREMENT 是一种常见的非标准扩展
    Username VARCHAR(50) NOT NULL UNIQUE,
    SubscriptionLevel VARCHAR(10) DEFAULT 'free',
    IsActive BOOLEAN DEFAULT TRUE,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 使用函数作为默认值
);

在这个例子中：

SubscriptionLevel 将为 ‘free’ 如果未指定。
IsActive 将为 TRUE 对于新用户。
CreatedAt 将自动设置为当前的插入时间。

现在，让我们插入一个新用户，只提供必需的 Username。

INSERT INTO Users (Username) VALUES ('alex_jones');

如果我们查询该表，会看到默认值已经应用：

用户ID	用户名	订阅级别	是否活跃	创建时间
1	alex_jones	free	TRUE	2023-11-20 10:30:00

管理现有表上的 DEFAULT 约束

你也可以使用 ALTER TABLE 语句从现有表中添加或移除 DEFAULT 约束。不同数据库系统之间的语法可能略有不同。

语法：添加 DEFAULT 约束

以下是 PostgreSQL 和标准 SQL 的语法：

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

以及 MySQL 的语法：

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

语法：删除 DEFAULT 约束

对于 PostgreSQL 和标准 SQL：

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

以及 MySQL 的语法：

ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

最佳实践和常见陷阱

为动态默认值使用函数： 对于创建时间戳等值，始终使用数据库函数 (CURRENT_TIMESTAMP、NOW())，而不是硬编码静态值。
DEFAULT 与 NULL： DEFAULT 约束仅在省略值时才阻止列为 NULL。如果用户明确插入 NULL，则该值将为 NULL（除非该列也有 NOT NULL 约束）。
检查兼容性： 确保你的默认值对于列的数据类型和任何其他约束（例如 CHECK 约束）有效。
Schema 版本控制： 在添加、更改或删除 DEFAULT 约束时，将其视为 schema 迁移。使用数据库 schema 的版本控制工具（如 Flyway 或 Liquibase）来跟踪这些更改，尤其是在团队环境中。