Have You Ever Wondered About the Difference Between NOT NULL and DEFAULT?
When writing DDL in SQL, you can specify a couple of constraints on columns, like NOT NULL
or DEFAULT
constraints.
Some people might wonder, if the two constraints are actually redundant, i.e. is it still necessary to specify a NOT NULL
constraint, if there is already a DEFAULT
clause?
The answer is: Yes!
Yes, you should still specify that NOT NULL
constraint. And no, the two constraints are not redundant.
The answer I gave here on Stack Overflow wraps it up by example, which I’m going to repeat here on our blog:
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 第一个插入记录,没有指定col的值 INSERT INTO tbl (A, B) VALUES (NULL, NULL); -- 2. This will insert "MyDefault" -- into tbl.col INSERT INTO tbl (A, B, col) 第二个插入记录,指定col为Default 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 第四个插入记录,指定col为null,不使用默认值 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:
-- 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:
No, NOT NULL and DEFAULT are not redundant
That’s already quite interesting, so the DEFAULT
constraint really only interacts with DML statements and how they specify the various columns that they’re updating. The NOT NULL
constraint is a much more universal guarantee, that constraints a column’s content also “outside” of the manipulating DML statements.
For instance, if you have a set of data and then you add a DEFAULT
constraint, this will not affect your existing data, only new data being inserted.
If, however, you have a set of data and then you add a NOT NULL
constraint, you can actually only do so if the constraint is valid – i.e. when there are no NULL
values in your column. Otherwise, an error will be raised.
相关文章
- The superclass"javax.servlet.http.HttpServlet" was not found on the Java Build Path
- The superclass "javax.servlet.http.HttpServlet" was not found on the Java Build Path
- Your UI profile is not granted with permission to access this section. Please contact the administrator.
- The property 'RowId' is part of the object's key information and cannot be modified.
- 解决问题“The App ID ‘XXXXX‘ appears to be in use by the App Store, so it can not be removed at this ti”
- ERROR: THESE PACKAGES DO NOT MATCH THE HASHES FROM THE REQUIREMENTS FILE.win10报错解决方案
- The platform of the target `Pods` (iOS 4.3) is not compatible 错误
- SQL Developer 连接 oracle数据库 报错 Io 异常 The Network Adapter could not establish the connection的三种解决方法
- Error: [mobx] Since strict-mode is enabled, changing observed observable values outside actions is not allowed. Please wrap the code in an `action` if this change is intended.
- OpenFeign 出现The bean xxx.FeignClientSpecification could not be registered问题解决
- Nignx出现failed (3: The system cannot find the path specified)问题
- SAP S4HANA LTMC Practice - the first shot
- upstream timed out (10060: A connection attempt failed because the connected party did not properly respond
- Swift - whose view is not in the window hierarchy 问题解决方法
- Microsoft SQL Server 17导出xlsx文件时报错:The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
- The superclass "javax.servlet.http.HttpServlet" was not found on the Java Build Path
- About the iOS File System
- 异常:The last packet sent successfully to the server was 0 milliseconds ago解决
- The bean 'xx.FeignClientSpecification', defined in null, could not be registered
- 解决The JAVA_HOME environment variable is not defined correctly
- MySQL错误:The user specified as a definer (XXX@XXX) does not exist
- An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full.
- Abp ajax The required antiforgery request token was not provided in either form field
- 【安卓-疑难杂症】:你的主机中的软件中止了一个已建立的连接 and The application could not be installed: INSTALL_FAILED_USER_RESTRI
- java.lang.IllegalArgumentException: Invalid character found in the request target. The valid characters are defined in RFC 7230 and RFC 3986