Re: A few questions about ltree

[Teodor Sigaev <teodor(at)sigaev(dot)ru>]

> We've been experimenting with a table containing a branch 'a', 'a.b' and 
> 'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
>
> 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
> SQL> select * from ltree_test;
>  path
> -------
>  a
>  a.b.c
> (2 rows)
>
> Is there some obvious/easy way to prevent this?

Sorry, only by using triggers on insert/delete/update.

If it was a possible to use function in foreign key then it might looks as
create table foo (
    path ltree not null
);

insert into foo values (''); -- root of tree, but it unremovable...

create unique index path_foo_idx on foo ( path ); -- BTree index for constraint

alter table foo add foreign key subpath( path, 0, -1) references foo( path )
    deferrable initially deferred,;

But it's impossible...


--
Teodor Sigaev                                   E-mail: teodor(at)sigaev(dot)ru
                                                   WWW: http://www.sigaev.ru/