Re: Tree constraint in the adjacency graph model

From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Sat, 25 Jun 2005 04:27:09 +0200
Message-ID: <42bcc0ff$0$37751$e4fe514c_at_news.xs4all.nl>

Mikito Harakiri wrote:
> Stefan Rybacki wrote:
>

>>>>Consider adding these constraints: QuoteNo as key,
>>>>and PreviousQuoteNo as foreign key to QuoteNo.
>>>>
>>>>Only now we have the intended tree-consistency.
>>>>The circular states mentioned above are inconsistent with the
>>>>constraints, and I can't add these values by hand or by algorithm.
>>>
>>>
>>>Oops - still not good enough.
>>
>>
>>It should be good enough for an insert but not for an update, or did I
>>forget something?

>
>
> Why QuoteNo as unique key alone is not good enough? And, more
> important, why it's not good for update?

'update' is not really the issue.

---

create table quoterequest ( quote integer primary key,

                             previousquote integer );
alter table quoterequest
       add constraint QR_TREE1
       foreign key (previousquote) references quoterequest(quote);

--alter table quoterequest
--add constraint QR_TREE2 CHECK (previousquote < quote);

insert into quoterequest values (1, NULL);
insert into quoterequest values (2, NULL);
insert into quoterequest values (3, 3);

select quote relquote from quoterequest

    connect by previousquote = prior quote     start with previousquote = 3
union
select previousquote relquote from quoterequest

    connect by quote = prior previousquote     start with quote = 3
/

---

.ORA-01436: CONNECT BY loop in user data

with the add CHECK constaint this just rejects the third insert - as wanted.

> With QuoteNo as unique key no child having 2 parents is allowed, so
> that the only structures that escape this constrait are cycles. Then,
> disallow cycles requiring that at least one node should either have 2
> children or no children at all.

Hmm... how? Received on Sat Jun 25 2005 - 04:27:09 CEST