Re: modeling either/or relationship...

David Portas wrote:
[...]

>

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 - 21:08:34 CET