Re: modeling either/or relationship...

David Portas wrote:
> lennart_at_kommunicera.umea.se wrote:
>

[...]
>
> Since SQL92 NULLs are permitted in UNIQUE constraints (SQL89 didn't
> allow them). Rows with nulls aren't compared in the unique constraint
> so you can add multiple rows with nulls in the key columns even though
> the other values are duplicated.
>
> Here is ISO/IEC 9075-2:2003 section 8.10:
>
> <quote>
>
> <unique predicate> ::= UNIQUE <table subquery>
>
> 1) Let T be the result of the <table subquery>.
>
> 2) If there are no two rows in T such that the value of each column
> in one row is non-null and is not distinct from the value of the
> corresponding column in the other row, then the result of the
> <unique predicate> is True; otherwise, the result of the <unique
> predicate> is False.
>
> </quote>
>
> The constraint is based on this predicate.
>

Thanx

> As far as foreign keys go, a foreign key containing nulls does not
> violate the REFERENCES constraint anyway so your example is valid
> whether or not nulls are allowed by the UNIQUE constraint.
>

Yes, I realised that the example was even sillier than I thought as soon as I had hit the sendbutton. Still, I get this strange feeling thinking about nulls in unique. Cant put my finger on it though.

[...]

> Nulls are a disappointment if you like sense ;-)
>

;-)

/Lennart Received on Wed Feb 01 2006 - 22:39:01 CET