When writing DDL in SQL, you can specify a couple of constraints on columns, like NOT NULL or DEFAULT constraints.
, 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.
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.colINSERT INTO tbl (A, B, col) 第二个插入记录，指定col为Default VALUES (NULL, NULL, DEFAULT);-- 3. This will insert "MyDefault"-- into tbl.colINSERT 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.colUPDATE tbl SET col = DEFAULT;-- 6. This will update NULL -- into tbl.colUPDATE 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.