Re: Unique Constraint with Multiple NULLS

> > > What is the SQL-92 standard for allowing multiple NULL values within a
> > > unique constraint?
>
> > A UNIQUE constraint treats NULLs in the same column as equal to each
other.
> > In other words UNIQUE(colX) will not be satisfied if colX contains more
than
> > one NULL value. This is consistent with the behaviour of other grouping
> > operations like GROUP BY and DISTINCT.
>
> However, this is also *in*consistent with the = and <> comparison
> operators within JOIN, WHERE, HAVING, etc. NULL as a concept is
> horribly broken and ruins the relational model as defined by Codd.
>

This problem is manifesting itself quite a bit in a project I am working on. We need to implement some type of versioning in tables which forces us to have many columns that allow NULL because the information is not available yet. However once all the NULLs are replaced with values we need to enforce several constraints.

One thought I had was creating two tables, a builder table for the uncompleted version which allows NULLs and has few check constraints, and a completed which does not allow nulls and has more strict check constraints. Once the record is valid it would be moved from the builder table to the complete table and deleted from the builder table.

Any flaws with this design?

Simplified Example:

create table BUILDER (

    PriKey int not null primary key,
    FooKey1 null default null,
    FooKey2 null default null,
    FooKey3 null default null
)

create table COMPLETE (

    PriKey int not null primary key,

    FooKey1 not null,
    FooKey2 not null,
    FooKey3 not null,

    constraint u_fookey2 unique( FooKey2, FooKey1),     constraint u_fookey3 unique( FooKey3, FooKey1) ) Received on Thu Jan 01 2004 - 23:22:40 CET