Re: Unique Constraint with Multiple NULLS

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 7 Jan 2004 00:38:48 -0800
Message-ID: <6dae7e65.0401070038.4b3c7cf@posting.google.com>

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message news:<G_2dncia7cWZCGmiRVn-uw_at_giganews.com>...
> > 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.
>
>

I was'nt aware that null was allowed for unique constraints (db2 does not allow it). Given that it is a legal construct, the following is also legal then?

create table A (

   c1 int not null primary key,
   c2 int,
   unique (c2)
)

create table B (

   c1 int not null primary key,
   c2 int,
   foreign key(c2) references A(c2)
)

or can you only reference a unique and not nullable column in the parent table?

Kind regards
/Lennart

[...] Received on Wed Jan 07 2004 - 02:38:48 CST