Re: NULL values in Unique constrain

Phil Singer <psinger1_at_chartermi.net> wrote in message news:<3BBBC4B4.C36357A3_at_chartermi.net>...

> Diego Dal Cero wrote:
> > create table mytable (
> > value1 integer not null,
> > value2 integer null
> > value3 integer not null,
> > unique (value1, value2, value3)
> > )
> >
> > Is this correct or the "value2" column should be also "not null"? In
> > my application, in certain cases, the "value2" could be null.
> >
> If value1 or value3 are going to be your primary key,
> then you will have no problems (aside from the generic
> ones involving NULLs which I'll leave for a wiser one
> than me to comment on).
>

The primary key is a composite of two values. The definition of the table is a bit more complicated. I understand that the problem could be bigger than the one that involve the NULL value in value2.

The structure is like this one:

create table table1 (

    pk0 integer not null,
    pk1 integer default 0 not null,
    value1 integer not null,
    value2 integer null
    value3 integer not null,
[... other non relevant content columns ...]

    primary key (pk0,pk1),
    unique (value1,value2,value3),
    foreign key (value1) references table2(table2pk0),     foreign key (value3) references table3(table3pk0) )

I was thinking that while the uniqueness is granten even using NULL in value2, the table will create correctly. I don't know is its possible to redesign this table to define it better or using a way that is easiest for the db enginge to handle it...

I could change the composite primary key till a single value primary key (and using a foreing referenced table to itself):

create table table1 (

    pk0 integer not null,
    value1 integer not null,
    value2 integer null
    value3 integer not null,
[... other non relevant content columns ...]

    primary key (pk0),
    unique (value1,value2,value3),
    foreign key (value1) references table2(table2pk0),     foreign key (value3) references table3(table3pk0) )

create table table1v (

    father integer not null,
    was_pk1_in_table1 integer default 0 not null, [... other non relevant content columns ...] )

I don't know which design is better (I think that the second one will use a bit less amount of data (the value1, value2 and value3 aren't needed), but needs more programming and require harder work, unions and joins, to retrive the data.
The data in the table1 is more accessed than wrote (1 write for a millon query).

Ciao,
Diego Dal Cero Received on Thu Oct 04 2001 - 21:55:35 CEST