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.
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.
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
sql server - Why is adding a NOT NULL column with a default constraint instantaneous? - Database Administrators Stack Exchange
Behavior of NOT NULL DEFAULT
sql - Why do we use default along with not null in database columns? - Stack Overflow
mysql - Field both NOT NULL and DEFAULT NULL - Database Administrators Stack Exchange
Videos
Hey all! I’m currently implementing a database using mariadb and was wondering whether it’s good practice to avoid NULL values in any column by using the NOT NULL constraint? Also, should all columns with a NOT NULL constraint have a DEFAULT constraint as well? Thanks in advance!
A default clause is only applied when you don't reference the column when inserting, explicitly inserting (or updating) null to a column will still allow you to store null. Using a not null constraint prevents that.
So both clauses serve different purposes, and there is no overlap.
The SQL standard allows you to use DEFAULT instead of a value to explicitly assign the default value in an insert or update. Be aware though that not all DBMSes support this.
A DEFAULT value is useful when you don't want to always specify a value (especially when you have tables with 10+ columns, and a successful INSERT only requires a couple column's worth of data). If you had a cars table with a has_steering_wheel column (which is t in most cases), you could rely on the DEFAULT instead of specifying the column every time you wanted to do an INSERT. Basically, it saves you some keystrokes.
A NOT NULL constraint is useful when you need to require a value, and there should be no exceptions. A typical example would be a color on a cars table.
To combine both DEFAULT and NOT NULL would be to require a value, and that value doesn't usually deviate from the standard. An example would be a has_power_locks column on a cars table (typically the answer is t, and sometimes it could be f, but it should never be NULL -- unless your application is coded to handle NULL with either t or f).
There is no difference. NULL DEFAULT NULL is the implicit default.
From the CREATE TABLE documentation:
- If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified
From the "Data Type Default Values" chapter:
- If a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows: If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.
price DOUBLE NULL;
price is a double and can be null and its default value is null.
price DOUBLE DEFAULT NULL;
price is a double and can be null and its default value is null.
price DOUBLE NULL DEFAULT NULL;
price is a double and can be null and its default value is null.
NULLs have special behavior: comparing anything with a NULL gives you back a NULL, which is something else than false or 0. It means "unknown".
For example, take this table:
user_id | gender
------------------
1 | NULL
2 | 'M'
3 | 'F'
4 | 'F'
SELECT * FROM mytable WHERE gender = 'M' will return 1 row, as expected
SELECT * FROM mytable WHERE gender != 'M' will return 2 rows, NOT 3 rows.
SELECT * FROM mytable WHERE gender != 'M' OR gender IS NULL will return the expected 3 rows.
Edit: For some applications, using 0 (or, God forbid, another "magic number") instead of NULL is not even advisable (units or exact values are not relevant in this example):
Date | Temperature
--------------------------
2010-01-01 | 10
2010-01-02 | 4
2010-01-03 | 0
2010-01-04 | -22
2010-01-05 | -45
2010-01-06 | NULL
2010-01-07 | -34
Here, the NULL on Jan 6th means "value unknown" - maybe because the temperature was so low that the thermometer probe stopped responding. However, it's a completely different meaning than Jan 3rd, when the temperature was 0, that is, 0 degrees.
Also, as @Bill Karwin mentions, NULLs behave specially in aggregate functions (COUNT,SUM,AVG etc.): calculating AVG(Temperature) on the above data would give you -14.5, as the NULL row is ignored.
null and "" are not the same thing, so there is no contradiction here.
What the semantic meaning of null/"" is depends on the individual and is often a "religious" issue. To some people in some schemas, of course, they may be the same, but they don't have to be. For example, a "" might mean "I explicitly asked the user for input, and they chose to enter nothing" while a null might mean "I never even asked for input".
When a field has a DEFAULT and NOT NULL specification is the DEFAULT set automatically when the record is created, or is it set only if the new incoming field is NULL?
I want the database to use the DEFAULT only when the incoming field is NULL as generation on the client side has creates some side effects which need to be logged.