SQL Column definition : default value and not null redundant?
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
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.
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
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.