db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Derby and other RDBMS handling constraints and NULLS.
Date Wed, 02 Nov 2005 07:34:54 GMT
I lost my DB server that was also my mail server to a melt down. (You can only 
keep those INTEL CPUs running with poor airflow for only so long... ;-)
[3 1/2 years or so?] So I had to go out and build a new server from the ground 

Problem Statement:
How do databases handle unique constraints placed upon their objects and 

The issue arose that Sybase allows one to place a unique constraint on a 
table, yet will allow duplicate records iff (if and only if) one of the 
constraint columns has null value.  

As per one of Daniel's posts the test for a unique constraint is to do an 
inner select to see if the row already exists. If the inner select fails to 
find any rows, then its ok to insert the row.

The problem is when you have an instance of NULL.
here's a good link to explain NULLS: 

Now with respect to unique constraints, they can be applied on either indexes 
or on tables.

For each database we need to review the consistency between how the database 
handles uniqueness in both the table clause and unique indexes.

	Derby does not allow NULLs in columns that are part of a unique table 
constraint. So this begs the question all together. No NULL values. However, 
you can create a unique index on a set of columns even when one is null.
The result is that you can create a unique index, however you can only have 
one row where an element can be NULL.

NOTE: Derby is INCONSISTENT with its treatments of UNIQUENESS on Indexes and 

	Informix does allow you to create a table constraint including columns that 
contain NULL. It will also allow you to create a row that contains null, 
however it will not allow for duplicate NULLs.  When a Unique constraint is 
applied to an index you can create an index that contains NULLS, however you 
can not create duplicates.

NOTE: INFORMIX is CONSISTANT in how it handles NULLS when working on Unique 
Constraints on either Indexes or Tables. (I didn't try a column but I'm sure 
it will be the same..

I was going to do it again on DB2, but alas, my machine melted.
And we have heard Sybase handles it differently.


In designing a database, you need to apply the KISS principle. (Thats Keep It 
Simple Stupid.) Its a real engineering term and on the deserves respect.
So in database design, we want to consistently treat NULLS along with how you 
apply constraints across containers.

This consistency will clean up and simplify your design, allowing for more 
complex issues to be implemented within a more efficient footprint.

Now having said all that, if the ANSI Standard Base were to allow for multiple 
NULLS within a UNIQUE constraint, or a unique index, It would be a pretty 
lame and bad idea. 

In fact, how can you generate an unique index with multiple NULL values?
(Hint: NULL A != NULL B, so NULL != NULL, yet since NULL represents the 
unknown set,  Try creating an unique index with having multiple tupples that 
contain NULL and the same non-null tupple. ;-)

Another important design consideration: Which takes precedence: SQL Statements 
that manipulate your containters, or ones that manipulate the data stored in 
the containers? And Why? ;-)

If you code properly, you should have a clean system.

Again, Sybase's code is a bug. 
Also with respect to the snippet posted by Junior, it doesn't handle nulls 
properly. In fact, it never addresses Nulls.

Read that URL I included.
Also think about a Scrodinger's Cat.

MEOW! ;-)

But hey, what do I know ? ;-)

You can try other databases and you'll find that they will uniquely interpret 

Think before you post. ;-)

Michael Segel

View raw message