Re: modeling either/or relationship...

lennart_at_kommunicera.umea.se wrote:

> David Portas wrote:
> [...]
> >
> > AFAIK in standard SQL the PRIMARY KEY / UNIQUE constraints are
> > interchangeable except that UNIQUE may include nullable columns
>
> Are you sure about that (unique may contain null)? I dont see how that
> should work (and I know that it does not in DB2). AFAIK the rule is
> that a foreign key constraint is satisfied as long as it does not
> evaluate to FALSE. Assuming that a unique constraint may contain null
> we can construct:
>
> CREATE TABLE PARENT (
> A int not null,
> B int,
> constraint AK UNIQUE (A, B)
> )
>
> CREATE TABLE CHILD (
> A int not null,
> B int,
> constraint FK (A, B) references PARENT (A,B)
> )
>
> insert into PARENT (A) values 1
> insert into CHILD (A) values 1 -- this satisfies FK because it does
> evaluate to NULL (i.e. it does not evaluate to FALSE)
>
> insert into CHILD (A) values 2 -- this also satisfies FK because it
> does evaluate to NULL (i.e. it does not evaluate to FALSE)
>

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.

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.

> Either I am missing something here, or null in unique does not make
> sense. Can anyone with a better insight in the sqlstandard shed some
> light on this?

Nulls are a disappointment if you like sense ;-)

-- 
David Portas