CODEWITHSUNDEEP
sqlpostgresql
sofs1
sofs1

Reputation: 4176

Why do we use default along with not null in database columns?

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?

Upvotes: 2

Views: 2091

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109134

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.

Upvotes: 4

richyen
richyen

Reputation: 10018

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

Upvotes: 0

Related Questions