SQL列定义:默认值和非null冗余吗? [英] SQL Column definition : default value and not null redundant?

问题描述

我已经多次看到以下语法，该语法定义了create/alter DDL语句中的一列:

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

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

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

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 ?

推荐答案

DEFAULT是在insert/update语句中没有显式值的情况下将要插入的值.假设您的DDL没有NOT NULL约束:

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:

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

或者，根据UPDATE语句中使用DEFAULT ="noreferrer"> SQL-1992 标准:

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

-- 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仍然是有效语句.因此，请回答您的问题:不，它们不是多余的.

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: No, they're not redundant.

这篇关于SQL列定义:默认值和非null冗余吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！