UTEP Oracle 9i
UTEP Oracle 10g Notes

Knowledge Base:

entity and referential integrity rules

Pragmatically, dealing with NULL brings added complexity to the storage engine, because SQL Server keeps a special bitmap( see picture below) in every row to indicate which nullable columns actually are NULL. If NULLs are allowed, SQL Server must decode this bitmap for every row accessed. Allowing NULL also adds complexity in application code, which can often lead to bugs. You must always add special logic to account for the case of NULL.

You, as the database designer, might understand the nuances of NULL and three-valued logic when used in aggregate functions, when doing joins, and when searching by values. However, you need to consider whether your development staff understands as well. We recommend, if possible, that you use all NOT NULL columns and define default values (discussed later in this chapter) for missing or unknown entries (and possibly make such character columns varchar if the default value is significantly different in size from the typical entered value).

In any case, it's good practice to explicitly declare NOT NULL or NULL when creating a table. If no such declaration exists, SQL Server assumes NOT NULL. (In other words, no NULLs will be allowed.) However, you can set the default to allow NULLs by using a session setting or a database option. The ANSI SQL standard says that if neither is specified, NULL should be assumed, but as mentioned, this isn't SQL Server's default. If you script your DDL( Database Definition Language) and then run it against another server that has a different default setting, you'll get different results if you don't explicitly declare NULL or NOT NULL in the column definition.

As you can see, you can configure and control the treatment and behavior of NULL values several ways, and you might think it would be impossible to keep track of all the variations. If you try to control every aspect of NULL handling separately within each individual session, you can cause immeasurable confusion and even grief.

However, you'll notice that most of the issues become moot if you follow a few basic recommendations:

Therefore, for an entity integrity rule, there is absolutely no null entries in primary key field. For the referential integrity rule, there is foreign key to link with another table with the same entries, including null values.

If you must use NULLs in some cases, you can minimize problems by always following the same rules, and the easiest rules to follow are the ones that ANSI already specifies.