SQL column definition: default, not null redundant?

Question

I have seen many times the following syntax that defines a column in a create / alter DDL statement:

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

The question is, since the default value is specified, you must also indicate that the column should not accept NULL? In other words, is DEFAULT not NOT NULL?

Answer 1

DEFAULT is the value that will be inserted if there is no explicit value in the insert / update statement. Suppose your DDL did not have a 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 in accordance with 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 that not all databases support all of these standard SQL syntaxes. Adding a NOT NULL will result in an error with operators 4, 6 , while 1-3, 5 are still valid operators. So, to answer your question: no, they are not redundant.

Answer 2

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

The NOT NULL will not allow you to update this line after it is created using null value

Answer 3

My SQL teacher said that if you specify both a DEFAULT value and NOT NULL or NULL , DEFAULT should always be expressed before NOT NULL or NULL .

Like this:

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

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

Answer 4

I would say no.

If the column is NULL, then you have nothing to put a null value in the field, as far as I know, the default value is applied only when creating a new rule.

If the value is not set, you cannot insert a null value in the field because it will cause an error.

Think of it as a fail-safe mechanism to prevent zeros.