Re: modeling either/or relationship...

From: <lennart_at_kommunicera.umea.se>
Date: 1 Feb 2006 12:08:34 -0800
Message-ID: <1138824514.430471.113900@o13g2000cwo.googlegroups.com>

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)

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?

/Lennart

[...] Received on Wed Feb 01 2006 - 14:08:34 CST