SQL Column definition : default value and not null redundant?
SqlDdlSql Problem Overview
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 ?
Sql Solutions
Solution 1 - Sql
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);
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, they're not redundant.
Solution 2 - Sql
Even with a default value, you can always override the column data with null
.
The NOT NULL
restriction won't let you update that row after it was created with null
value
Solution 3 - Sql
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
Solution 4 - Sql
I would say not.
If the column does accept null values, then there's nothing to stop you inserting a null value into the field. As far as I'm aware, the default value only applies on creation of a new row.
With not null set, then you can't insert a null value into the field as it'll throw an error.
Think of it as a fail safe mechanism to prevent nulls.
Solution 5 - Sql
> 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.
> 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.
Solution 6 - Sql
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.