CODEWITHSUNDEEP
mysqldatabase-design
WildBill
WildBill

Reputation: 9291

Should booleans be NOT NULL in mysql?

In designing my database I realized I left some booleans as NOT NULL and others default to NULL. What is the rule of thumb for when to allow a boolean to be nullable? As the booleans I have are all is_SOMETHING, I'm thinking they should never NULL as they are either classified as the SOMETHING or not.

Upvotes: 9

Views: 10952

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562378

It's something of a pity that SQL treats all columns as nullable by default, if you don't specify NOT NULL. Arguably, it is more common for attributes to require a value and not a null, so it would have been nice if SQL were defined to treat attributes as NOT NULL by default instead of the other way around.

In your case, I would assume the attribute should be NOT NULL unless you have a need to represent a case of "did not specify" or "inapplicable" which is what NULL is for.

We can't answer that from your description. It depends on the usage and requirements of your project.

It's even possible that some of your boolean should be nullable, while others should be NOT NULL, in the same database!

Upvotes: 7

akuzminsky
akuzminsky

Reputation: 2258

It makes sense to decide from an application perspective whether a column can be NULL or not .

NULL is quite appropriate when some property can be encoded in two-state variable, but in some cases the value is not applicable.

Upvotes: 4

Tripp Kinetics
Tripp Kinetics

Reputation: 5449

A NULL boolean has three possible values: true, false, and null. A NOT NULL boolean has only two, true and false. Your application logic will have to suffice for telling you which is the correct choice.

Upvotes: 8

Related Questions