CODEWITHSUNDEEP
sqlddl
Razvan
Razvan

Reputation: 10093

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?

Upvotes: 133

Views: 172725

Answers (7)

Shaowen Zhu
Shaowen Zhu

Reputation: 105

In other words, doesn't DEFAULT render NOT NULL redundant?

No, they are not.

NOT NULL just means you cannot insert NULL to that column, and DEFAULT enables you insert data without specify value to the field which decorated by DEFAULT.

e.g., there is a table:

CREATE TABLE `test_table` (
    `id` int NOT NULL AUTO_INCREMENT,
    `age` int,
    `name` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
);

You probably think that NOT NULL will prevent you from doing this like below:

insert into test_table (age) value (10);

Yes, there will be an error but not because NOT NULL, in this case, MySQL will complain because you didn't set DEFAULT to name field: Field 'name' doesn't have a default value.

If you do this like below:

insert into test_table (age, name) value (10, NULL);

The mysql will give you an error Column 'name' cannot be null, this is what NOT NULL "does" for us.

So, NOT NULL just prevent you from inserting NULL value to that field, whereas DEFAULT allows you insert data without specifying the value of a specific field, they are different.

Upvotes: 0

Roland
Roland

Reputation: 7853

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');

ALTER TABLE

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.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

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.

db<>fiddle demo

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.

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220762

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.

Upvotes: 174

Dark Hippo
Dark Hippo

Reputation: 1265

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.

Upvotes: 3

Tanguy Labrador Ruiz
Tanguy Labrador Ruiz

Reputation: 295

My SQL teacher said that if you specify both a DEFAULT value and NOT NULLor 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

Upvotes: 6

Tamir
Tamir

Reputation: 3901

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

Upvotes: 32

Related Questions