Re: A few questions about ltree

Stephan Szabo wrote:
> On Fri, 21 Apr 2006, Alban Hertroys wrote:
>>Stephan Szabo wrote:
>>
>>>>SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
>>>>ltree_test(path));
>>>>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
>>>>"ltree_test_pkey" for table "ltree_test"
>>>>CREATE TABLE
>>>>SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
>>>>INSERT 84117368 1
>>>>SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
>>>>INSERT 84117369 1
>>>>SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
>>>>INSERT 84117370 1
>>>>SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
>>>>DELETE 1
>>>
>>>I'm not sure why you expect this to error. Any row that would reference
>>>a.b would be removed by the delete AFAICS.
>>
>>Nope, there's no ON DELETE CASCADE on the FK, and RESTRICT is the
>>default (thankfully).
>
> The only row that matches 'a.b' that I see in the above is the second
> insert which is also the row that is deleted in the delete. And since the
> constraint uses equality, any row that matches path='a.b' is a target of
> the delete because it's the same operator.

Ah, I misinterpreted what you said; I thought you were talking about
records with foreign keys referencing 'a.b' being deleted. That would
have been unexpected unless there'd be an ON DELETE CASCADE on those
records.
What may be confusing here is that the FK reference is in the same table
(in fact, on the same column) as the PK being referenced.

Why I'd expect it to error is because the record with PK value 'a.b'
should have been referenced by the record with PK value 'a.b.c'.

I was hoping that the fact that the fields are of type ltree would hint
the underlying system that these are related records, of which the
parent can't be deleted due to the FK constraint. But alas, this doesn't
seem to be the case - instead we'll need a few triggers. I expected a
little bit more magic than there actually is.

Regards,
--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //