SQL列定义：默认值和非空是否冗余？

Question

我经常看到在创建/修改DDL语句中定义列的以下语法：

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

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

- Razvan

Answer 1

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仍然是有效的语句。所以回答你的问题：不，它们不是多余的。

- Lukas Eder

---

我访问了你的链接（博客文章），本以为会看到一个解释（可能带有一些数字）关于查询性能的巨大影响，但只是简短地重申了它有影响。不冒犯，但这个链接有点误导。 - Seth Flowers

@SethFlowers：感谢你的指引，Seth。链接的文章在此期间已经被编辑过了。我会看看能否提供其他链接，或者将其删除。现在来看，你完全正确，它并没有为这个答案增加任何价值。 - Lukas Eder

谢谢 - 希望我没有听起来很粗鲁。我只是看到链接，就像“是的，这绝对是我想读的东西” - 然后有点失望 :). - Seth Flowers

@SethFlowers：不用担心，我完全同意。我的博客技能在过去的几年里得到了极大的提高，可以帮上你的忙 :) - Lukas Eder

Answer 2

即使有默认值，你仍然可以使用 "null" 覆盖列数据。

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

- Tamir

---

我认为这句话需要重新表述：“NOT NULL限制将不允许您在使用null值创建行后更新该行。” 当然，具有NOT NULL列的行可以被更新，只是不能将null作为该列的值进行更新。此外：我假设创建一行意味着插入一行。指定了NOT NULL的列的INSERT语句也不能具有空值。 - makrom

Answer 3

我的SQL老师说，如果你同时指定了DEFAULT值和NOT NULL或NULL，那么DEFAULT应该始终在NOT NULL或NULL之前表达。

像这样：

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

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

- Tanguy Labrador Ruiz

---

嗨，感谢您的参与。但是这并没有回答当前的问题，两种排序方式都是完全有效的。我发现当我手动编写表格时，将默认值放在最后更容易使NULL和NOT NULL值对齐。 - emragins

好的，我想你也是对的。我不明白为什么我的课程明确说明了我在答案中写的内容。我知道我的答案实际上并没有回答问题，但我觉得留言可能不太易读。 - Tanguy Labrador Ruiz

主观最佳实践与规则之间存在差异。保持一致的风格并不是一个坏主意，但在这种情况下，我个人更喜欢在DEFAULT之前放置NOT NULL。 无论如何，你也可以编写注释而不是答案，这可能更合适。 - makrom

那可能并没有回答问题。但那正是我在寻找的。谢谢。 - Sasuke Uchiha

Answer 4

我认为不是这样的。

如果列允许空值，那么就没有阻止您将空值插入字段的限制。据我所知，默认值仅适用于创建新行时。

将not null设置为真，则无法将null值插入字段，因为它会引发错误。

把它想象成一个故障保护机制来防止出现null值。

- Dark Hippo

---

您说的是“创建新规则”。您是想说“创建新行”吗？ - bielawski

我认为这是这个问题的一个更实际的答案。 - Noman_ibrahim

@bielawski，将近8年后，我终于纠正了那个拼写错误 :) - Dark Hippo

这应该是被接受的答案。OP提出了一个简单的问题，答案很简单，就是“不”。然而，为了文档化需要提供解释和示例。 - Nicolas Hevia

Answer 5

换句话说，“DEFAULT”难道不会使“NOT NULL”变得多余吗？

不，这并不是多余的。对于可为空的列“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引入了额外的语法来覆盖明确的NULL，使用默认值DEFAULT ON 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，但却得到了默认值。 db<>fiddle演示 如果您指定了ON NULL子句，那么当后续的INSERT语句试图分配一个被评估为NULL的值时，Oracle数据库会分配DEFAULT列值。当您指定ON NULL时，NOT NULL约束和NOT DEFERRABLE约束状态会隐含指定。

- Lukasz Szozda

Answer 6

在Oracle 12c中，您可以使用DEFAULT ON NULL，它意味着一个NOT NULL约束条件。

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

修改表

ON NULL

如果您指定了 ON NULL 子句，则当后续的 INSERT 语句试图分配一个计算为 NULL 的值时，Oracle 数据库会分配 DEFAULT 列值。

当您指定 ON NULL 时，NOT NULL 约束和 NOT DEFERRABLE 约束状态会隐式指定。如果您指定了与 NOT NULL 和 NOT DEFERRABLE 冲突的内联约束，则会引发错误。

- Roland

Answer 7

换句话说，DEFAULT不是使NOT NULL变得多余了吗？

不是的。

NOT NULL仅意味着您无法将NULL插入该列，而DEFAULT使您可以在未指定由DEFAULT修饰的字段的值的情况下插入数据。

---

例如，有一个表格：

CREATE TABLE `test_table` (
    `id` int NOT NULL AUTO_INCREMENT,
    `age` int,
    `name` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
);

你可能认为使用NOT NULL会防止你像下面这样操作：

insert into test_table (age) value (10);

是的，在这种情况下会出现错误，但不是因为 NOT NULL，而是因为你没有将 name 字段设置为 DEFAULT，MySQL 会抱怨：Field 'name' doesn't have a default value。

如果你像下面这样做：

insert into test_table (age, name) value (10, NULL);

MySQL 会报错 Column 'name' cannot be null，这就是 NOT NULL 对我们的作用。

因此，NOT NULL 只是防止您向该字段插入 NULL 值，而 DEFAULT 允许您插入数据而不指定特定字段的值，它们是不同的。

- Shaowen Zhu