Isn't it extra overhead?
When we mention default x (for example, alter table users add column id default 0 it is not gonna allow null at database level ) . So why use not null along with default in queries for a column?
Add a comment
|
2 Answers
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.
Comments
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).
