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.
At Uni I was taught that the opposite is true. It's much more dangerous to make something not null without reason. With a nullable field the worst thing that can happen is you trip over the application accessing the data. Oh dear, go back and fix the app...
With a not-null field you make it impossible to add record because some arbitrary field isn't available. Now you need to change the data model and potentially fix the result in a LOT of different places...
It's good to think of null as "unknown". If there's any plausible reason why you might want to enter a record without knowing something then it should be nullable.
One of my university lecturers described it like this:
Apocryphally I've heard of a sales system in the USA which required customer's social security number to make a sale. All the till operators did when a foreigner came to the till was enter 000-00-0000. But then others would enter 123-45-6789. This makes it impossible to identify junk. It's much better to allow a field to be blank than to force it to contain junk.
Or another story. I have genuinely been refused car insurance because I don't have two phone numbers. They absolutely would not give me insurance unless I gave them two. The sales guy suggested I just give a false one. In the end I refused to lie to an insurer and just went with another company.
In practice reserve not null for fields which are required to make sense of the record. For example:
A table of places with fields (ID, Place Name, Country, Longitude, Latitude) ... "longitude" "latitude" should be nullable so that you can store the existence of a place before you know where it is.
But if you have a table who's sole purpose is to store geographical coodinates with fields (Item_id, longitude, latitude) the entire record is meaningless if longitude and latitude are null. Therefore in this instance they should be not-null
In my professional experience since uni, there are far more fields which can optional than need to be mandatory.
It strikes me as extremely counter-intuitive...
Intuitive is in the eye of the beholder and your opinion on that is shaped by the things to which you've been exposed. I hail from a time when that kind of safety wasn't standard and the tools didn't point out when you goofed up. I've been using the chain saw without a blade guard long enough that my first instinct is to avoid intuition entirely, go back to the DDL and find out exactly what assumptions the schema will let me make about its data.
...and potentially dangerous for allowing NULL's to be the default behavior.
I think you're overstating the relative dangers. NOT NULL has its own set of pitfalls that can lead to equally-insidious bugs. (Enumerating them would be fodder for a different question.)
The designer of a table always has the option of constraining a column NULL or NOT NULL and will do one or the other to get around the default, whatever it is. Not constraining a column correctly is a developer's failure to follow the business rules. Not doing the right thing elsewhere based on the column's definition is a developer's failure to understand the data he's being handed. There's no technical fix for either.
Still, I have to ask, is there any good reason for the language to be designed this way, with types being nullable by default?
No, there isn't. Because both have hazards, there's also no good reason for the language to be designed the other way. It boils down to picking your poison.