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"
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?
In other words, doesn't DEFAULT render NOT NULL redundant ?
No, it is not redundant. To extended accepted answer. For column col which is nullable awe can insert NULL even when DEFAULT is defined:
CREATE TABLE t(id INT PRIMARY KEY, col INT DEFAULT 10);
-- we just inserted NULL into column with DEFAULT
INSERT INTO t(id, col) VALUES(1, NULL);
+-----+------+
| ID | COL |
+-----+------+
| 1 | null |
+-----+------+
Oracle introduced additional syntax for such scenario to overide explicit NULL with default DEFAULT ON NULL:
CREATE TABLE t2(id INT PRIMARY KEY, col INT DEFAULT ON NULL 10);
-- same as
--CREATE TABLE t2(id INT PRIMARY KEY, col INT DEFAULT ON NULL 10 NOT NULL);
INSERT INTO t2(id, col) VALUES(1, NULL);
+-----+-----+
| ID | COL |
+-----+-----+
| 1 | 10 |
+-----+-----+
Here we tried to insert NULL but get default instead.
If you specify the ON NULL clause, then Oracle Database assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.
When you specify ON NULL, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified.
In case of Oracle since 12c you have DEFAULT ON NULL which implies a NOT NULL constraint.
ALTER TABLE tbl ADD (col VARCHAR(20) DEFAULT ON NULL 'MyDefault');
ON NULL
If you specify the ON NULL clause, then Oracle Database assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.
When you specify ON NULL, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified. If you specify an inline constraint that conflicts with NOT NULL and NOT DEFERRABLE, then an error is raised.