Search code examples
sqlddl

SQL Column definition: default value and not null redundant?

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?

Solution

  • 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.