NULL VALUES - Simon Gare

Hi,

is there anyway of preventing NULL values in a db, what causes them is it
the field type etc?

I have noticed that not all fields contain a NULL value others are left
blank no problem at all but I need to resolve the issue.

Regards
Simon

--
Simon Gare
The Gare Group Limited

website:   www.thegaregroup.co.uk
website:   www.privatehiresolutions.co.uk

NULL VALUES - Adam Machanic

You can define your columns as non-nullable:

CREATE TABLE YourTable
(
YourColumn INT NOT NULL,
YourOtherColumn VARCHAR(50) NOT NULL,
... (etc)
)


--

Adam Machanic
SQL Server MVP - http://sqlblog.com

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220

NULL VALUES - Aaron Bertrand [SQL Server MVP]

If the column allows NULLs, and you allow direct access to the table, then
yes anyone can create a row and insert only into the columns they want.  If
you don't want NULLs in that column, then you can define the column as NOT
NULL.  Attempts to not enter a value into that column will result in an
error, unless you also specify a default column.  Note that you can't change
a column to NOT NULL after the fact unless you also specify a default value.
Now once you have NOT NULL specified, try to ensure that data access is
through stored procedures, so people can't make up their own insert
statements and/or open table and change the value in those important
columns.

From your narrative, it sounds like NULL and blank mean similar things to
you, so I'm not sure why "blank"s are okay but NULLs aren't?  In this case,
I'm not sure what you would gain by forcing someone to enter a blank string
instead of explicit NULL; if they mean the same thing, the end result is the
same.  You can always just have your retrieval query or queries use
COALESCE() to change a NULL to a "blank" so that the NULLs are not visible
externally.

In case it wasn't obvious: the difference between '' and NULL is important,
and this differentiation should not be overlooked.

--
Aaron Bertrand
SQL Server MVP