SQL列定义:默认值和非空冗余?

I've seen many times the following syntax which defines a column in a create/alter DDL statement:

我已经看到了很多次的语法，它在create/alter DDL语句中定义了一个列:

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

The question is: since a default value is specified, is it necessary to also specify that the column should not accept NULLs ? In other words, doesn't DEFAULT render NOT NULL redundant ?

问题是:既然指定了默认值，是否还需要指定列不应该接受NULLs ?换句话说，默认渲染不是NULL是多余的吗?

4 个解决方案

#1

DEFAULT is the value that will be inserted in the absence of an explicit value in an insert / update statement. Lets assume, your DDL did not have the NOT NULL constraint:

默认值是在插入/更新语句中没有显式值时插入的值。假设DDL没有not NULL约束:

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

Then you could issue these statements

然后你可以发布这些声明

-- 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);

Alternatively, you can also use DEFAULT in UPDATE statements, according to the SQL-1992 standard:

此外，还可以根据SQL-1992标准在更新语句中使用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;

Note, not all databases support all of these SQL standard syntaxes. Adding the NOT NULL constraint will cause an error with statements 4, 6, while 1-3, 5 are still valid statements. So to answer your question:

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

No, NOT NULL and DEFAULT are not redundant. In particular, NOT NULL can have a tremendous impact on query performance as explained in this blog post here

不，不是NULL，默认也不是冗余的。特别是，正如本文所解释的，NOT NULL会对查询性能产生巨大的影响

#2

Even with a default value, you can always override the column data with null.

即使使用默认值，也总是可以用null覆盖列数据。

The NOT NULL restriction won't let you update that row after it was created with null value

NOT NULL限制不会让您在创建了空值后更新该行。

#3

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.

我的SQL老师说，如果您同时指定一个默认值，而不是NULLor NULL，那么默认值应该总是在NOT NULL或NULL之前表示。

Like this:

是这样的:

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

ALTER TABLE tbl添加列col VARCHAR(20)默认“MyDefault”不为空。

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

添加列col VARCHAR(20)默认的“MyDefault”NULL

#4

I would say not.

我想说不是。

If the column does accept null values, then there's nothing to stop you inserting a null value into the field, as far as I'm aware, the default value only applies on creation of a new rule.

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

With not null set, then you can't insert a null value into the field as it'll throw an error.

使用非空集，则不能将空值插入字段，因为这会抛出错误。

Think of it as a fail safe mechanism to prevent nulls.

把它看作是一种失效的安全机制来防止空值。

#1