CODEWITHSUNDEEP
sqlsql-server
Flood Gravemind
Flood Gravemind

Reputation: 3803

NULL NOT NULL on Default constraint in sql

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.

Upvotes: 5

Views: 7133

Answers (4)

Joe L.
Joe L.

Reputation: 1908

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.

Upvotes: 3

Zeph
Zeph

Reputation: 1738

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

Upvotes: 4

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

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.

Upvotes: 1

Kermit
Kermit

Reputation: 34062

No, they are optional. 

column_name 
    ...
    [ NULL | NOT NULL ]

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

Upvotes: 2

Related Questions