NULL in column constraint? 
Author Message
 NULL in column constraint?

Dear Folks,

We're new to SQL, and we've been trying our program against DB2 6.1 (among
others). In doing so we've come across an inconsistency in the use of NULL
(not NOT NULL) in column constraints. Some databases don't mind NULL; for
example, the following VARCHARs all allow NULLs:

CREATE TABLE link (
 id INT NOT NULL,
 o1_host VARCHAR(256) NULL,
 o1_db VARCHAR(64) NULL,
 o1_id INT NOT NULL,
 o2_host VARCHAR(256) NULL,
 o2_db VARCHAR(64) NULL,
 o2_id INT NOT NULL);

This works fine in Cloudscape 3.0 (an embedded Java DB), SQL Server 7.0, and
others, but DB 2 6.1 (and Interbase 5.5, it turns out) complains about it.
The DB2 syntax diagram shows only NOT NULL, but other systems allow NULL -
Cloudscape docs
say:

NULL
The opposite of NOT NULL (not really a constraint), it specifies that the
column can hold NULL values. Specifying NULL is the same as saying nothing
at all, except when the column is included in a PRIMARY KEY constraint.

SQL Server docs say:

NULL | NOT NULL
Are keywords that determine whether or not null values are allowed in the
column. NULL is not strictly a constraint, but can be specified in the same
manner as NOT NULL.

...It is recommended that you always explicitly define a column as NULL or
NOT NULL

So what we'd really appreciate knowing is:

o Is NULL (as shown above) standard SQL? Since IBM pretty much invented SQL,
I can't believe they're non-standard. (BTW, I looked for the entry level
SQL92
standard online, but it looks as if it must be purchased. Strange!)

o If we omit NULL and just specify NOT NULL, is the default for all DBs
always NULL? (Yea, I know saying "all DBs" is asking for it.)

o What is the standard amongst developers in the know?

Thanks!

Matthew Cornell
Senior Developer
UMass



Sun, 08 Sep 2002 03:00:00 GMT
 NULL in column constraint?

Hi,

I don't have the standrad handy (and no desire to fight through it
either ;-).
However
"SQL-99 Complete, really" from R&D books has in its <column constraint>
paragraphs no notion of NULL, only NOT NULL.
From a language point of view NULL seems a bad choice to me.
Wouldn't NULLABLE be better?

In DB2 the default is nullable.

Cheers
Serge



Mon, 09 Sep 2002 03:00:00 GMT
 NULL in column constraint?
I just verified with our standards expert that this NULL option is not
part of the SQL standard.
--
___________________________________________________________________________
    Doug Doole
    DB2 Universal Database Development
    IBM Toronto Labs


Tue, 10 Sep 2002 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Inserting NULLs into columns with DEFAULT constraint

2. problem with sp_repladdcolumn when column has UDT with not null constraint

3. UNIQUE constraint, paired columns, null value

4. Unique indexes and constraints cannot be created on columns that allow null

5. Adding a column with NOT NULL constraint and user defined default

6. Can a NOT NULL column constraint be dropped?

7. UNIQUE constraints and NULL columns

8. How to drop a NOT NULL column constraint?

9. NOT NULL restriction and CHECK constraint on same column in 7.3.4

10. column is null vs. column = coalesce(@var,null)

11. How to ensure if column A is null, column B has to be null


 
Powered by phpBB® Forum Software