NULL NOT NULL on Default constraint in sql

Question

If I set a Record in Sql with a Default constraint like

[Publicbit] BIT DEFAULT ((0)),

Do I need to set the NULL/NOTNULL Constraint?

Edit: I am using a boolean but please expand your answer to other datatypes.

• Kermit: @marc_s Guessing SQL Server given the brackets and BIT type.
• Flood Gravemind: @marc_s thanks for the reply I am using MSSQL

Answer 1

You never need the Null constraints, but the default value won't guard your table against explicit NULL's. So you should use a NOT NULL constraint if you want to enforce this condition.

use tempdb
go
CREATE TABLE example
(
 id BIT DEFAULT (0)
)
INSERT example (id) VALUES (null)
SELECT * FROM example

• marc_s: Hint: I would always recommend to explicitly name your (default) constraints: id BIT CONSTRAINT DF_Example_ID DEFAULT (0) so that you'll have an well-known name for your constraint if you ever need to disable and/or drop it.

Answer 2

You should specify NOT NULL, to protect against a NULL from getting into this record. It sounds like the only valid values you want are 0 and 1. If someone (or you in the future) writes a record (or code) passing in a NULL for this column, then it will be allowed unless you specified NOT NULL.

By specifying a DEFAULT, you are only protecting against a NULL value if the SQL INSERT statement doesn't include that column.

Answer 3

No, they are optional.

column_name 
 ...
 [ NULL | NOT NULL ]

You shouldn't expect a NULL when your options are 0/1.

• Flood Gravemind: what about other datatypes such as NVARCHAR and Ints?
• Kermit: @FloodGravemind It depends on your data requirements. I don't want to say there's a general rule, but given that those attributes are optional then I suggest that you don't need these attributes.

Answer 4

These are just optional. It is up to you if you want to specify it or not

From MSDN:

Determines whether null values are allowed in the column. NULL is not strictly a constraint but can be specified just like NOT NULL.