SQL 列定义: 默认值而非空冗余？

我多次看到以下语法在 create/alter DDL 语句中定义列:

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) NOT NULL DEFAULT "MyDefault"

问题是: 既然指定了默认值，是否还需要指定列不应该接受 NULL？换句话说，DEFAULT不会使 NOT NULL变得多余吗？

138419 次浏览
小开

即使使用默认值，也始终可以使用 null重写列数据。

NOT NULL限制不允许您在使用 null值创建该行之后更新该行

小开
最佳答案

DEFAULT是在插入/更新语句中没有显式值的情况下插入的值。假设您的 DDL 没有 NOT NULL约束:

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT 'MyDefault'

然后你可以发表这些声明

-- 1. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B) VALUES (NULL, NULL);


-- 2. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, DEFAULT);


-- 3. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) DEFAULT VALUES;


-- 4. This will insert NULL into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, NULL);

或者，您也可以根据 SQL-1992标准在 UPDATE语句中使用 DEFAULT:

-- 5. This will update 'MyDefault' into tbl.col
UPDATE tbl SET col = DEFAULT;


-- 6. This will update NULL into tbl.col
UPDATE tbl SET col = NULL;

注意，并非所有数据库都支持所有这些 SQL 标准语法。添加 NOT NULL约束将导致语句 4, 6出错，而 1-3, 5仍然是有效语句。所以回答你的问题: 不，它们不是多余的。

小开

我不这么认为。

如果列确实接受 null 值，那么就没有什么可以阻止您将 null 值插入到字段中。据我所知，默认值只应用于新行的创建。

如果没有 null 设置，那么就不能在字段中插入 null 值，因为它会抛出一个错误。

可以将其视为防止 null 的故障安全机制。

小开

My SQL teacher said that if you specify both a DEFAULT value and NOT NULLor NULL, DEFAULT should always be expressed before NOT NULL or NULL.

像这样:

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT "MyDefault" NOT NULL

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT "MyDefault" NULL

小开

In other words, doesn't DEFAULT render NOT NULL redundant ?

不，这不是多余的。延长接受的答案。对于 col列，即使定义了 DEFAULT，它也可以插入 NULL:

CREATE TABLE t(id INT PRIMARY KEY, col INT DEFAULT 10);


-- we just inserted NULL into column with DEFAULT
INSERT INTO t(id, col) VALUES(1, NULL);


+-----+------+
| ID  | COL  |
+-----+------+
|   1 | null |
+-----+------+

Oracle 为这种场景引入了额外的语法，用默认的 DEFAULT ON NULL覆盖显式的 NULL:

CREATE TABLE t2(id INT PRIMARY KEY, col INT DEFAULT ON NULL 10);
-- same as
--CREATE TABLE t2(id INT PRIMARY KEY, col INT DEFAULT ON NULL 10 NOT NULL);


INSERT INTO t2(id, col) VALUES(1, NULL);


+-----+-----+
| ID  | COL |
+-----+-----+
|  1  |  10 |
+-----+-----+

这里我们尝试插入 NULL，但是得到了 default。

Db < > 小提琴演奏

空

If you specify the ON NULL clause, then Oracle Database assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

指定 ON NULL 时，将隐式指定 NOT NULL 约束和 NOT DEFERABLE 约束状态。

小开

对于 Oracle 来说，从12c 开始你就有了 DEFAULT ON NULL，这意味着一个 NOT NULL约束。

ALTER TABLE tbl ADD (col VARCHAR(20) DEFAULT ON NULL 'MyDefault');

更改桌子

空

如果指定 ONNULL 子句，则 Oracle 数据库将 后续 INSERT 语句尝试 分配一个计算结果为 NULL 的值。

当您指定 ON NULL 时，NOTNULL 约束和 NOTNDEFERABLE 约束状态是隐式指定的。如果指定内联 与 NOT NULL 和 NOT DEFERABLE 冲突的约束，则为 错误被提出。


提交答案