Re: A few questions about ltree

> 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/